Creating a SQL Server 2005 or 2008 database | Questionmark

Creating a SQL Server 2005 or 2008 database

Applies to the following products: 
Questionmark Perception
Applies to the following Perception versions: 
Perception 5.2
Perception 5.1

The instructions given below assume that you are using SQL Server 2005 or 2008. This method also assumes you will be using SQL Server authentication mode to setup the database. If you intend to use Windows authorization for your SQL Server 2005 or 2008 database, please refer to:

Using Questionmark Perception and Windows Authentication in SQL Server

To create a SQL Server 2005 or 2008 database for use with Perception:

    1. Open SQL Server Management Studio
    2. Right-click the Database folder on the attached database server and select New database
    3. Enter the database name
    4. From the Options page set the collation to SQL_Latin1_General_CP1_CI_AS and click OK

Once you have created the database, there are three further tasks to complete:

Creating a database login

Once you have created your database, you need to set up a new login. To do so:

    1. Open the SQL Server Management Studio
    2. Expand the server folder for the server on which you have created your database in the navigation pane
    3. Expand the Security sub-folder
    4. Right-click Logins and select New login...
    5. Enter a name for the new login
    6. Select SQL Server Authentication and enter a password
    7. Un-check the Enforce password policy, Enforce password expiration and User must change password at next login check-boxes
    8. In the Default database list, select the database you created
    9. Open the User Mapping page and tick the database you created

  1. Tick the db_owner box to assign permissions
  2. Click OK

Configure SQL Server authentication

To ensure that SQL Server is configured to provide authentication based on the SQL Server login you have created:

    1. Open the SQL Server Management Studio
    2. Right-click the server you have created the database in and select Properties
    3. Open the Security page

  1. Ensure that SQL Server and Windows Authentication Mode is selected and click OK

Switching on Full Text Indexing

If you intend to use question searching in Authoring Manager you will need to ensure that Full Text indexing is enabled for the database. To enable Full text indexing in SQL Server Management Studio:

    1. Right click on the database you created above
    2. Select Properties
    3. Click Files from the Select a page menu
    4. Place a check mark in the Use Full-text indexing check-box

  1. Click OK
If the check box is already ticked and grayed out this is because Full Text Indexing has been enabled for the whole server meaning all the databases have Full Text Indexing enabled by default.

Creating an ODBC data source

You need to create an ODBC data source to link Perception to the database you have created. If you are using a 64-bit version of Windows (For example, Windows Server 2008 R2) you will need to use the 32-bit version of ODBC driver.

To create a data source:

    1. Open the ODBC Data Source Administrator on your Perception machine as an administrative user

To do so on a:

  • 32-bit operating system go to Control Panel double-click Administrative Tools and then Data Sources (ODBC)
  • 64-bit operating system, such as Windows Server 2008 R2, navigate and launch the following ODBC driver:


  • Open the System DSN tab
  • Click Add...
  • Select the SQL Server driver from the list and click Next
  • Enter a name for the new data source and select the server you want to connect to (that is, the machine on which your database is installed) from the drop-down list
  • Click Next
  • Ensure that With SQL Server authentication using a login ID and password entered by the user is selected
      1. Enter the ID and password of the login you created and click Next
      2. Select your database from the default database drop-down list and click Next

      3. Click Finish. Review the details of the new ODBC data source that will be created and click OK

    You can click Test Data Source to check the connection.

    1. Click OK to close the ODBC Data Source Administrator.

    You can now create your repository.