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_REPL21022Access 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?
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.
- Create a new folder on you Distribution Server.
- Grant your SQL Server Service Account(s), if you are using more than one, permissions to read/write to the folder.
- 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).
- Control the permission on the share also; so only SQL Server Service Account can read/write to it.
- Re-setup your Replication using \\ServerName\ShareName$\.
Client confirmed it worked for them 🙂
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories
- Administration (25)
- AlwaysOn Availability Groups (1)
- Announcement (17)
- Best Practices (3)
- Cluster Errors (1)
- Database Mirroring (5)
- Disaster Recovery (4)
- DNS (1)
- Documentation (1)
- Dynamic Management Views/Functions (11)
- Full Text Search (2)
- Interesting Reads (7)
- Linked Servers (1)
- Maintenance Plans (2)
- Operating System Errors (2)
- Performance Tuning (21)
- Power BI (1)
- Power BI Desktop (2)
- Published Article (2)
- Security (10)
- Smart Index Management (1)
- SQL 2000 (5)
- SQL 2005 (30)
- SQL 2008 (18)
- SQL 2008 R2 (11)
- SQL 2012 (16)
- SQL 2014 (11)
- SQL 2016 (15)
- SQL Bootstrap Errors (1)
- SQL Embedded Edition (1)
- SQL Errors (16)
- SQL Failover Cluster (2)
- SQL Internals (2)
- SQL Protocols (1)
- SQL Replication Errors (1)
- SSRS 2000 (1)
- SSRS 2008 (1)
- T-SQL Reference (4)
- Tinkering (1)
- Tools (14)
- Troubleshooting (31)
- Windows 2003 Clustering (3)
- Windows 2008 (1)
- Windows 2008 Clustering (9)
- Windows 2008 R2 (2)
- Windows 2008 R2 Clustering (3)
- Windows Errors (1)
- WMI Errors (1)
Tags
Archives
Administration Links
Blog Stats
- 110,167 hits
Oh my goodness! Incredible article dude! Thanks, However I am experiencing difficulties with your RSS. I don’t 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!!