SQL Canada (SQLCAN)

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?

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 🙂