StarQuest Technical Documents
SQL Server Authentication Considerations
Last Update: 17 March 2010
Product: StarQuest Data Replicator
Version: 3.x
Article ID: SQV00DR022
Abstract
The StarQuest Data Replicator (SQDR) service interacts with SQL Server security for these purposes:
- To read from or write to the SQDR control tables
- To read from a SQL Server object that is a replication source
- To write to a SQL Server object that is a replication destination
In each of these situations, the SQDR Service must log in to SQL Server using a valid login and password. The security properties of the SQL Server and of the ODBC DSN dictate which login and password should be used to connect to the SQL Server and how SQDR determines default settings.
This document outlines the SQDR configuration and usage considerations when using a particular SQL Server authentication mode. While these instructions address many of the configuration steps involved with setting up a new SQDR installation, this document is intended to supplement, but not replace, the SQDR or SQDR Plus Quick Start Guides.
Background
SQL Server can operate in one of two security modes: Windows Authentication mode or SQL Server and Windows Authentication mode. For both authentication modes, a Windows login can be used to connect to the SQL Server. Only in the latter case will SQL Server credentials be accepted. To determine the security mode in effect, open the SQL Server Management Studio, right-click on the server in the left pane and select Properties. Select Security on the left and note the authentication mode. Refer to your SQL Server documentation for more information about managing security levels and authentication modes.
Solution
The type of authentication to be used to access SQL Server (by the SQDR service) should be decided on during the planning stages of a SQDR implementation. To simplify the implementation, we generally recommend the use of SQL Server authentication. However, SQDR fully supports Windows authentication as well.
The type of authentication will affect the following SQDR configuration operations:
Configuring the SQL Server ODBC DSN
SQDR uses the SQL Server ODBC DSN configuration to determine which type of user credentials to expect when connecting to the SQL Server. Note that the login, whether SQL Server or Windows, must have appropriate permissions for the operations the SQDR Service will perform, such as read/write access to the destination database.
Configuring the SQDR Service
On the first panel of the SQDR Configuration wizard, you will be prompted to select the ODBC DSN that the SQDR Service should use to connect to the SQL Server that contains the SQDR control tables and to provide the user credentials for the connection. Use the appropriate login credentials (Windows or SQL Server) depending on how you configured the SQL Server ODBC DSN.
Defining a SQDR Source or Destination
When defining a SQDR Source or Destination for the SQL Server, select the SQL Server ODBC DSN and specify the appropriate login credentials (Windows or SQL Server) depending on how you configured the SQL Server ODBC DSN.
Creating a Replication Subscription
In the Destination panel of the SQDR subscription wizard, SQDR automatically provides the default Object Schema based on the login used in the Destination properties. When using Windows authentication, the default value provided by SQDR for the Object Schema is not typically valid and must be manually modified.
To help you decide which authentication type to use, review the information below for each authentication type. Or, if you aready know which authentication type is best suited for your environment, choose the appropriate link below to be directed to the specific SQDR configuration steps.
SQL Server Authentication
Configuring the SQL Server ODBC DSN
If your SQL Server is configured to use the "SQL Server and Windows Authentication" mode, you have the option of using a SQL Server user account to connect to the SQL Server. Select the option "With SQL Server authentication using a login ID and password entered by the user " in the DSN configuration, as shown in the image below.
Configuring the SQDR Service
Select the SQL Server ODBC DSN from the Data Source drop down list and supply a SQL Server User ID and Password, as shown in the image below.
Important note: the SQL Server login must be a member of the SQL Server sysadmin server role.
Defining a SQDR Source or Destination
When defining a SQDR Source or Destination for the SQL Server, select the SQL Server ODBC DSN and the SQL Server login to use. The images below show the Destination Properties, but the same applies to the Source configuration.
Note: it is not necessary to use the same type of login for the Source or Destination as the one used during the SQDR Configuration. For example, if you would like the SQDR service to use a Windows login to connect to its control database, but would prefer that the SQDR Source/Destination use a SQL Server login, you only need to create a separate SQL Server ODBC DSN configured to accept a SQL Server login and use it when creating the Source/Destination in SQDR.
Creating a Replication Subscription
If your SQDR Destination uses a SQL Server login, SQDR will automatically populate the Object Schema field with the default schema for that login, such as "dbo. In almost all cases, no change is required. You only need to modify the Object Schema if you want to use a different schema for the destination object.
Windows Authentication
Configuring the SQL Server ODBC DSN
If you plan to use a Windows user account to connect to the SQL Server, select the "With Windows NT authentication using the network login ID" in the DSN configuration, as shown in the image below.
Configuring the SQDR Service
Select the SQL Server ODBC DSN from the Data Source drop down list and supply a Windows User ID (including the domain) and Password, as shown in the image below.
Important note: the Windows login must be a member of the SQL Server sysadmin server role.
Defining a SQDR Source or Destination
When defining a SQDR Source or Destination for the SQL Server, select the SQL Server ODBC DSN and specify a Windows login, including the domain if necessary. The images below show the Destination Properties, but the same applies to the Source configuration.
Note: it is not necessary to use the same type of login for the Source or Destination as the one used during the SQDR Configuration. For example, if you would like the SQDR service to use a Windows login to connect to its control database, but would prefer that the SQDR Source/Destination use a SQL Server login, you only need to create a separate SQL Server ODBC DSN configured to accept a SQL Server login and use it when creating the Source/Destination in SQDR.
Creating a Replication Subscription
If the SQDR Destination is configured to use a Windows login, when creating a subscription SQDR will automatically populate the Object Schema field with the Windows login. In most cases, this is not a valid schema on the destination database. Modify the Object Schema to use a valid schema on the destination database server, such as "dbo", as shown below.
DISCLAIMER
The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization. The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.