StarQuest Technical Documents

Creating a Secure, 2-Tier SQDR Environment

Last Update: 5 February 2015
Product: StarQuest Data Replicator
Version: SQDR 3.42 or later
Article ID: SQV00DR020

Abstract

In certain environments, it may be necessary to secure SQDR and its dependent resources to prevent unauthorized use. This document describes how to set up a secure SQDR environment using methods that take advantage of Windows and SQL Server security. The illustration describes a 2-tier scenario, where the SQDR software co-exists on the same Windows computer as the destination SQL Server database but uses its own secure instance of SQL Server Express to store its control database.

Note that this document contains obsolete information for users of SQDR 4.5 and later:

• The use of SQL Server 2005 as the SQDR control database is no longer recommended; we recommend using SQL Server 2008 or later, and the use of the edition "SQL Server with Tools".
• Alternatively, DB2 for LUW can be used as a control database.
• StarSQL is now installed as part of SQDR; it is no longer necessary to install StarSQL in Step 3.
• We now recommend the use of connection strings rather than ODBC data sources for connections to the control database and to the source and destination databases.
• SQDR Configuration does not have an explicit option to Create a New Control Database; instead, enter the name of the database to be created in the dropdown field that lists available databases.

However, the basic principles illustrated here - restricting access to various objects to create a secure SQDR environment - are still relevant to users of SQDR 4.5 and later.

These instructions require the use of domain and local Administrator logins.

Step 1: Create a Windows account for the SQDR Administrator user

Step 2: Create a drive partition or folder for the SQDR and associated software

Step 3: Install StarSQL (and any other necessary database drivers) and create ODBC DSN(s)

Step 4: Install SQL Server 2005 Express and create ODBC DSN(s)

Step 5: Install and configure SQDR

Step 6: Restrict access to the drive or folder

Solution

The following sections guide you through the steps that are summarized in the preceding Abstract.

Before you Begin

The StarQuest Data Replicator requires a control database in which to store definitions for sources, destinations, subscriptions, and groups of subscriptions. In order to secure the SQDR installation, we recommend that you install a separate SQL Server (or named instance) in which to store the SQDR control database.

You can use any of the versions of SQL Server listed in System Requirements for the Data Replicator control database. If you do not already have a SQL Server installation, you can obtain the SQL Server 2005 Express Edition free of charge from http://www.microsoft.com/sql/editions/express. You may also need to download and install the Microsoft .NET Framework 2.0 which is available on the SQL Server 2005 Express download page. These instructions illustrate how to install SQL Server 2005 Express Edition with Advanced Services SP2.

Step 1: Create a Windows user account for the SQDR Administrator user

1. Create a local or domain Windows user account to act as the SQDR Administrator. The remainder of the instructions assume "sqdradmin" is the name of the Windows user created for this purpose.
2. Add the sqdradmin account to the local Administrators group.

Step 2: Create a drive partition or folder for the SQDR and associated software

Create a new drive partition or a new folder under an existing drive that will house SQL Server, SQDR, and the associated database drivers. It may be beneficial to create a separate drive partition to facilitate the isolation of SQDR and to ease housekeeping and backup operations.

These instructions assume an X: drive was created and assigned the name "SQDR".

Step 3: Install StarSQL (and any other necessary database drivers) and create ODBC DSN(s)

Install StarSQL and configure an ODBC System DSN as described in the StarSQL Quick Start Guide. Install other database drivers and configure ODBC System DSNs as needed.

Step 4: Install SQL Server 2005 Express Edition and create ODBC DSNs

Installing SQL Server 2005 Express

