How to give MSSQL$SQL2016 permissions to write to a folder

Question

Morning,

I have hit a brick wall and I need pointing in the right direction. What I am trying to do is give my SQL Server instance permission to create the MDB / LDB files in a directory on a new hard drive I have installed on my local machine, without using Everyone.

When I try to create a new database in the folder (through management studio) I get the following error:

===================================

Create failed for Database 'TestDatabase1'.  (Microsoft.SqlServer.Smo)

------------------------------
For help, click: http://go.microsoft.com /fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.16106.4+((SSMS_Rel_16_5).170125-2137)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.ApplyChanges(Control marshallingControl)
   at Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabase.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext, Boolean executeForAlter)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

===================================

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\MSSQL\Data\TestDatabase1.mdf'.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.4224&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

------------------------------
Server Name: my_machine\SQL2016
Error Number: 5123
Severity: 16
State: 1
Line Number: 1


------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

The SQL Server instance is logging on using "NT Service\MSSQL$2016". I have checked the original data folder and MSSQL$2016 has permissions to read and write.

I've had a look at these posts:

https://blog.sqlauthority.com/2018/08/25/sql-server-fix-create-file-encountered-operating-system-error-5-access-is-denied/

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-file-system-permissions-for-database-engine-access?view=sql-server-2017

https://serverfault.com/questions/507496/sql-server-2012-with-account-nt-service-mssqlserver-access-is-denied-in-domain

http://documentation.sqlserverbooster.com/en/1_1_2/SQLServerBoosterDocumentation.html?AddingreadwritepermissionstoNTSe.html

https://dba.stackexchange.com/questions/70069/how-do-i-give-file-system-access-to-sql-servers-sqlserveragent-virtual-account

I have also tried adding the following to the folder by right clicking on the folder->properties->Security, none of which have worked

NT Service\MSSQL$SQL2016
[NT Service\MSSQL$SQL2016]
[NT Service]\MSSQL$SQL2016
[NT Service]\[MSSQL$SQL2016]
MSSQL$SQL2016

When I click on the Check Names it cannot find it.

If it makes any difference this is on a Windows 10 PC on a domain.

I have missed something obvious but I am not sure what it is. If you could point me at a document, it would be greatly appreciated.

Windward

Might be good to try to restart the SQL services after applying the security settings? – Brose

Sorry misread that you could not find the user. Make sure you select the correct Location (local, not AD tree) before performing the search. – Brose

Thank you @Brose . I thought I had tried that, but it would appear not, that seems to have cured it. – Windward

glad it works! Cheers – Brose

Answer 1

At my machine the solution was assigning user:

NT Service\MSSQL$MSSQLSERVER2016 (instead of MSSQL$MSSQLSERVER2016)

in the dialog box where you manage access rights to concrete directory.

Eal

Do not forget the UPPER CASING, otherwise it wont find the user. It happened to me and it took me a while to figure it out. – Messiah

Answer 2

On my side, I followed this documentation https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-file-system-permissions-for-database-engine-access?view=sql-server-ver15

In a nutshell, I had to search for the user NT SERVICE\MSSQLSERVER

Vang

Answer 3

This worked for me:

NT Service\MSSQL$SQL2016

Give full permission

Argufy

Answer 4

I had the same problem. When I tried to add the user to the folder, the list did not contained my user. So I copied the user from sql configuration and then paste it in the security settings for the folder.

Ingressive