MSSQLSERVER_5120

Článok
05/12/2023

Applies to: SQL Server

Details

Attribute	Value
Product Name	SQL Server
Event ID	5120
Event Source	MSSQLSERVER
Component	SQLEngine
Symbolic Name	DSK_FCB_FAILURE
Message Text	Table error: Unable to open the physical file "%.*ls". Operating system error %d: "%ls".

Explanation

SQL Server was unable to open a database file. The operating system error provided in the message points to more specific underlying reasons for the failure. You may commonly see this error together with other errors like 17204 or 17207.

User action

Diagnose and correct the operating system error, then retry the operation. There are multiple states that can help Microsoft narrow down the area in the product where the area is occurring.

Access is denied

If you are getting the Access is Denied operating system error = 5, consider these methods:

Check the permissions that are set of the file by looking at the properties of the file in Windows Explorer. SQL Server uses Windows groups to provision Access Control on the various file resources. Make sure the appropriate group [with names like SQLServerMSSQLUser$ComputerName$MSSQLSERVER or SQLServerMSSQLUser$ComputerName$InstanceName] has the required permissions on the database file that is mentioned in the error message. Review Configure File System Permissions for Database Engine Access for more details. Ensure that the Windows group actually includes the SQL Server service startup account or the service SID.
Review the user account under which the SQL Server service is currently running. You can use the Windows Task Manager to get this information. Look for the "User Name" value for the executable "sqlservr.exe". Also if you recently changed the SQL Server service account, know that the supported way to do this operation is to use the SQL Server Configuration Manager utility.
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. Review the topic Securing Data and Log Files to understand which operation sets what permission and to which accounts. Use a tool like Windows SysInternals Process Monitor to understand if the file access is happening under the security context of the SQL Server instance service startup account [or Service SID] or an impersonated account.

If SQL Server is impersonating the user credentials of the login that executes the ALTER DATABASE or CREATE DATABASE operation, you will notice the following information in the Process Monitor tool (an example).
```
Date & Time:      3/27/2010 8:26:08 PM
Event Class:        File System
Operation:          CreateFile
Result:                ACCESS DENIED
Path:                  C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\attach_test.mdf
TID:                   4288
Duration:             0.0000366
Desired Access:Generic Read/Write
Disposition:        Open
Options:            Synchronous IO Non-Alert, Non-Directory File, Open No Recall
Attributes:          N
ShareMode:       Read
AllocationSize:   n/a
Impersonating: DomainName\UserName
```

Attaching files that reside on a network-attached storage

If you cannot re-attach a database that resides on network-attached storage, a message like this may be logged in the Application log.

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "\\servername\sharename\filename.mdf". Operating system error 5: (Access is denied.).

This problem occurs because 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.

To resolve, follow these steps:

Use the -T startup option to start SQL Server. Use this startup option to turn on trace flag 1802 in SQL Server Configuration Manager (see Trace Flags for information on 1802). For more information about how to change the startup parameters, see Database Engine Service Startup Options.
Use the following command to detach the database.
```
 exec sp_detach_db DatabaseName
 go 
```

Use the following command to reattach the database.

exec sp_attach_db DatabaseName, '\\Network-attached storage_Path\DatabaseMDFFile.mdf', '\\Network-attached storage_Path\DatabaseLDFFile.ldf'
go

Zdieľať cez