Advertisements

Archive

Posts Tagged ‘Operating System Error 5’

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 🙂

Advertisements

CREATE FILE encountered operating system error 5(Access is denied.)

April 23, 2010 5 comments

When trying to attach the database to SQL Server with an account other then yours you might get following error.

TITLE: Microsoft SQL Server Management Studio
——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBName.mdf’. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

——————————
BUTTONS:

OK
——————————

If you follow the link it explains nothing; a little confused I did search on the Google (yeeh?).  With no answers as to why this is happening, I made sure SQL Server had proper permissions to the MSSQL Data and Log folders.  I decided to run Process Monitor to see what service account is trying to access the file when I am attaching it; it was as expected my SQL Server Service account was trying to access the file.  So why the permission denied?  File was located in same folder as rest of mdf/ldf files…

Looking into it I saw the file had a small lock icon on it like “Lock Icon“. Looking into the permissions on the file I see it was only accessible by my user name.  I don’t know why this was the case (actually I found why, I’ll post on it tomorrow; need to do a bit more research on that topic).  I edited the permission properties on the file to inhert form parent folder.

Reattached the database with no issues.

Issue: Can’t attach mdf and ldf file to the SQL Server, keeps getting permissions deined even though file is in default data/log directory and SQL Server has access to it.
Solution: Check file to make sure it is inheriting permissions from parent and is not owned by single user.

%d bloggers like this: