Archive for the ‘SQL Replication Errors’ Category

rReplication Configuration Issue

November 20, 2011 1 comment

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 🙂

%d bloggers like this: