Share via

Security Architecture for Web Synchronization

  • Article

Microsoft SQL Server enables fine-grained control over the configuration of Web synchronization security. This topic provides a comprehensive list of all the components that can be included in a Web synchronization configuration and information about the connections that are made between components. When possible, use Windows Authentication.

The following illustration shows all the possible connections, but some connections might not be required in a particular topology. For example, a connection to an FTP server is required only if the snapshot is delivered by using FTP.

Components and connections in Web synchronization

The following tables describe the components and connections that are shown in the illustration.

A. Windows User Under Which the Merge Agent Runs

During synchronization, the Merge Agent (A) is started at the Subscriber. The Merge Agent can be started from a SQL Server Agent job step or from a stand-alone custom application. If the Merge Agent is started from a SQL Server Agent job step, the Merge Agent runs under the context of a Windows user that you specify. If you do not specify a Windows user, the Merge Agent runs under the context of the Windows service account for SQL Server Agent.

Type of account Where the account is specified
Windows user Transact-SQL: the @job_login and @job_password parameters of sp_addmergepullsubscription_agent.

RMO (Replication Management Objects): the Login and Password properties for SynchronizationAgentProcessSecurity.
Windows service account for SQL Server Agent SQL Server Configuration Manager
Stand-alone application The Merge Agent runs under the context of the Windows user that is running the application.

B. Connection to the Subscriber

The Merge Agent connects to the Subscriber by using Windows Authentication or SQL Server Authentication. The Windows user or SQL Server login that you specify must be associated with a database user that is a member of the dbowner fixed database role in the subscription database.

Note

Windows Authentication is always used when the Merge Agent is started from a SQL Server Agent job. Windows Authentication is also used when the Merge Agent is started programmatically unless SQL Server Authentication is explicitly specified.

Type of authentication Where the authentication is specified
Windows Authentication. The Merge Agent makes connections under the context of the Windows user that is specified for the Merge Agent (A).
SQL Server Authentication is used only if the following is specified:

RMO: a value of Standard for SubscriberSecurityMode.

Merge Agent command line: a value of 0 for SubscriberSecurityMode.		 RMO: SubscriberLogin and SubscriberPassword.

Merge Agent command line: -SubscriberLogin and -SubscriberLogin.

C. Connection to an Outgoing Proxy Server

Specify a Windows user for this connection only if there is an outgoing proxy server that restricts access to the internal network of the Subscriber.

Type of authentication Where the authentication is specified
Windows Authentication RMO: InternetProxyLogin and InternetProxyPassword with InternetProxyServer.

Merge Agent command line: -InternetProxyLogin and -InternetProxyPassword with -InternetProxyServer.

D. Connection to IIS

After connecting to the Subscriber and extracting any changes from the subscription database, the Merge Agent makes an HTTPS request to Microsoft Internet Information Services (IIS) and uploads data changes as an XML message. The Merge Agent must have logon permissions to IIS.

Type of authentication Where the authentication is specified
Basic Authentication is used if one of the following is specified:

Transact-SQL: a value of 0 for the @internet_security_mode parameter of sp_addmergepullsubscription_agent.

RMO: a value of Standard for InternetSecurityMode.

Merge Agent command line: a value of 0 for -InternetSecurityMode.		 Transact-SQL: the @internet_login and @internet_password parameters of sp_addmergepullsubscription_agent.

RMO: InternetLogin and InternetPassword.

Merge Agent command line: -InternetLogin and -InternetPassword.
Integrated Authentication* is used if one of the following is specified:

Transact-SQL: a value of 1 for the @internet_security_mode parameter of sp_addmergepullsubscription_agent.

RMO: a value of Integrated for InternetSecurityMode.

Merge Agent command line: a value of 1 for -InternetSecurityMode.		 The Merge Agent makes connections under the context of the Windows user that is specified for the Merge Agent (A).

*Integrated authentication can be used only if all computers are in the same domain or are in multiple domains that have trust relationships with each other.

Note

Delegation is required if you use Integrated Authentication. We recommend that you use Basic Authentication and SSL for connections from the Subscriber to IIS.

E. Connection to the Publisher

The SQL Server Replication Listener and Merge Replication Reconciler components are hosted on the computer that is running IIS. These components perform the following actions:

  • Pick up the HTTPS request that is described in the section "D. Connection to IIS".

  • Make an SQL connection to the publication database and apply the uploaded changes to the publication database.

  • Extract the downloaded changes and send an HTTPS response back to the Merge Agent.

The Merge Replication Reconciler connects to the Publisher by using either Windows Authentication or SQL Server Authentication. The Windows user or SQL Server login that you specify must comply with the following:

  • Be in the publication access list (PAL). For more information, see Secure the Publisher.

  • Be associated with a user in the publication database.

