How to Enable SQL Server Authentication

This applies to: Managed Dashboards, Managed Reports

SQL Server authentication is recommended for connecting Symphony to its application and warehouse databases for security reasons. This article explains how to enable SQL Server authentication, and how to use it in your Symphony environment.

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

Note: You should have a basic understanding of using SQL Server Management Studio to deploy in your environment.

Enabling SQL Server Authentication through SQL Management Studio

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

   

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

   

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

   

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

Using SQL Server Authentication in Symphony

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

Before Deploying an Instance

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

   

4. Enter the login name as DundasBIUser, select SQL Server authentication, and enter the Password.

   

5. Click the Server Roles page and enable the SysAdmin role, or a combination of DbCreator, DiskAdmin, ProcessAdmin, and SecurityAdmin roles.

   

Specifying the SQL Server Authentication User

Now that the SQL Server authentication user has been created, use these credentials when you deploy Symphony and set up the application database connection.

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.

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

Application Database

Open a text editor such as Notepad as an administrator, by right-clicking its shortcut and choosing Run as administrator. Open the configuration file.

Edit the connection string to use SQL Server authentication by specifying the User ID and Password. After saving, recycle the application pool in IIS or restart the Linux service for Symphony's website.

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.

Warehouse Database

The warehouse database connection is defined in Symphony's configuration settings.

Log into Symphony as an administrator, and access Administration from the main menu.

Click to expand Setup and then click Config.

For more information, see:

• Microsoft Docs: SQL Server Management Studio

• Microsoft Docs: Choose an Authentication Mode

• Symphony Managed Dashboards and Reports Config File

• Configuration Settings