Edit

Replication Security Best Practices

  • Article

Applies to: SQL Server

Replication moves data in distributed environments ranging from intranets on a single domain to applications that access data between untrusted domains and over the Internet. It is important to understand the best approach for securing replication connections under these different circumstances.

The following information is relevant to replication in all environments:

  • Encrypt the connections between computers in a replication topology using an industry standard method, such as Virtual Private Networks (VPN), Transport Layer Security (TLS), previously known as Secure Sockets Layer (SSL), or IP Security (IPSEC). For more information, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager). For information about using VPN and TLS for replicating data over the Internet, see Securing Replication Over the Internet.

    If you use TLS to secure the connections between computers in a replication topology, specify a value of 1 or 2 for the -EncryptionLevel parameter of each replication agent (a value of 2 is recommended). A value of 1 specifies that encryption is used, but the agent does not verify that the TLS/SSL server certificate is signed by a trusted issuer; a value of 2 specifies that the certificate is verified. Agent parameters can be specified in agent profiles and on the command line. For more information, see:

  • Run each replication agent under a different Windows account, and use Windows Authentication for all replication agent connections. For more information about specifying accounts, see Identity and access control for replication.

  • Grant only the required permissions to each agent. For more information, see the "Permissions Required by Agents" section of Replication Agent Security Model.

  • Ensure all Merge Agent and Distribution Agent accounts are in the publication access list (PAL). For more information, see Secure the Publisher.

  • Follow the principle of least privilege by allowing accounts in the PAL only the permissions they need to perform replication tasks. Do not add the logins to any fixed server roles that are not required for replication.

  • Configure the snapshot share to allow read access by all Merge Agents and Distribution Agents. In the case of snapshots for publications with parameterized filters, ensure that each folder is configured to allow access only to the appropriate Merge Agent accounts.

  • Configure the snapshot share to allow write access by the Snapshot Agent.

  • If you use pull subscriptions, use a network share rather than a local path for the snapshot folder.

If your replication topology includes computers that are not in the same domain or are in domains that do not have trust relationships with each other, you can use Windows Authentication or SQL Server Authentication for the connections made by agents (For more information about domains, see the Windows documentation). It is recommended as a security best practice that you use Windows Authentication.

  • To use Windows Authentication:

    • Add a local Windows account (not a domain account) for each agent at the appropriate nodes (use the same name and password at each node). For example, the Distribution Agent for a push subscription runs at the Distributor and makes connections to the Distributor and Subscriber. The Windows account for the Distribution Agent should be added to the Distributor and Subscriber.

    • Ensure that a given agent (for example the Distribution Agent for a subscription) runs under the same account at each computer.

  • To use SQL Server Authentication:

    • Add a SQL Server account for each agent at the appropriate nodes (use the same account name and password at each node). For example, the Distribution Agent for a push subscription runs at the Distributor and makes connections to the Distributor and Subscriber. The SQL Server account for the Distribution Agent should be added to the Distributor and Subscriber.

    • Ensure that a given agent (for example the Distribution Agent for a subscription) makes connections under the same account at each computer.

    • In situations that require SQL Server Authentication, access to UNC snapshot shares is often not available (for example access might be blocked by a firewall). In this case, you can transfer the snapshot to Subscribers through file transfer protocol (FTP). For more information, see Transfer Snapshots Through FTP.

See Also

Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)
Replication over the Internet
Secure the Subscriber
Secure the Distributor
Secure the Publisher
View and modify replication security settings