How to enable SQL Server authentication
- Enabling SQL Server Authentication through SQL Management Studio
- Using SQL Server Authentication in Dundas BI
- Changing an existing instance to use SQL Server authentication
- See Also
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.
2. Enabling SQL Server Authentication through SQL Management Studio
To enable SQL Server Authentication for your instance do the following:
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:
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:
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
To change an existing instance to use SQL Server authentication do the following:
4.1. Application Database
- Open notepad as an administrator.
- Open the file at: [InstanceRoot]\www\BIWebsite\App_Data\dundasBIConnection.config.
- Edit the connection string with the new SQL Server Authentication style connection string. NoteTo 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.
- Go to IIS and reset the application pool.
4.2. Warehouse Database
- Login to Dundas BI as an administrator.
- Click the admin icon.
- Click the Config section.
- Edit the data warehouse connection string in the general category with the new SQL Server Authentication style connection string.