Type of authentication Where the authentication is specified
Windows Authentication is used if one of the following is specified:

Transact-SQL: a value of 1 for the @publisher_security_mode parameter of sp_addmergepullsubscription_agent.

RMO: a value of Integrated for PublisherSecurityMode.

Merge Agent command line: a value of 1 for -PublisherSecurityMode.		 The Merge Agent makes connections to the Publisher under the context of the Windows user that is specified for the connection to IIS (D). If the Publisher and IIS are on different computers and Integrated Authentication is used for the connection (D), you must enable Kerberos delegation on the computer that is running IIS. For more information, see the Windows documentation.
SQL Server Authentication is used if one of the following is specified:

Transact-SQL: a value of 0 for the @publisher_security_mode parameter of sp_addmergepullsubscription_agent.

RMO: a value of Standard for PublisherSecurityMode.

Merge Agent command line: a value of 0 for -PublisherSecurityMode.		 Transact-SQL: the @publisher_login and @publisher_password parameters of sp_addmergepullsubscription_agent.

RMO: PublisherLogin and PublisherPassword.

Merge Agent command line: -PublisherLogin and -PublisherPassword.

F. Connection to the Distributor

The Merge Replication Reconciler that is hosted on the computer that is running IIS also makes connections to the Distributor. The Merge Replication Reconciler connects to the Distributor by using either Windows Authentication or SQL Server Authentication. The Windows user or SQL Server login that you specify must comply with the following:

  • Be in the publication access (PAL). For more information, see Secure the Publisher.

  • Be associated with a database user in the distribution database. The user can be the Guest user.

The snapshot share is typically on the Distributor. For more information about snapshot shares, see the section "H. Access to the snapshot share" later in this topic.

Type of authentication Where the authentication is specified
Windows Authentication is used if one of the following is specified:

Transact-SQL: a value of 1 for the @distributor_security_mode parameter of sp_addmergepullsubscription_agent.

RMO: a value of Integrated for DistributorSecurityMode.

Merge Agent command line: a value of 1 for -DistributorSecurityMode.		 The Merge Agent makes connections to the Distributor under the context of the Windows user that is specified for the connection to IIS (D). If the Distributor and IIS are on different computers and Integrated Authentication is used for the connection (D), you must enable Kerberos delegation on the computer that is running IIS. For more information, see the Windows documentation.
SQL Server Authentication is used if one of the following is specified:

Transact-SQL: a value of 0 for the @distributor_security_mode parameter of sp_addmergepullsubscription_agent.

RMO: a value of Standard for DistributorSecurityMode.

Merge Agent command line: a value of 0 for -DistributorSecurityMode.		 Transact-SQL: the @distributor_login and @distributor_password parameters of sp_addmergepullsubscription_agent.

RMO: DistributorLogin and DistributorPassword

Merge Agent command line: -DistributorLogin and -DistributorPassword.

G. Connection to an FTP Server

Specify a Windows user for this connection only if you will download snapshot files from an FTP server, instead of from a UNC location, to the computer that is running IIS before you apply the snapshot to the Subscriber. For more information, see Transfer Snapshots Through FTP.

Type of authentication Where the authentication is specified
Windows Authentication Transact-SQL: the @ftp_login and @ftp_password parameters of sp_addmergepublication.

RMO: FtpLogin and FtpPassword.

H. Access to the Snapshot Share

The snapshot share is accessed by the Merge Replication Reconciler that is hosted on the computer that is running IIS.

Type of authentication Where the authentication is specified
Windows Authentication The Merge Agent accesses the snapshot share under the context of the Windows user that is specified for the connection to IIS (D). If the snapshot share and IIS are on different computers and Integrated Authentication is used for the connection (D), you must enable Kerberos delegation on the computer that is running IIS. For more information, see the Windows documentation.

I. Application Pool Account for IIS

This account is used to start the W3wp.exe process on the computer that is running IIS for Windows Server 2003 or the Dllhost.exe process on Windows 2000. These processes host applications on the computer that is running IIS, such as the SQL Server Replication Listener and Merge Replication Reconciler. This account should have read and execute permissions on the following replication DLLs on the computer that is running IIS:

  • Replisapi

  • Replrec

  • Replprov

  • Msgprox

  • Xmlsub

The account should also be part of IIS_WPG group. For more information, see the section "Setting Permissions for the SQL Server Replication Listener" in Configure IIS for Web Synchronization.

Type of account Where the account is specified
Any Windows user that has the required permissions. Internet Information Services (IIS) Manager.

See Also

Configure Web Synchronization
Replication Merge Agent