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 e.g. 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 i.e. 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.

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.

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.