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 your Dundas BI instance.

This applies when installing or upgrading Dundas BI when it uses Microsoft SQL Server to store its own data. Within Dundas BI, users can then connect, analyze, and visualize data from a variety of other data sources or use other authentication methods.

Important
Before reading this article, you should have a basic understanding of using SQL Server Management Studio.

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

Certain database permissions are needed for the user Dundas BI connects as while deploying a Dundas BI instance, which can be reduced when deploying is finished.

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:

  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, right-click on the Logins folder and choose 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 SQL Server authentication, and enter the Password.

    Create SQL Server authentication login
    Create SQL Server authentication login

  5. Click the Server Roles page and enable the SysAdmin role, or a 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 when you deploy Dundas BI and set up the application database connection.

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 a text editor such as Notepad as an administrator, by right-clicking its shortcut and choosing Run as administrator.

Open the Dundas BI configuration file, located at [InstanceRoot]\www\BIWebsite\App_Data\dbi.config.

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

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

The warehouse database connection is defined in Dundas BI's configuration settings:

Log into Dundas BI as an administrator, and click the Admin icon in the main menu on the left.

Click to expand Setup and then click Config.

Find or search for the Data Warehouse Connection String setting, located in the General category, and edit it to also use SQL Server authentication.

Updating the warehouse connection string in Dundas BI
Updating the warehouse connection string in Dundas BI

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:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri