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

Nishant Kumar 1 Reputation point
2020-09-24T01:18:45.57+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,805 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions
0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2020-09-24T02:09:30.463+00:00

    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.

    7 people found this answer helpful.
  2. Raghu Gopalakrishnan 11 Reputation points Microsoft Employee
    2020-12-21T08:13:56.303+00:00

    Launch SQL Server Management Studio as "Run As Administrator"

    2 people found this answer helpful.
    0 comments
  3. Stacy Clark 21 Reputation points
    2020-12-23T12:53:00.867+00:00

    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

    1 person found this answer helpful.
  4. Olaf Helper 41,001 Reputation points
    2020-09-24T06:14:00.057+00:00

    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.

  5. Tom Phillips 17,716 Reputation points
    2020-09-25T11:50:50.62+00:00

    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.

    0 comments