Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

SQL Server error 5120: Unable to open the physical file, operating system error 5 (Access is denied)

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

Error 5120 with "Operating system error 5: Access is denied" means Windows refused SQL Server access to a database file, as happens when you attach files you have copied or moved. Which account was refused depends on the operation: it can be the SQL Server service, or the Windows account of the person running the attach. Find that account first, then give it access to the folder and repeat the attach.

Granting permissions across a whole drive on a guess is a poor fix. The checks below let you name the account before you change anything.

What the error means

Microsoft documents error 5120 with the message text: Table error: Unable to open the physical file "%.*ls". Operating system error %d: "%ls". The symbolic name is DSK_FCB_FAILURE. Microsoft's explanation is that SQL Server was unable to open a database file and that the operating system error "points to more specific underlying reasons". The page says you may see it together with errors 17204 or 17207, which also report files SQL Server could not open.

Operating system error 5 is "Access is denied". Microsoft's user-action list for it names several things to check:

  • The permissions on the file in Windows Explorer. SQL Server uses Windows groups for file access, with names like SQLServerMSSQLUser$ComputerName$MSSQLSERVER, and the group must include the service startup account or the service SID.
  • The account the SQL Server service runs under, which you can read from Task Manager as the "User Name" for sqlservr.exe. If the service account changed, Microsoft says the supported way to change it is SQL Server Configuration Manager.
  • The account used for impersonation. "Depending on the type of operation (opening databases during server startup, attaching a database, database restore, and so on), the account that is used for impersonation and accessing the database file may vary."

Microsoft's page on securing data and log files explains the attach case. When you detach or attach, the Database Engine "tries to impersonate the Windows account of the connection performing the operation" to confirm that account can reach the files. For SQL Server logins, the page says impersonation might fail. After an attach where the connecting account can be impersonated, permissions go to the service account and members of the local Administrators group. Where it cannot be impersonated, only the service account gets them. After a detach where the account can be impersonated, only the account that detached gets permissions.

Since SQL Server 2012, Microsoft says, permissions are assigned to the per-service SID. The Database Engine per-service SID is NT SERVICE\MSSQLSERVER for a default instance and NT SERVICE\MSSQL$InstanceName for a named instance.

Likely causes

Cause What you see How to confirm
Files copied from another server or folder carry permissions that exclude this instance 5120 on the first attach; files copied by an administrator or a backup tool File Security tab or icacls output lacks the per-service SID
The Windows account running the attach has no NTFS rights on the files Attach by a Windows login that can use SQL Server but not the folder Process Monitor shows ACCESS DENIED with an "Impersonating" line naming that account
Database was detached and only the detaching account has rights Files were detached on this server; another admin reattaches Only one account appears on the file ACL
Attach by a SQL Server login, so impersonation fails Same operation works for a Windows login Microsoft: permissions then go only to the service account
Service account changed outside Configuration Manager Errors began after a service-account change File ACL names the old account only
Files on a network share or NAS Path begins with \\server\share Share and NTFS permissions both need checking

Prove the cause before changing anything

Everything here is read-only. Replace the paths with yours.

Find the account the service runs under. Microsoft documents service_account in sys.dm_server_services.

SELECT servicename, service_account
FROM sys.dm_server_services;

Look at the file's current permissions with icacls, which Microsoft documents as the command that displays DACLs when given just a file name.

icacls "D:\SQLData\SalesDB.mdf"

Read the output for the per-service SID, for the service account, and for the Windows account that ran the attach. Do the same for the .ndf and .ldf files. The error names the one file that failed, so check that one first; the others may also lack access, which would cause the next error after you fix the first.

If the ACLs look right, Microsoft points to the Sysinternals tool Process Monitor. Filter on the file name and run the attach again. Microsoft shows a sample row from that tool in its 5120 page: Operation CreateFile, Result ACCESS DENIED, a Path ending in attach_test.mdf, and an "Impersonating: DomainName\UserName" line. If that line names the person running the attach, SQL Server is using that person's rights for the open. If no Impersonating line appears, the open was not made under an impersonated account, so look at the service account and per-service SID instead.

Record the SQL Server error log lines next to the failure. If the attach is part of a restore or a startup, an error 17204 naming the same file shows the underlying open failure.

Good and bad results here are described in words, because Microsoft shows one format only for Process Monitor: a good result shows the file access ending in SUCCESS under the account you expect, and a bad result shows ACCESS DENIED.

Fix

Grant the per-service SID access to the folder. Microsoft's page on file-system permissions describes this for files placed in a different location: add the per-service SID with Full control on the folder. In Explorer, that is Properties, Security, Edit, Add, then the name NT SERVICE\MSSQLSERVER (or NT SERVICE\MSSQL$InstanceName), with the Locations box set to the computer name. Microsoft notes: "If the validation fails, it might advise you that the name wasn't found. When you select OK, a Multiple Names Found dialog box appears." Then select the per-service SID from that dialog box.

