Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 341
lardymonkey Avatar asked Mar 18 '19 12:03

lardymonkey


People also ask

How do I authorize the SQL Server service to write to the destination folder?

Right-click the file system folder, and then select Properties. On the Security tab, select Edit, and then Add. In the Select Users, Computer, Service Account, or Groups dialog box, select Locations, at the top of the location list, select your computer name, and then select OK.

How do I give a SQL Server Agent access to a folder?

Just right-click the <MSSQLPATH>\MSSQL\Binn\ folder where sqlagent.exe resides, then choose > Properties > Security, then check to make sure that 'NT Service\SQLSERVERAGENT' account has execute permission within the folder.

How do I give SQL read write permissions?

Security - Logins, then double click on Login and you'll get Login Properties window. Go to User Mapping Tab and select the database on which you want to give permission. Under 'Database role membership for' section, check 'db_datawriter' checkbox as shown below. Click Ok and you're done.

How do I grant permissions in mssql?

Right-click a stored procedure and select Properties. In the Stored Procedure Properties -stored_procedure_name dialog box, under select a page, select Permissions. Use this page to add users or roles to the stored procedure and specify the permissions those users or roles have.


2 Answers

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.

like image 175
Marcin Avatar answered Oct 18 '22 03:10

Marcin


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

like image 34
Daniel Avatar answered Oct 18 '22 02:10

Daniel