Security considerations are an important part of the design and implementation of your distributed application. Because replication applies data changes from one server to many others across the network, understanding the layers of network security is essential.
The decentralized availability of replicated data increases the complexity of managing or restricting access to that data. Microsoft SQL Server replication uses a combination of security mechanisms to protect the data and business logic in your application.
One way to consider security requirements is to view the requirements as different layers of access. Each lower layer must work properly before any successive layer is added. Each successive layer is dependent on the proper operation of any preceding layer. These are the four layers of security to configure:
The first step in replicating data over the Internet is to establish a connection between the Subscriber and the proxy server. This process requires that Windows NT user accounts be configured on each computer. These accounts should differ from those used to log on to the Windows NT Server or to start SQL Server; otherwise, users might gain administrative access to the Windows NT Server or to SQL Server.
You must set up a Windows NT user account to start SQL Server Agent. If your organization uses SQL Server Agent services, such as alerts and SQL Mail, and has not established a separate account for SQL Server Agent, the new account must be configured with the minimum access rights required by these services.
The SQL Server Agent account on the Subscriber must be configured as a user account on the proxy server. This account is entered in the WinSock service on the proxy server to authenticate the Subscriber’s request for a connection. You either can use the same account or create a new account for FTP service authentication of the Subscriber. If you are creating separate accounts, the WinSock Proxy user account does not need any special access rights to the proxy server. The FTP user account must have read permission granted so the Subscriber can retrieve data from the snapshot folder.
After the user accounts have been configured, they must be entered into the appropriate services on the proxy server. Microsoft Proxy Server provides a line of defense against unauthorized connections to the internal network. The Distribution Agent or Merge Agent on the Subscriber uses the user account information in the SQL Server Agent account profile to gain access to the FTP and WinSock proxy services on the proxy server. If you have configured a separate user account for the FTP service on the proxy server, the Distribution Agent or Merge Agent accesses the MSsubscription_properties table in the Subscription database to retrieve the FTP user account name and password.
When authentication is completed, the Subscriber gains access to SQL Server on port 1433 configured in the WinSock Proxy service. A logon to SQL Server is not established at this point. When the initial connection is made, only port 1433 is active. No access is granted to the FTP port 21. The Distributor server must validate the Subscriber and the subscription as well as identify the type of replication requested. Only then is a connection made to the FTP port.
After proxy server security has been established, access to all the servers required for replication has been gained. Permission has been granted to port 1433, which allows the Subscriber to initiate a direct connection to SQL Server. Using WinSock port 1433, the Subscriber establishes a connection to the Distributor server for transaction replication and to both the Distributor and Publisher servers for merge replication. You must ensure that a user account has been configured to allow the Subscriber to log in to the Publication database. This user account should not be granted any permissions, but is required for the replication process to work properly. If you are running separate Publisher and Distributor servers, you must configure a logon account on each server.
For pull subscriptions, replication requires that the Publisher server be registered on the Subscriber. The registration process requires a username and password to gain access to the SQL Server database on the Publisher server. The ability to register the Publisher server on the Subscriber means that you have established a communications link at the Windows NT level and at the SQL Server level. If you are not able to register the Publisher on the Subscriber, you must check each user account to ensure they have been granted permission to access Windows NT and SQL Server. For more information about registering SQL Server, see SQL Server Books Online.
An existing Windows NT or SQL Server login account must be used to register the Publisher server on the Subscriber. This account does not need to be granted special access rights on SQL Server except to enter the Publication database. Access rights can be as a guest or by explicitly adding this user to the database. This account must also be included in the Publication Access List (PAL) of each publication you want to grant subscription permission to the Subscriber.
This is the final security check prior to exposing data. The login used by the Replication Agent is validated against the PAL of each publication it tries to access. If the Subscriber’s login is not found in the PAL, access is denied. Using separate logins for different Subscribers in the PAL can help limit access to data in the publication.
