question

nishantmjk avatar image
0 Votes"
nishantmjk asked ·

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

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.

sql-server-generalsql-server-transact-sql
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
1 Vote"
Monalv-msft answered ·

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.)”

27853-addfullcontrolforusers.png

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.



· 4 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Monalv-msft

I have tried giving Full control to the file and folders for respective users. But still, it would not work.

0 Votes 0 ·

Hi @nishantmjk,

Unable to open the physical file "\\machineRemote\ShareName\sample\sample_database.mdf".

Could you please use the file in local server? Like in C:\ or D:.

Best Regards,
Mona


0 Votes 0 ·

Yes, it works if the files are in local servers. But that is not what I need exactly.

0 Votes 0 ·
Show more comments
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered ·

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.

· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@OlafHelper-2800 I have checked the service account under which SQL Server is running. I am running it under a domain user and the user has Full control over the file and folders.

Thanks for the suggestion. I understand that is not the best idea but I want to know how can I solve this issue.

0 Votes 0 ·

Do the account have permissions on the share and on NTFS file level?

0 Votes 0 ·

Yes. The account has Full permission on the share and NTFS file level both.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RaghuGopalakrishnan-9711 avatar image
0 Votes"
RaghuGopalakrishnan-9711 answered ·

Launch SQL Server Management Studio as "Run As Administrator"

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

StacyClark-5656 avatar image
0 Votes"
StacyClark-5656 answered ·

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.

50846-image-1.png


50847-image-2.png



image-1.png (69.6 KiB)
image-2.png (86.6 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.