rReplication Configuration Issue

Client Question

Replication setup keeps failing, permissions are correct.  Why does it keep giving me error code 5 (access denied).

Error Description

Command attempted:

\\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc\ReplFolder\WOI_WorkOrde180608a5_10125.pre
(Transaction sequence number: 0x0000005F0001433C00CC00000000, Command ID: 10128)

Error messages:

The process could not read file ‘\\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata\unc\ReplFolder\20111114154553\WOI_WorkOrde180608a5_10125.pre’ due to OS error 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21022)
Get help: http://help/MSSQL_REPL21022

Access is denied.
(Source: MSSQL_REPL, Error number: MSSQL_REPL5)
Get help: http://help/MSSQL_REPL5

The error message is fairly clear; OS Error 5 is error messages returned from Windows API when it is trying to read from the file.  But why was he getting this error?  All permissions are set; SQL Server (Publisher) is able to write to it, why wouldn’t the SQL Server (Subscriber) be able to read it if it is running under same service account?

@SQLCAN: Replication Configuration Issue - Setup Screen
Replication Configuration Issue – Setup Screen

If you take note of the image the location the file is written to; its \\ServerName\R$\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\..; problem with this is the R$ share.  This is an administrative share, you must grant the SQL Server service account local administrative permissions on your Distributor to be able to read the file from there; which is against security best-practices.  Therefore a better way to do is to use UNC paths.

  1. Create a new folder on you Distribution Server.
  2. Grant your SQL Server Service Account(s), if you are using more than one, permissions to read/write to the folder.
  3. Create a HIDDEN share on the folder (any share with $ after words is hidden share; I generally think anything that doesn’t need to be public on network; hide it).
  4. Control the permission on the share also; so only SQL Server Service Account can read/write to it.
  5. Re-setup your Replication using \\ServerName\ShareName$\.

Client confirmed it worked for them 🙂

One comment

  1. Oh my goodness! Incredible article dude! Thanks, However I am experiencing difficulties with your RSS. I dont know why I can’t join it. Is there anybody else getting identical RSS issues? Anyone who knows the solution can you kindly respond? Thanx!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.