How to enable SQL Server authentication

Contents[Hide]

1. Overview

SQL Server authentication is recommended for connecting Dundas BI to its application and warehouse databases for security reasons. This article explains 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:

  1. Open SQL Server Management Studio.
  2. Connect to the SQL Server instance you would like to use for Dundas BI.

    Connecting to a database server (e.g., localhost)
    Connecting to a database server (e.g., localhost)

  3. In the Object Explorer, right-click the server, and then click Properties.

    Server properties
    Server properties

  4. On the Security page, under Server authentication, select SQL Server and Windows Authentication mode, and then click OK.

    Turn on SQL Server authentication mode
    Turn on SQL Server authentication mode

  5. In the Object Explorer, right-click your server, and then click Restart. If the SQL Server Agent is running, it must also be restarted.

    Restart SQL Server
    Restart SQL Server

3. Using SQL Server Authentication in Dundas BI

3.1. Before deploying an instance

3.1.1. Creating a SQL Server authentication user

During the deployment of an instance with new databases, the user must have the SysAdmin role, or else all of the following: DbCreator, DiskAdmin, ProcessAdmin, and SecurityAdmin.

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....

    Create SQL Server authentication login from context menu
    Create SQL Server authentication login from context menu

  4. Enter the login name as DundasBIUser, select the SQL Server authentication radio button, and enter the passwords.

    Create SQL Server authentication login
    Create SQL Server authentication login

  5. Click the Server Roles page and check the SysAdmin Role, or a the combination of DbCreator, DiskAdmin, ProcessAdmin, and SecurityAdmin Roles.

    Add SysAdmin role to login
    Add SysAdmin role to login

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:

Specifying credentials for SQL Server authentication
Specifying credentials for SQL Server authentication

3.2. After deploying an instance

After deploying, it is possible to remove the SysAdmin role from this user. For regular operation, the user will only require the dbo default schema and the db_owner role membership. The SysAdmin role will be required again when attempting to upgrade an instance.

4. Changing an existing instance to use SQL Server authentication

Follow the steps below to change an existing instance to use SQL Server authentication for its application and warehouse databases.

4.1. Application database

Open Notepad as an administrator, by right-clicking its shortcut and choosing Run as administrator.

Open the file at: [InstanceRoot]\www\BIWebsite\App_Data\dundasBIConnection.config.

Edit the connection string to use SQL Server authentication. After saving, reset Dundas BI's application pool in IIS.

Updated application database connection string
Updated application database connection string

Note
To change the credentials when the connection string is encrypted, first decrypt the connection string using the dt.exe command line tool, then update the credentials. To resume the secure state, encrypt the connection string when you have updated the credentials.

4.2. Warehouse database

Login to Dundas BI as an administrator.

Click the Admin icon in the main menu on the left.

Click Config, found in the Setup section.

Edit the Data Warehouse Connection String setting in the General category with the new connection string that uses SQL Server authentication.

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