|
This post installation step is only necessary if you did not advice the Setup Program to create a database server login and database user during installation. |
Below you find information on how to prepare the Database Server and the Application Server Database to provide access for the Application Server.
Creating an SQL Server login
Before the Application Server accesses the Database Server to read and write data from and to the Application Server Database the Application Server has to first authenticate against the SQL Server. In other words a login (or in SQL Server terms a principal) has to be created on the Database Server that will be later used by the Application Server.
SQL Server can authenticate logins using its own internal mechanism, or it can rely on Windows to authenticate logins. Each of these approaches has its advantages and disadvantages, for a detailed discussion on this please refer to the SQL server documentation. As far as Redbex is concerned both authentication mechanisms are fine and you can choose whatever fits better to in your environment.
When authentication relies on Windows user accounts or groups these can belong either to the local machine or to a Windows domain. When using log on to SQL Server using Windows authentication, the windows user account or the windows user group must be created as a login in SQL Server. Listing 1 shows shows a template for creating an SQL server principal based on a windows user account or windows user group. Listing 2 shows an example for creating a principal based on a windows domain user. Bare in mind that when using windows users for SQL server that the windows password expiration rules might disable logins at some point in time.
CREATE LOGIN [<windows user or user group>] FROM WINDOWS WITH DEFAULT_DATABASE = [<YourRedbexDatabaseName>]; |
Listing 1: Template for creating an SQL server principal based on a windows user account or user group.
CREATE LOGIN [windowsdomain\user1] FROM WINDOWS WITH DEFAULT_DATABASE = [Redbex Application Server Database]; |
Listing 2: Example for creating an SQL server principal based on a windows domain user account.
Listing 3 shows a template that can be used to create a login based on SQL Server's internal authentication mechanism.
CREATE LOGIN [<LoginName>] WITH PASSWORD = '<your password>', DEFAULT_DATABASE = [<YourRedbexDatabaseName>], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF; |
Listing 3: Template for creating an SQL server principal based on a SQL Server's internal authentication mechanism.
The SQL server principal created does not need and should not have any specific server roles.
Creating a database user
The login you crated in the previous step now needs to be added as database user for the Application Server Database. In SQL Server Database user principals are the database-level security context under which requests within the database are executed, and are associated with either SQL Server or Windows logins.
The template shown in Listing 4 will create a new user within the Application Server database and associate this user with the specified login (principal) you created earlier. When Application Server with this login attempts to access the Application Server Database, it will do so under the security context of the here created database user within that database.
USE [<YourDatabaseName>] GO CREATE USER [<Your selected username>] FOR LOGIN [<YourSQLServerLoginName>]; GO |
Listing 4: Template for creating a database user
To be able to perform the necessary data modification operations the database user used by the Application Server has to have specific database roles. These are the roles: db_datareader, db_datawriter. Listing 5 shows a template for adding a user to these role.
USE [<YourDatabaseName>]; GO EXEC sp_addrolemember N'db_datareader', N'<Your selected username>'; EXEC sp_addrolemember N'db_datawriter', N'<Your selected username>'; GO |
Listing 5: Template for adding the database user to database roles.
Listing 6 shows a complete example for creating a database user. We suggest to use the name RedbexApplicationServer for both the SQL server login and the database user. This example uses a windows domain user.
CREATE LOGIN [company\RedbexApplicationServer] FROM WINDOWS WITH DEFAULT_DATABASE = [Redbex Application Server Database]; GO
USE [Redbex Application Server Database] GO
CREATE USER [Redbex Application Server] FOR LOGIN [company\RedbexApplicationServer]; GO
EXEC sp_addrolemember N'db_datareader', N'Redbex Application Server'; EXEC sp_addrolemember N'db_datawriter', N'Redbex Application Server'; GO |
Listing 3: Template for creating an SQL server principal based on a SQL Server's internal authentication mechanism.