1. Log on to the computer with the sqdradmin account.
2. Download SQL Server 2005 Express with Advanced Services and then double-click the downloaded SQLEXPR_ADV.exe file to start the installation process.
3. After the files have been extracted, accept the license agreement and click Next.
4. Install the prerequisite components and click Next.
5. On the welcome dialog, click Next to start the installation of SQL Server 2005.
6. After the system configuration check completes, click Next.
7. Enter registration information and de-select (uncheck) the Hide advanced configuration options box. Click Next.
8. Click on the Database Services component and click the Browse button. Change the installation path to point to the newly created drive or folder (e.g., X:\Program Files\Microsoft SQL Server\).
9. On the Feature Selection dialog, expand the Client Components and choose to install the Connectivity Components and Management Studio Express. Click Next.
10. On the Instance Name dialog, check the Named instance radio button and enter SQDRControlDB in the Named instance field. Click Next.
11. If you are presented with the Existing components dialog, review and click Next. Otherwise, proceed to the next step.
12. Specify the sqdradmin Windows account to use for the SQL Server service and click Next.
13. On the Authentication Mode panel, choose the Windows Authentication Mode and click Next.
14. On the Collation Settings dialog, select the desired Collation designator and sort order (or SQL Collation).
15. On the Configuration Options dialog, de-select (uncheck) the Enable User Instances option. Select (check) the option Add user to the SQL Server Administrator role. Click Next.
16. Select the desired Error and Usage Report Settings and click Next.
17. Click Install and click Finish once the installation is complete.

Refer to the How to: Install SQL Server Express topic in the SQL Server 2005 Books Online for more information on installing SQL Server 2005 Express Edition.

Configure a SQL Server ODBC DSN

Define a System ODBC DSN for the SQL Server database that the Data Replicator Service will use to communicate with the control database.

1. Launch the ODBC Administrator from the Windows Control Panel. For most versions of Windows this is located in the menu path Control Panel—>Administrative Tools—>Data Sources (ODBC). If using a Windows 64-bit operating system, you must create both a 32-bit and a 64-bit ODBC DSN for the SQL Server database with identical data source names. The 32-bit ODBC Data Source Administrator (odbcad32.exe) is typically located in \WINDOWS\SysWOW64 directory.
2. Click the System DSN tab of the ODBC Data Source Administrator window.
3. To create a new data source for the SQL Server database, click Add. In the next dialog, select SQL Server Native (latest available) for the driver and click Finish.
4. Provide a name for the DSN and the newly installed SQL Server instance (i.e., <SERVER>\SQDRControlDB) and click Next to proceed.
5. Continue through the remainder of the wizard and click Test Data Source to ensure the DSN can connect to the specified SQL Server. If you do not see TESTS COMPLETED SUCCESSFULLY, modify the data source configuration until it can successfully connect to the server.

Step 5: Install and configure SQDR

1. Log on to the computer with the sqdradmin account.
2. Run the setup.exe program from the SQDR installer image.
3. When prompted, modify the installation path to point to the newly created drive partition or folder (i.e., X:\Program Files\StarQuest\SQDR).
4. At the end of the installation, with the Run Configuration Wizard option enabled, click Finish.

Configure the Data Replicator Service

You must configure the Data Replicator service to specify which SQL Server data source to use for the control tables and how the Data Replicator service should log in to the SQL Server.

1. In the first pane of the Configuration Wizard, select the SQL Server data source that you configured for the Data Replicator control tables (in Step 4)and enter the sqdradmin user and password to log in to the SQL Server. Click Next to proceed.
2. Select the option Create a New Control Database and click Next.
3. Choose to start the Data Replicator service automatically when the computer is started. Configure the service to start using the System Account and click Next.
4. De-select (uncheck) the options to start the Data Replicator Service and Manager. Click Finish.
5. Enter the license key(s) provided by StarQuest Customer Support.

Modify the SQDR Service Properties

1. Launch Windows Services from Control Panel—>Administrative Tools.
2. Right-click the StarQuest Data Replicator service and select Properties.
3. Under the Log On tab, select to Log on As "This Account" and provide the sqdradmin user and password.
4. Start the Data Replicator Service.

Step 6: Restrict access to the drive or folder

1. In Windows Explorer, browse to the drive or partition containing the SQDR and SQL Server files.
2. Right-click the folder or drive and select Sharing and Security.
3. Under the Security tab, click the Add button and enter sqdradmin in the object name field. Click the Check Names button to verify that the name is correct.
4. Grant Full Control access to the sqdradmin user.
5. Remove the Users group. It may be necessary to turn off the inheritable permissions option prior to removing the Users group, which can be done by clicking the Advanced button and de-selecting the "Allow Inheritable Permissions..." option.