Replication Proxies, Credentials and Subsystems

Introduction

In this article I'd like to look a little at the new option for the security of replication jobs and hopefully this'll give a better understanding of how this really works and provide some insight to help when when troubleshooting. Firstly, what is the new option I'm referring to? This is something you'll have noticed already - anyone who has started creating publications using the wizards in SSMS will have spotted one screen in the wizard  (below) which is quite unlike the screens used in SQL Server 2000. On this screen there is now the option to "Run under the following Windows account":

This is of course referring to the relevant replication agent and therefore replication job - snapshot in the above case. There are similar screens visible in the publication properties screen and the wizard used when creating a subscriber. In other words, this new option applies to all the agents used in replication. As we would expect, this also exists in the replication stored procedures: there are 2 new arguments "@job_login" and "@job_password" in each of those replication stored procedures related to job creation e.g. sp_addpublication_snapshot.

Why use the new option?

If we backtrack a little to SQL Server 2000, how did the job security work there? Well, the jobs ran under the same user that the SQL Server agent used. That is why there was a requirement for the agent of pull subscribers to have rights to the snapshot folder on the publisher, and therefore to be a domain user (let's forget the rare case of replicating to the same box for the moment!). In SQL Server 2005 we now have the option to use an account different to that of the SQL Server Agent. Why is this useful? In SQL Server 2000 the SQL Server Agent often runs as a high-privileged domain-user account. Sometimes this account is even a domain admin, sometimes it is the same domain user account used on both the publisher and subscriber, which is usually also a local administrator on each machine. In other words, the account usually has a lot of rights on the boxes involved, and users gaining access to run scripts under this account user could do a lot of damage. One possible way this could be exploited is using sp_addscriptexec. This stored procedure is used in replication to send a script to each subscriber and execute it there. It can come in useful when making changes which don't necessitate a reinitialization eg adding new indexes or changing stored procedures. Anyway, to run sp_addscriptexec you just need to be in the db_owner role. So, if replication was using a windows user with only the required rights, sp_addscriptexec couldn't be taken advantage of ie this allows users to implement the principal of minimum privilege.

How it works: (a) Proxies and Credentials

To understand what actually happens under the covers, and where the info is stored etc, we need to use some new terminology and introduce "Credentials" and "Proxies".

From BOL: "A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server and most credentials include a Windows user and password. A SQL Server Agent proxy manages security for job steps that involve subsystems other than the TSQL subsystem. Each proxy corresponds to a security credential".

So, we would expect the user "groupbases\ibisonp" in the above screen to become a credential and there must therefore be a proxy somewhere that links this to a snapshot job. Fortunately, these entities are both represented graphically in SSMS and I've expanded the relevant nodes below:

The verbose name ("[REPL][groupbases\ibisonp][UK-HOSTbases-Pub1-test-UK-HOSTbases-30]") used in the credential and proxy is actually quite useful - it shows that they are related to replication ("[REPL]"), the windows user impersonated ("[groupbases\ibisonp]") and the actual job this applies to ("[UK-HOSTbases-Pub1-test-UK-HOSTbases-30]"). In the above example, I selected the process account "groupbases\ibisonp". If this is not suitable, we can reverse the decision made in the replication job by using the "Run as:" dropdown (below) and selecting the "SQL Server Service Account" which would return us to the SQL Server 2000 behaviour.

So, we can change back to the behaviour of SQL Server 2000 by choosing the top option, or we can leave it as the "groupbases\ibisonp" proxy. As a matter of interest, it is possible to create a new credential and proxy manually using TSQL ("create credential" and "sp_add_proxy" and "sp_grant_proxy_to_subsystem") which will then appear in the "Run as:" dropdown. Another way to create and associate a new proxy is to use sp_changepublication_snapshot.

So, to summarise, we can create proxies and credentials by

(a) the wizard's "Run under the following windows account" option

(b) using Create Credential, sp_add_proxy and sp_grant_proxy_to_subsystem

(c) using sp_changepublication_snapshot

How it works: (b) Replication Subsystems

Now we know about Proxies and Credentials, we can look at how these are used by the various processes. Basically, the snapshot agent and the replication subsystem used by the SQL Server Agent are two distinct components. The snapshot agent (snapshot.exe) is responsible for generating the snapshot files whereas the replication subsystem (sqlrepss90.dll) is responsible for launching the snapshot agent (to see more information on each subsystem, the relevant dll and executable, you can run "select * from msdb.dbo.syssubsystems"). The following listing explains the relationships.

SQL Server Agent → Job → Replication Subsystem → Proxy → Credential → Snapshot Agent

Conclusions

Hopefully this article helps understand the new security options which appear throughout the replication wizards. The principal of minimum privilege is definitely worth adhering to, otherwise a malicious replication user could take advantage of your existing setup, and in most cases do whatever he wanted on the complete subscriber box. Setting up replication using proxies and credentials is simple - either automatically or manually - and troubleshooting it once you understand the topology should be pretty straightforward.