The same grant from a command prompt uses the documented /grant syntax, with (OI) and (CI) as the inheritance rights and F for full access. Run the plain icacls "D:\SQLData" first and keep its output, so you know the folder's state before the change.

icacls "D:\SQLData" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F"

Risk: this changes the permissions on a folder. The (OI)(CI) entry is inheritable, so files created in the folder later receive it, and so do existing files that inherit from the folder; files whose inheritance is disabled, or that were moved within the volume with their own permissions, do not. Re-run icacls on the failing file to see whether it carries the entry. To back out, run icacls "D:\SQLData" /remove "NT SERVICE\MSSQLSERVER", which Microsoft documents as removing all occurrences of that SID from the DACL. If the SID already held a different entry before your change, that entry goes too, so compare against the output you kept. Grant to the folder that holds the database files, never to the root of a drive.

Attach was run by an account without NTFS rights. Either run the attach as a Windows account that has rights, or give that account read and write on the files. Microsoft's table says the Database Engine tries to impersonate the connecting Windows account, so the account running the attach needs access as well as the service.

Risk: giving a person's account read and write on database files lets that person copy the files. Grant only the accounts that must attach databases, on the specific folder. To back out, remove that account from the folder's permissions.

Attach by a SQL Server login. Microsoft says impersonation might fail for mixed-security accounts that use SQL Server logins, and the service account is then the only one granted permissions. Use a Windows login with rights, or grant the service account full control first.

Risk: granting the service account full control on the files changes the file permissions for every database in that folder, and a broad grant weakens file protection. Grant to the specific files or folder only. To back out, remove the entry you added, and compare against the icacls output you kept first.

Service account changed in Windows Services. Microsoft says the operating system administrator must then grant the new account Full Control on all database and log files.

Risk: this is a permission grant on every database file the instance uses, and the old account's entries remain on the files unless you remove them. To back out, remove the new account's entries and set the service account again in SQL Server Configuration Manager, which is the supported way to change it.

Files on network-attached storage. Microsoft's 5120 page gives the cause as "SQL Server resets the file permissions when the database is detached. When you try to reattach the database, a failure occurs because of limited share permissions." Its procedure uses trace flag 1802 at startup (the -T startup option), a detach with sp_detach_db, and a reattach with sp_attach_db. The page does not explain what the flag does.

Disruptive step: the NAS procedure edits the SQL Server startup parameters (-T1802), which needs a service restart, and it detaches the database, which takes it offline for users. To back out, remove the startup parameter and restart, then reattach. Test it outside production first. Microsoft's sp_attach_db page begins "This feature will be removed in a future version of SQL Server" and says "We recommend that you use CREATE DATABASE <database_name> FOR ATTACH instead", which is the form Microsoft's current attach page lists first.

Confirm the fix

Run the attach again. The statement should complete without 5120, and sys.databases should show the database ONLINE. Re-run icacls on the file and confirm both the per-service SID and the account that ran the attach appear with the rights you expect.

Optionally, to prove that the service identity rather than a person's rights carries the open, restart the instance in a maintenance window and confirm the database comes online on its own.

Disruptive step: restarting the SQL Server service drops every connection and interrupts every application until the databases recover. Do it only in an agreed maintenance window. There is nothing to back out; if the database does not open, go back to the icacls output and the error log.

Example (illustrative): on sql01, an administrator copies three files from fs01 to D:\SQLData: SalesDB.mdf (62 GB), SalesDB_2.ndf (38 GB) and SalesDB_log.ldf (10 GB), a total of 110 GB. The attach, run as CONTOSO\dba1, fails on the .mdf with error 5120 and operating system error 5. icacls shows that D:\SQLData grants rights only to CONTOSO\dba1 and BUILTIN\Administrators, because the folder was created by hand. The service account listed in sys.dm_server_services is NT SERVICE\MSSQLSERVER. The administrator keeps the icacls output, grants Full control to that SID on the folder, and the attach succeeds. A second attach of a 5 GB database from the same folder works without further changes because the grant is inherited.

What to watch afterwards

  • Create the data folder with the per-service SID granted before copying files into it.
  • After any service-account change, use Configuration Manager and re-check the ACLs on every database folder.
  • Keep the icacls output from before and after the change with the change record.
  • Alert on 5120 and 17204 in the error log, since both report a file SQL Server could not get at.
  • Document which account runs attaches, so the impersonation path is known.

Risks and limits

These commands have not been run or lab-tested. The per-service SID steps come from Microsoft's current file system permissions page, which covers SQL Server 2012 and later; the impersonation table comes from an older page kept in the documentation archive. The detach-and-attach table was written for earlier versions and uses MSSQLSERVER as the service name; instance names and per-service SID names differ for named instances.

Granting Full control to a broad group or a whole drive weakens file protection for every database on the server. Grant only to the per-service SID and the accounts that need it, on the specific folder. Do not detach a production database to test a permission change.

Sources