SQL Server Logins or Using Windows Impersonation

Connecting with a SQL Server database via Windows Authentication requires a "SQL Server login" that allows the database platform to recognize the user and gives access to the application database. There are three ways that a Synthesis user account may be recognized by SQL Server:

Individual Login: The user has an individual SQL Server login that is associated directly with his/her Windows username.
Group Login: The user belongs to an Active Directory group that has a SQL Server login shared by all members of the group.
Use Impersonation for Connection File: The user does not have an individual or group login but he/she connects to the repository with an enterprise connection file (*.rserp) that impersonates another Windows user account that does have a SQL Server login.

Your organization may choose to use any or all of these methods for your Synthesis implementation (e.g., some users may have their own individual logins, while other users connect using Windows identity impersonation). This document provides an overview of all three options.

Creating Individual SQL Server Logins

If you choose to create individual SQL Server logins for some or all of the Synthesis user accounts, you have two options:

A database administrator for SQL Server can create SQL Server logins in advance for every potential user and give the logins access to the application database (at least the db_datareader and db_datawriter roles are required). This would be performed directly in SQL Server (not via one of the Synthesis applications).
A database administrator for SQL Server can grant the appropriate level of database authority for creating SQL Server logins and database roles (e.g., securityadmin or sysadmin) to any user who has the ability to create user accounts in the Synthesis repository. The additional authority would be added directly in SQL Server. Then, when any of these administrative users creates a new user account via the Synthesis application, the required SQL Server login can be created and the application database roles can be assigned automatically at the same time.

The web page at http://www.ReliaSoft.com/synthesis/sql_server.htm provides links to instructions for performing certain tasks in SQL Server. This includes a link to an FAQ that discusses these two options in more detail and provides specific instructions for the actions that must be performed in the SQL Server Management Studio.

If you are using the first approach, you can clear the Create SQL Server login check box that is displayed when you are adding or importing a user account. If you are using the second approach, you must select this check box.

Tip: If the user already has a SQL Server login and access to the application database, it does not matter whether you select or clear the Create SQL Server login check box because the application attempts to create the login only if one does not already exist.

Furthermore, if the user who is creating the user account does not have the necessary level of database authority in SQL Server, the login will not be created even if the check box is selected.

Using a Group Login

If the user belongs to an Active Directory group that has a SQL Server login shared by all members of the group and that group has access to the application database, you can clear the Create SQL Server login check box that is displayed when you are adding or importing a user account.

For example, base installations of Microsoft SQL Server Express 2005 and 2008 include the "Builtin\Users" Active Directory group as a SQL Server login by default. This means all users with a Windows account for that domain will be able to log in to the enterprise database with no need to create individual SQL Server logins in SQL Server Express. However, it will still be necessary to grant access for this group login to the application database (at least the db_datareader and db_datawriter roles are required).

Using Windows Impersonation for the Connection File

If you choose to have some (or all) users connect to the SQL Server repository with a connection file that impersonates a shared Windows user account that has a SQL Server login, you must do the following:

A Windows network administrator must establish the shared user account on Windows.
A database administrator for SQL Server must create a SQL Server login for the shared Windows user account and grant this login access to the application database (at least the db_datareader and db_datawriter roles are required).
A Synthesis user must create an enterprise repository connection file that impersonates the shared Windows user account:
1. Choose File > New then click Create a new enterprise repository connection file.
2. Under Repository Connection Settings, select Microsoft SQL Server (2005, 2008) from the drop-down list and then select the Use impersonation check box.
3. Enter the server and database name for the SQL Server repository, then enter the domain, username and password for the shared Windows account that users will need to impersonate.
4. Click OK to create the connection file (*.rserp). It will be stored in the location specified under Connection File Name. Note that the default filename will be “SQL_(Server Name)_(Database Name),” but you can assign any name that fits the process your organization will use for distributing the file to users.

Once you have created a connection file that impersonates the shared Windows user account, you can distribute the file to any Synthesis user who needs it. To connect to the repository using this file, the user can:

Choose File > Open Repository and browse for the connection file.
Click Open to connect with the repository.

After the first connection, this *.rserp file will be saved in the list of recent repositories, which can be accessed by choosing File > Recent.

Note: For the purpose of being recognized by SQL Server and accessing the application database, the user will be impersonating the shared Windows login. For the purpose of performing actions via the Synthesis applications, the user’s actions will be governed by his/her own user account in Synthesis. In other words, multiple users can connect with the repository using the same enterprise connection file, but their activities within the Synthesis applications will be governed by the permissions established in their own individual Synthesis user accounts, and any changes made to the analysis data will be recorded in Synthesis under their own usernames.

If the user is going to connect to the repository with a connection file that uses Windows impersonation, you can clear the Create SQL Server login check box that is displayed when you are adding or importing the account.