What people get wrong about how SQL Server accesses files

184

In this post I clarify some misconceptions that I’ve seen regarding how SQL Server accesses files and potential file access denied errors.

Intro

This is one of those things that’s obvious for anyone with a bit of SQL Server and Windows experience, but stumps a lot of newcomers.
Especially when it comes to students going through tasks like creating and restoring databases.

It’s also one of those things that I have to explain on a regular basis, so might as well have detailed explanation with examples I can direct people to in the future.

The latest example being this reddit post in r/SQL, where the wrongest answer has the highest upvotes.

Scenarios

First off, I want to cover the scenarios that tend to spark this type of conversation.

In all of these cases the files and folders reside on the same machine as the instance.

Database restores

For this I’ll be using the AdventureWorks2019 sample database provided by Microsoft.

From backups stored not in the default backup location

This one I see fairly often. Someone downloads a .bak file and puts it in a random directory or leaves it in their Downloads folder and then attempts a restore.

So, I’ve saved the AdventureWorks2019.bak file in my Downloads folder and run the restore.

And I get the following error:

Msg 3201, Level 16, State 2, Line 3
Cannot open backup device ‘C:\Users\Vlad\Downloads\AdventureWorks2019.bak’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

If I would have tried to do this through the Database Restore GUI in SSMS, the .bak file wouldn’t even show up because I can’t navigate to the Downloads folder.

With moving the database files to non-default data and TLog locations

The other variation of this scenario consists of trying to restore a backup and specifying custom paths that are located in directories with tighter permissions.
So, I have my .bak file in the instance’s default backup location, but I want the resulting .mdf and .ldf files to be located in a folder I have just created.

In this case, the location of the data and log files would be C:\Program Files\AdventureWorksDBFiles\.

The error is the same – Operating system error 5(Access is denied.); but the message is different:

Msg 3634, Level 16, State 1, Line 3
The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘C:\Program Files\AdventureWorksDBFiles\AdventureWorks2019.mdf’.
Msg 3156, Level 16, State 5, Line 3
File ‘AdventureWorks2019’ cannot be restored to ‘C:\Program Files\AdventureWorksDBFiles\AdventureWorks2019.mdf’. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 3
The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘C:\Program Files\AdventureWorksDBFiles\AdventureWorks2019_log.ldf’.
Msg 3156, Level 16, State 5, Line 3
File ‘AdventureWorks2019_log’ cannot be restored to ‘C:\Program Files\AdventureWorksDBFiles\AdventureWorks2019_log.ldf’. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 3
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

And all messages are complaining about that path.

Database Creation

Another scenario is with scripts that create test/demo databases that insist to specify a location for the database files.

I’ll reuse the same location as in my previous example – C:\Program Files\AdventureWorksDBFiles\

And another similar error message.

Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\Program Files\AdventureWorksDBFiles\TestAccessToPath.mdf’.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

The common denominator

The pattern is fairly obvious here, all the above operations get the same error – operating system error 5(Access is denied.)

The error message is pretty straightforward, one of the file access operations that’s carried out by the above commands in SQL Server is unable to read or write files due to insufficient permissions.

While the error is straightforward, the “culprit” isn’t.

The often picked, yet incorrect suspect

In most cases of incorrectly identifying the root cause, people tend to blame it on the SQL or Windows login connecting to the instance and/or SSMS not opened as an administrator.

The OS and SQL Login permissions

My Windows user is a member of the local Administrators group on the host OS.
This can be easily checked via PowerShell:

And is a member of the sysadmin fixed server role on the instance.
Which can be checked via T-SQL:

I can literally do anything on this computer.

Ok, so with all the full range of permissions that the combined Administrators group and sysadmin role memberships provide, I still get the error.
Surely I have to open SSMS as Administrator (for some reason).

Running SSMS as Admin

If my permissions aren’t doing the trick, maybe I have to open SSMS as Administrator.

Strange, looks like that didn’t do the trick.
People from the reddit brain trust are welcome to chime in via the comments as to why this didn’t work.

How would this even affect anything?
I’m suspecting that the people suggesting this never had to restore a database in a production environment where they can’t just RDP onto the SQL Server host and use SSMS with admin privileges whenever they feel like it.

