Update: ** Make sure to check out the comments at the end of this post. There are some interesting differences in behavior between transactional replication (pull/push subscribers) versus merge replication's behavior. **
Yesterday I was working on implementing transactional replication with the goal of limiting the permissions each replication account ran under. I created three separate domain accounts for the snapshot, log reader and distribution agents. These accounts had no other permissions before I began:
· I created logins on the publisher and distributor (in this case, the same SQL Server instance) and I added the snapshot and log reader agent accounts to the db_owner role of the distribution and publisher databases.
· This was a push subscription, so I also added the distribution agent to the db_owner role for the distribution database, but I did not grant it access to the publication database. I did make the distribution agent a member of db_owner for the subscription database (which was located on a separate server and default instance).
· I gave the snapshot agent “write” permissions and the distribution agent “read” permissions to the snapshot share.
By the way, all this talk of db_owner makes it sound like I wasn’t limiting permissions all that much; however this fixed database role membership is indeed a minimum requirement in this implementation. It’s also typically more restrained then what I’ve seen out in the field. Usually I’ll see the use of domain accounts with sysadmin used to manage everything in the replication topology. I don’t usually see a separate set of accounts configured for each agent role, nor do I see them set up for each unique topology (for very large environments, the administrative overhead may not make this a practical choice – but that’s another discussion altogether).
I did leave out one step though – and I’ll get to that in a moment. After applying the permissions I described, I set up the new publication and new subscription, and the data flowed correctly with no issues and no sysadmin permissions required.
The step I specifically left out was the adding of the distribution agent to the Publication Access List (PAL). According to Books Online, “Access to a publication is controlled by the publication access list (PAL).” Also according to Books Online, the distribution agent for a push subscription must “Be a member of the PAL.” I wondered why? And if this is such a key area – why don’t we hear much discussion of the PAL? If you search the replication forums, you’ll find very few questions about it (searching today, I found 26 loosely related threads). Either this means that most shops use high privilege accounts and haven’t pushed further to find out the role of PAL – or the PAL role isn’t entirely what it seems to be (as its described, it seems to suggest that the distribution agent account needs membership in order to synchronize).
Now if it must be a member, why was transaction replication working properly (rows were moving fine from publisher to distributor and distributor to subscriber).
My first assumption was that I missed something or that somehow the distribution agent account was getting implied permissions either through group membership.
The first thing I validated was the current PAL list of accounts (looking explicitly for my distribution agent account – called SQLskills\SQLskillsDistAGT). Looking at the PAL, this account had NOT been explicitly granted membership somehow through other activities:
Perhaps SQLskills\SQLskillsDistAGT was gaining access through group membership? Seemed unlikely to me, but I checked nonetheless by using EXECUTE AS LOGIN and querying the sys.login_token to see the groups associated with that account:
I didn’t see any connections or group memberships that would map to the PAL.
My next thought was to examine the SQL Server Agent job and ensure it really was running under the context of SQLskills\SQLskillsDistAGT. The SQL Server Agent Job for the distribution agent was owned by the Administrator account, but the job step itself was running as the SQLskills\SQLskillsDistAGT proxy:
The proxy maps to a security credential, which in this case was my SQLskills\SQLskillsDistAGT account. I validated the mapping by querying sys.credentials (checking the credential_identity column):
So the mapping was what I expected.
But was the job really connected as that account? I ran a few test transactions at the publisher and again confirmed that rows were flowing to the subscriber. I then queried sys.dm_exec_sessions for the distribution agent session, checking the login name and running a few times to ensure it was incrementing the logical reads:
Logical reads were incrementing and the job was indeed running under the account.
So where are we? Basically, I could find no connection whatsoever between the PAL membership and my distribution agent account.
So because I wanted to be absolutely sure (and because this was a test environment) I removed all accounts from the PAL (including “sa”). I did so one-by-one, testing to see if it broke replication. And guess what? Replication just kept on working. I even restarted the agents to see if it would initiate some kind of challenge-response, and it did not.
So is PAL access required? And if so, what is the boundary of that requirement?
I logged off of my Administrator account and logged in to the publisher/distributor SQL Server instance as the SQLskills\SQLskillsDistAGT. I then opened up SSMS and looked to see if I could view the publication:
No publications to be seen, even though this account is actually responsible for running the distribution agent and is doing so successfully.
I then jumped back on my Administrator account and first added SQLskills\SQLskillsDistAGT to the public role of the publication database (required in order to be seen in PAL) and then I added SQLskills\SQLskillsDistAGT to the PAL:
After doing this, I logged aback in as the distribution agent account, and sure enough, I can now “see” the publication (and also launch a new subscription, more importantly).
So this now made sense why PAL wasn’t the talk of the town. Most DBAs I’ve worked with set up replication with their own high privilege credentials – even when designating other credentials for the replication agents. Once they do, the agents work as advertised. It’s when the agent account wishes to participate independently of the DBA that the PAL helps restrict the visibility of available publications.
If you’ve seen other variations or even contradictions related to the PAL – I’d love to hear about it. We can help flesh out some of the ambiguities around this feature on this post.
9 thoughts on “When is the Publication Access List required?”
Hi Joe,
I’ve found the PAL to work as expected with Merge Replication, e.g., preventing the Merge agent process account from synchronizing if it is not a member of the PAL. However, it doesn’t seem to be working correctly with Transactional Replication.
I honestly think you are facing a bug here. If the Distribution agent process account login is not a member of the PAL, the agent should not be able to synchronize.
Thanks Brandon! This is what I was hoping for (for folks to post variations of their PAL usage here, since there wasn’t much out there that reflected my walk through on version 10.50.2500 with transactional replication).
I’d like to repeat this specific test on a different version (push-subscriptions and transactional replication). I’ll report back here in the comments and also compare to merge replication.
Can you tell me what version you’re using (SP and any CUs specifically)? I can then compare to your exact edition. I won’t be surprised if you’re right that this was not intended, but then the next question is around which versions are impacted by this gap.
Great post!!
Do we need db_owner role in distribution database for snapshot agent?
Thanks Yagnesh!
The snapshot agent should be added as a member of db_owner for the distribution database, yes. The full list of required permissions can be found on the Replication Agent Security Model topic (http://msdn.microsoft.com/en-us/library/ms151868(v=sql.105).aspx).
While the role and directory permissions seem solid – I still have some open questions on the PAL behavior. I’ll post findings here.
Hi Brandon,
Quick update, I was able to test on a different version – SQL Server 2012 RC0 (11.0.1750).
Both scenarios had the same PAL "bypass" I documented on today’s post for transactional replication.
11.0.1750 (SQL Server 2012 RCO), transactional replication, push subscription
11.0.1750 (SQL Server 2012 RCO), transactional replication, pull subscription
I’ll try merge replication next and share my findings. Hopefully this gap only applies to transactional replication.
Another update…
So I tested 11.0.1750 (SQL Server 2012 RCO), merge replication, pull subscription.
This time I saw PAL enforcement (unlike with transactional replication). The gateway for merge replication when the merge agent isn’t in the PAL is met when calling sp_helpdistpublisher. Calling it under the merge agent account returns “Msg 14080” when the agent account isn’t in the PAL. The message is generic (as expected since it is security related) – but it does say that the failure is at Line 138. Leading up to the error, the procedure checks a few conditions (for example – dbo on the distributor and checking sys.sp_MSrepl_DistDBPALAccess for PAL access).
So then that leads to the question of why this is enforced for merge and not transactional (even though it is documented as though it is fully enforced).
So I recreated the transactional replication pull scenario to see if the remote distributor is calling the same procedures in the same way.
Long story short is – executing sp_helpdistpublisher under the context of the distributor works just fine – even when the distributor account is not in the PAL (unlike in the merge replication scenario) – as the logic in the procedure is looking for db_owner in the distribution database. If I add that merge agent, the procedure runs under that agents context (even though it isn’t in the PAL). However merge replication is enforcing the PAL even further down the workflow. Where – I’m not sure – still working my way in and will likely trace it to find out where.
Either which way – it seems the intended functionality is represented in merge – but not in pull or push subscribers for transactional replication. Fun stuff!
Joe,
I’m testing with SQL Server 2008 R2 (10.50.2500).
Removing the Merge agent process account from the PAL prevents pull subscriptions from synchronizing for me. I have not tested with push subscriptions yet, but I’m guessing they will exhibit the same behavior, I’ll let you know shortly.
Either we’re missing something, or the Distribution agent isn’t giving us any respect. I’ll play with this some more and let you know what I find.
Excellent! Thanks Brandon. If it matches my test with SQL Server 2012 RC0 – I think you’re right that we’ll see that merge follows the rules – and transactional does not.
Thanks Joe,
Sorry for my mistake. I forgot that snapshot agent resides on distributor and hence it is required db_owner permission. Thanks for refreshing that. It is really great post on replication. We rarely find this type of stuff.
Comments are closed.