How to enable SQL Server authentication

Contents[Hide]

1. Overview

Dundas BI recommends that SQL Server Authentication be used as the means of connecting to the Dundas BI application database, and the Dundas BI warehouse database. This is done for security reasons. In this article we explain how to enable SQL Server authentication, and how to use it with a Dundas BI instance.

Important
Before reading this article you should have a basic understanding of using SQL Server Management Studio. For more information, click here.

2. Enabling SQL Server Authentication through SQL Management Studio

To enable SQL Server Authentication for your instance do the following:

  1.  Open SQL Server Management Studio.
  2.  Connect to the SQL Server instance you would like to use for Dundas BI.
  3.  In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  4.  On the Security page, under Server authentication, select SQL Server and Windows Authentication mode, and then click OK.
  5.  In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

3. Using SQL Server Authentication in Dundas BI

3.1. Before deploying an instance

3.1.1. Creating a SQL Server authentication user

Dundas BI will require during the deployment of an instance with new databases that the user have the following roles: SysAdmin role, or all of the DbCreator, DiskAdmin, ProcessAdmin, and SecurityAdmin roles. Create a user like this in SQL Management Studio by doing the following:

  1.  Open SQL Server Management Studio.
  2.  Connect to the SQL Server instance you would like to use for Dundas BI.
  3.  Expand the Security folder, and right click on the logins folder, and then click New Login....
  4.  Enter the login name as DundasBIUser, select the SQL Server authentication radio button, and enter the passwords.
  5.  Click the Server Roles page and check the SysAdmin Role, or a the combination of DbCreator, DiskAdmin, ProcessAdmin, and SecurityAdmin Roles.

3.1.2. Specifying the SQL Server authentication user

Now that the SQL Server authentication user has been created, use these credentials in the following screen:

Updated application database connection string in Dundas BI.
Updated application database connection string in Dundas BI.

3.2. After deploying an instance

After deploying it is possible to remove the Sysadmin Role from this user. This user role should be added again before attempting to upgrade an instance.

4. Changing an existing instance to use SQL Server authentication

To change an existing instance to use SQL Server authentication do the following:

4.1. Application Database

  1. Open notepad as an administrator.
  2. Open the file at: [InstanceRoot]\www\BIWebsite\App_Data\dundasBIConnection.config.
  3. Edit the connection string with the new SQL Server Authentication style connection string.
    Note
    To change the credentials when the connection string is encrypted, you need to first decrypt the connection string using the dt.exe command line tool, then update the credentials.
  4. Go to IIS and reset the application pool.

Updated application database connection string in Dundas BI.
Updated application database connection string in Dundas BI.

4.2. Warehouse Database

  1. Login to Dundas BI as an administrator.
  2. Click the admin icon.
  3. Click the Config section.
  4. Edit the data warehouse connection string in the general category with the new SQL Server Authentication style connection string.

Updating the warehouse connection string in the Dundas BI user interface.
Updating the warehouse connection string in the Dundas BI user interface.

5. See Also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri