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.
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.
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With