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. If you create a user account in a ReliaSoft desktop application that is not recognized by SQL Server (i.e., is not associated with an individual or group SQL Server login that has access to the application database), the user will not be able to connect with the database under his/her own Windows login.
Tip: By default, base installations of Microsoft SQL Server Express include the "Builtin\Users" Active Directory group as a SQL Server login. 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.
Alternatively, in all ReliaSoft desktop applications, the software provides the option to create an enterprise database connection file (*.rserp) that impersonates another Windows login. This means that you can create one shared user account in Windows that has a SQL Server login. Then any user who does not have his/her own SQL Server login can connect with the database using an *.rserp file that impersonates the shared account.
If either of these considerations apply to your situation, you do not need the instructions provided below.
If You Want Each User to Have His/Her Own SQL Server Login
It is important to note that a user must have the appropriate role in SQL Server (e.g., the Security Administrator or System Administrator role) in order to create SQL Server logins, and this level of database authority is not provided by default to all users who have administrative permissions in the ReliaSoft application. Therefore, if you do not take the appropriate steps to prepare for your implementation, it may be possible to create user accounts via the ReliaSoft application that are not recognized by SQL Server or cannot access the application database.
If you plan to have each user connect with the database using his/her own SQL Server login, this document describes two options to ensure that a SQL Server login will be in place for all user accounts created via the ReliaSoft application. Both options require a database administrator to perform actions directly within the database platform using SQL Server Management Studio (Express).
Option 1 - Create Individual SQL Server Logins in Advance
The database administrator can create a SQL Server login in advance for each user who may receive a user account from the ReliaSoft application. This does not give the user access to any other database that resides on SQL Server; it simply ensures that SQL Server will recognize the username when/if an account is created via the ReliaSoft application, and the account will be able to access the application database.
To create a SQL Server login for a group of users or for every user account that might possibly be created via the software:
- Open SQL Server Management Studio (Express).
- Expand Security then Logins.
- Right-click and select New Login from the shortcut menu and create the login for the first potential application user (domain\username).
- In the Login Properties window, click User Mapping and grant access to the application database (at least the db_datareader and db_datawriter database roles are required).
- Continue creating logins until there is a SQL Server login for every potential user of the ReliaSoft application.
Option 2 - Create Individual SQL Server Logins When Creating Application User Accounts
The database administrator can grant the appropriate level of database authority to any user who has the ability to create user accounts via the ReliaSoft application. If the person who creates ReliaSoft user accounts also has the authority required to create the SQL Server logins, then when he/she creates a new user account via the software interface, the required SQL Server login can be created automatically at the same time.
To grant the authority to automatically create SQL Server logins to any user who has permission to create user accounts via the ReliaSoft application:
- Open SQL Server Management Studio (Express).
- Expand Security then Logins. If a login already exists for the user, right-click it and choose Properties from the shortcut menu. If a login does not already exist for the user, choose New Login from the shortcut menu and create it (domain\username).
- In the Login Properties window, click Server Roles and then select an appropriate option from the predefined list (e.g., securityadmin or sysadmin).
