Unable to open the physical file. Operating system error 5: “5(Access is denied.)”

Question

I am trying to work on a query to attach a database (UNC path) remotely. I have SQL Server management studio (SSMS) installed in SQL Server machine (machineSQL) and also in another machine (machineRemote) in same domain.

Query::

CREATE DATABASE "simpleData" ON 
(FILENAME = '\\machineRemote\ShareName\sample\sample_database.mdf'),
(FILENAME = '\\machineRemote\ShareName\sample\sample_database_log.ldf')
FOR ATTACH;

If I run the query in SSMS on machineSQL. The database gets attached successfully. But if I run it in SSMS on machineRemote it throws an error like :

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "\\machineRemote\ShareName\sample\sample_database.mdf". Operating system error 5: "5(Access is denied.)".
Msg 1802, Level 16, State 7, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

NOTE : The issue is occurring only if windows authentication is used to log in SSMS. SQL Server authentication works just fine. I have tried domain user permission on the database file path and also tried running SQL Server services as domain user, still no luck.

Need help on this.

Answer 1

Hi @NishantKumar-1885,

Please add the Full Control permission for the windows authentication user in the file property.

Please refer to the following link and picture:

SQL Server Operating system error 5: “5(Access is denied.)”

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Launch SQL Server Management Studio as "Run As Administrator"

Answer 3

SQL Server database engine service account must have permissions to read/write in the new folder.
To fix it, I did the following:
Added the Administrators Group to the file security permissions with full control for the Data file (S:) and the Log File (T:).
Attached the database and it works fine.

Answer 4

for respective users

Not the user accesses the database file, it's the database engine with it' service account and that service account needs full access.

BTW, hosting a SQL Server database file on a NAS share is not the best idea, one network hick-up and the database may gets corrupt; a SAN or local disks are a better solution.

Answer 5

The SQL Server service account requires access to the files, not the user running the account.

If you detached the file, the file rights are set to the service account on the server you dethatched the files from. You must reset the rights to be accessible to the "machineRemote" SQL Server service account.