Using Windows authentication with SQL Server 2005 or 2008 databases

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

This section describes how to configure Perception to use Windows authentication to secure the communications between Perception Server and the SQL Server 2005 or 2008 database. This section is divided in to the following sections:

 

Using Windows authentication to control access to the database is only possible with SQL Server 2005 and 2008.

What is the difference between SQL Server authentication and Windows authentication?

SQL Server authentication and Windows authentication provide unique methods for authentication of users and applications wanting access to databases. The decision tree below provides information about how the authentication process for the two methods differ.

Windows authentication uses a specific domain user account or group that has access permission for select databases and to specific servers. This allows the domain user account to be controlled by Active Directory or Domain Controllers within an organization. While SQL Server authentication is only controlled by the database server. Windows authentication allows organizations to use the following features that are not available or are inferior in the SQL Server authentication method.

Feature Description
Improved encryption of database passwords The encryption and transmission of Windows authentication is more secure than SQL Server authentication.
Account lockout The Windows account used by Windows authentication can be set to automatically lockout if the server detects someone trying to unlawfully gain access by using a brute force method of hacking a password.

Using Windows authentication means that any part of Perception that access the database will need to use the same user account information to be granted access. This requires the following Perception components to have their configuration changed to include the account information:

  • Shared Repository Service
  • Perception virtual directories
  • Perception database
  • Perception Server

The below instructions are only for Windows Server 2003. If you are using Windows Server 2008, please refer to the following Knowledge Base article for guidance about how to setup Windows authentication for use with the Perception repository:

How can I enable Windows authentication to the Perception database in Windows Server 2008?

Configuring Windows authentication on your Perception Server

To configure Windows authentication a number of changes will need to be made on the machine hosting Perception and the Perception database server. Refer to the following section to make the necessary changes:

Configuring your server for Windows authentication

To use Windows authentication with IIS successfully it is recommended that you carry out the following steps:

Enabling Parent Paths

For Windows authentication you will need to enable Parent Paths in IIS. For instructions about how to complete this, please refer to the Configuring Parent Paths permissionssection.

Configuring IIS to only use the NTLM protocol for Windows authentication in IIS 6

To configure IIS so that it only uses the NTLM protocol for Windows authentication complete the following steps:

    1. Launch IIS
    2. Right click on the Server Name and click Properties
    3. Place a check in the Enable Direct Metabase Edit check-box
    4. Click OK
    5. Launch Windows Explorer and navigate to the folder C:\Windows\System32\Inetsrv
    6. Locate and open the file Metabase.XML using an ASCII text editor like Notepad
    7. Locate the entry NTAuthenticationProviders
    8. Change the setting so that it appear as the following:
NTAuthenticationProviders="NTLM"
  1. Save the changes to the file and restart IIS to initialize the changes
  2. Repeat steps 1 to 4 this time un-checking the Enable Direct Metabase Edit check-box

Please note that if you are using Windows Server 2003 64-bit you will need to use the following steps instead of those listed above:

  1. Launch the command prompt as an administrative user
  2. Navigate to folder that contains the Adsutil.vbs file. By default, it is located in the following directory:

    C:\Inetpub\Adminscripts

  3. Enter the following command at the command prompt:

    cscript adsutil.vbs set w3svc/NTAuthenticationProviders "NTLM"

You can check if the setting has been enabled by entering the following command:

cscript adsutil.vbs get w3svc/NTAuthenticationProviders

You should get a response similar to the following:

Configuring Windows Server 2003 to use NTLM communication

To configure Windows Server 2003 so that the correct NTLM protocol is available for communication when using Windows authentication complete the steps below:

    1. Click Start | Control Panel | Administrative Tools | Local Security Policy
    2. Expand the folders Security Settings | Local Policies | Security Options
    3. Locate and right click on the policy Network Security: LAN Manager authentication level in the window pane on the right

    1. Select Properties
    2. Choose Send LM & NTLM -use NTLM2 session security if negotiated from the drop-down list

  1. Click OK

Adding the domain user for Windows authentication to the appropriate local groups

To ensure that access is granted to Perception using the domain user, the domain user will need to be added to certain local groups. For example, the domain user being used for Windows authentication should also be added to the following local groups:

  • Administrators
  • IIS_WPG (Worker Process Group)

The domain user can be added to these groups using the Computer Management console. To add the domain user to these groups complete the following steps:

    1. Click Start | Control Panel | Administrative Tools | Computer Management
    2. Expand the folders System Tools | Local Users and Groups | Groups

  1. Double click Administrators
  2. Click Add
  3. Enter the name of the domain user in the Enter the object names to select text-box
  4. Click Check Names to confirm the domain user is correct
  5. Click OK
  6. Click OK again to close the Administrators Properties window
  7. Double click on the IIS_WPG group
  8. Repeat steps 4 to 7

Setting the Perception Database to use Windows authentication

You will need to assign a domain user account to access the Perception database. Once the user has been setup you will need to apply this user to Perception.

To do this in SQL Server 2005 complete the following steps on your Perception database server:

    1. Launch Microsoft SQL Server Management Studio
    2. Select the Database Server name of your machine hosting the Perception database
    3. In the Object Explorer navigate to the folder Security | Logins
    4. Right click the Logins folder and select New Login...

The Login - New window will appear.

    1. Ensure the General page is selected and click Search
    2. Click Locations... and select your organizations domain from the Entire Directory drop-down list
    3. Click OK
    4. Enter the domain user account you want to use to access the database in the Enter the object name to select text-box.
      It is recommended that you create a general user account that is solely used to control access to the Perception.

    1. Click Check Name to confirm the domain account is valid and click OK
    2. Ensure the Windows authentication option button is selected
    3. Select the Perception database name from the Default database drop-down list
    4. Select the User Mappings page
    5. Place a tick in the check-box that appears in the Map column next to the Perception database
    6. Place a tick in the db_owner check-box in the Database role membership for list

  1. Click OK

The database has now been setup to use Windows authentication. You must change the ODBC connector to use the new details when connecting to the database. To do this follow the instructions below.

  1. On the machine hosting the Perception Server navigate to Control Panel | Administrative Tools | Data Sources (ODBC)
  2. Select the System DSN tab
  3. The System Data Source you created for your Perception installation initially should appear.
  4. Select your Perception DSN and click Configure
  5. Click Next >
  6. Select the option button for With Windows NT authentication using network login ID
  7. Remove the tick from the Connect to SQL Server to obtain default settings for the additional configuration options check-box and click Next >
  8. Ensure the Change the default database for check-box is ticked and select or enter your Perception database
  9. Click Next >
  10. Click Finish
  11. Test the connection to the database server is working

Configuring IIS to use Windows authentication with Perception

To use Windows authentication with Perception Server you will need to change the default installation of Perception so that it uses a separate Application Pool for Enterprise Manager and that it is configured to use the same account as the Windows authentication user used by SQL Server and Perception.

To create a new Application Pool for Enterprise Manager:

    1. Launch Internet Information Server (IIS) Manager on the server hosting Perception
    2. Select the computer name of the server
    3. Right click on the Application Pool folder
    4. Select New | Application Pool...
    5. Enter an Application pool ID in the text-box

    1. Select the Use default settings for new application pool option button
    2. Click OK
    3. Right click on the Application Pool just created and select Properties
    4. Select the Identity tab
    5. Select the Configurable option button
    6. Enter the domain user account and the appropriate password (This will be the same account and password you associated with the Perception database)

  1. Click OK

With the application pool created you will need to set the virtual directories so that they will use the application pool. To apply the application pool to the virtual directory:

    1. Launch Internet Information Server (IIS) Manager on the server hosting Perception
    2. Select the computer name of the server
    3. Select Web Sites
    4. Select Default Web Site (If your Perception Server installation was customized select the appropriate Web site folder)
    5. Right click on the em5 virtual directory and select Properties
    6. Select the application pool you created above from the Application pool drop-down list

  1. Click OK
  2. Repeat steps 5 to 7 for the <Repository_Name>_con, <Repository_Name>_res, <Repository_Name>_togo, OpenAuthoring5, qm2golms5, ScoringTool5, qmwise5 and ChartImages virtual directories

    If you only host Perception on your server you can change the virtual directories to use the new application pool once in IIS. To do this:

    1. Right click on the Default Web site and select Properties
    2. Select the Home Directory tab
    3. Select the application pool you created from the Application pool drop-down list
    4. Click OK

With the application pools set for the virtual directories you now have to configure the authentication and access control for the virtual folders. To do this:

  1. Launch IIS
  2. Right click on the Default Web Site and click Properties
  3. Select the Directory Security tab
  4. Click Edit in the Authentication and access control box
  5. Enter the domain user account and password you used for the Windows authentication in the User name and Password text-boxes
  6. Click OK and Select All the virtual directories you want to make this change to

Configuring the Shared Repository Service to use Windows authentication

You will need to alter the Shared Repository Service so that it uses the same user account to log on and run. Without making this change Authoring Manager users will not be able to connect to a shared repository that is running with Windows authentication. To change the log on information for the Shared Repository Service:

    1. Click Start | Run
    2. Enter services.msc into the Open text box and click OK

The Services window will appear

    1. Right click on the Questionmark Shared Repository service and select Properties
    2. Select the Log On tab
    3. Select the This account option button
    4. Click Browse to search for the required domain account or type it directly in the This account text-box
    5. Enter and confirm the password for the account
    6. Click OK

  1. Click OK to the messages confirming the permission change
  2. Right click on the Questionmark Shared Repository service and select Restart to enable the changes

Perception Server has now been configured to use Windows authentication to connect to the SQL Server database.