Note that there is one case where you might need to run SSMS or sqlcmd as admin, but this isn’t it.

The actual culprit

To understand why the above options will never work, we’ll first have to get a basic understanding of Windows services.

In Windows, a service is a program (process) that runs in the background. If you’re coming from a Unix background, think of it as a daemon.

Any service runs under a specific Windows user called a service account, and each SQL Server instance is a service on the machine that hosts it.
You can see this in places such as Services (service.msc) (1) and, specifically for SQL Server, in SQL Server Configuration Manager (2).

You can also use PowerShell to see under which account the SQL Server (sqlservr, not a typo) process is running.

Here’s a comparison with the two processes that belong to my two SQL Server Management Studio windows.

Notice how the SSMS processes run under my Windows account, while the sqlservr process runs under a user named NT SERVICE\MSSQL$VSQL2019.

Why is this important?

The point I’m trying to drive home here is that services don’t interact with the underlying OS via your own user account.
Unless you’ve made some poor decisions and set your user account as a service account, which you absolutely shouldn’t.

Instead, services interact with the underlying OS (and even network shares) via their service accounts.

Can you prove it?

Sure. In two ways even.

First, using xp_cmdshell, which lets us interact with the instance’s underlying Windows operating system as the SQL Server service itself.
I’ve already enabled it so I’m not posting the whole T-SQL here, but you can find how to enable/disable it in the docs.

And by using sysinternals‘ ProcesMonitor64, to see what SQL Server actually tries to do when the first restore command is executed.

In the above screenshot you can see the user under which the SQL Server process is running, the files it’s trying to read when the restore is attempted, the result of SQL Server’s file access operation and the details.

You can see that all attempts to read the .bak file and reading the attributes of the Download directory result in Access Denied.

Why does it matter?

The main point here is that it doesn’t matter what permissions you have at the OS and SQL Server level, SQL Server itself interacts with the underlying OS, the file system, and any potential network file shares, via its service account.
If that service account does not have the necessary permissions for the file access operations it’s trying to carry out on your behalf, it will always end up getting the same error – operating system error 5(Access is denied.)

The fix

Use the default directories

SQL Server will always have full control over the instance’s default locations.

So, for the above scenarios, the following options would sort out the issue:

1. Moving the backup file in the default backup folder
2. Changing the create database command to either:
   • Point to the default data and log file directories
   • Or just use the simplest version of the create database command (CREATE DATABASE [TestAccessToPath];), without specifying where the files should go and let SQL Server handle that for you.

To identify the instance’s default file locations you can either:

A. Check the Database Settings section of the instance’s properties

B. Use the following T-SQL on version 2019 and above:

To run the above query on SQL Server 2014, 2016 and 2017, you can comment out the line for the DefaultBackupPath, which won’t work on versions older than 2019.

Permissions

The other options, if you insist on using those specific file paths, is to grant the SQL Server service account the require level of access to those files or folders.

For the first example, I run the following command in command prompt opened as admin:

The above command gives the service account, in this case NT SERVICE\MSSQL$VSQL2019, read (hence th R at the end of the command) permissions on the .bak file “C:\Users\Vlad\Downloads\AdventureWorks2019.bak”.

Afterwards, the restore works without any issues:

Oh, and would you look at that. I don’t have SSMS opened as Admin.

For the other two scenarios, the restore and database creation to a different path, I’ll need to give the service account full control to the “C:\Program Files\AdventureWorksDBFiles\” path.

This command gives NT SERVICE\MSSQL$VSQL2019 full permissions (hence the F at the end) over the “C:\Program Files\AdventureWorksDBFiles” folder and any newly create object in that folder will inherit those permissions (OI = Object inherit).

And, just to test the new permissions, I run the database creation script in SSMS that isn’t opened as admin.

The ill-advised overkill (not joking, don’t do this)

One thing I’ve seen people do is just set the service account as LocalSystem.
That’s a very bad and lazy fix that can leave you exposed to other problems, especially in a production environment.
I’ve explained why in my securing SQL Server’s service account post.

Conclusion

One more time, for the people in the back: It’s not the user running SSMS, SQL Server accesses files through the its service account, and running SSMS as admin won’t do anything for that pesky “operating system error 5(Access is denied.)” error message.