Resolving CREATE FILE Encountered Operating System Error 5 in SQL Server: A Permission Issue Analysis

Error Phenomenon and Context

In SQL Server environments, users often need to attach existing database files (typically .mdf files) to server instances. However, during this operation, the following error message may appear:

CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15105) while attempting to open or create the physical file

This error commonly occurs when trying to attach database files located in system directories such as C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA. Error code 5 corresponds to the Windows system error "Access is denied," while 15105 is an internal SQL Server error code indicating permission-related issues.

Root Cause Analysis

The fundamental cause of this error is insufficient permissions. The SQL Server process (or the user performing the operation) does not have adequate access rights to the target database file. Specifically:

• When connecting to SQL Server using Windows Authentication, the current Windows user account must have read and write permissions on the database file.
• When using SQL Server Authentication, the SQL Server service account (e.g., MSSQL$SQLEXPRESS) must have appropriate permissions on the file.

In standard installations, the SQL Server service account typically has full control over its data directories (e.g., MSSQL\DATA). However, if the database file is located elsewhere or if permission settings are accidentally modified, this error can occur.

Solution 1: Run as Administrator

The most straightforward solution is to run SQL Server Management Studio (SSMS) with administrator privileges. The steps are as follows:

1. Close any currently open SSMS instances.
2. Locate SQL Server Management Studio in the Start menu.
3. Right-click and select "Run as administrator."
4. Retry attaching the database file.

This method works because administrator accounts usually have system-level full control permissions, bypassing most file system restrictions. However, this is a temporary fix and not suitable for production environments, as it may mask deeper permission configuration issues.

Solution 2: Configure Proper File Permissions

A more fundamental solution is to ensure the SQL Server service account has correct file permissions. Here are the detailed steps:

1. Identify the SQL Server service account:
   • Open the Services management tool (services.msc).
   • Find the corresponding SQL Server service (e.g., SQL Server (SQLEXPRESS)).
   • Check the account information in the "Log On" tab.
2. Configure file permissions:
   • Locate the target database file (.mdf) and its associated log file (.ldf).
   • Right-click the file and select "Properties."
   • Navigate to the "Security" tab and click "Edit."
   • Add the SQL Server service account and grant "Full control" permissions.
3. If the file is in SQL Server's default data directory, check the permission inheritance settings for that directory to ensure the service account has appropriate access.

For production environments, it is recommended to place database files in directories where the SQL Server service account already has permissions or to configure minimal necessary permissions specifically for that account.

In-Depth Understanding of Permission Mechanisms

Understanding the differences in permission checks between Windows Authentication and SQL Server Authentication is crucial:

• Windows Authentication: The SQL Server process impersonates the current Windows user for file access. This means file system permission checks are based on that Windows user's rights.
• SQL Server Authentication: File access is performed entirely by the SQL Server service account, independent of the SQL login used for connection.

This difference explains why, in some cases, users may encounter permission errors even when successfully connected via SQL Server Authentication—because the actual file operations are executed by the service account.

Preventive Measures and Best Practices

To avoid similar issues, consider the following preventive measures:

• Place database files in directories where the SQL Server service account already has appropriate permissions.
• Regularly audit permission settings for critical directories and files.
• Avoid using administrator accounts for routine database operations in production environments.
• Use SQL Server Configuration Manager to manage service accounts rather than directly modifying Windows service settings.
• When moving or copying database files, carefully check permission settings at the destination.

By understanding the fundamental principles of permission mechanisms and implementing proper configuration measures, the occurrence of such errors can be significantly reduced, ensuring stable and secure database operations.