ANF supports a handful of different privilege users for SMB volumes, which assign additional rights (privileges) to the specified domain users for particular use cases. We currently have 3 different groups when it comes to privilege users:
- Backup policy users; used for backup/restore operations (covered in this blog)
- Security privilege users; used for installing Microsoft SQL Server on SMB
- Administrator privilege users; administrator access (includes all backup and security privileges)
More information can be found on the official Learn page. This article will focus on the security privilege users.
The following security consideration is listed on the official ‘Install SQL Server with SMB fileshare storage‘ Learn page:
“The account used to install SQL Server should be granted SeSecurityPrivilege privileges on the SMB file server. To grant this privilege, use the Local Security Policy console on the file server to add the SQL Server setup account to the Manage auditing and security log policy. This setting is available in the User Rights Assignments section under Local Policies in the Local Security Policy console.”
Note: installing SQL Server start to finish on an ANF SMB share is out of scope for this article.
We’ll first demonstrate the specific error message that is returned when we try to install SQL Server on a share without SeSecurityPrivilege.
The domain user that we use to run the SQL Server service is ANF\mssqlserver in our example. We’ll start by running whoami and whoami /priv to observe the current privileges.
Observe that SeSecurityPrivilege is currently not listed.
When we get to the Server Configuration section of the SQL Server installation wizard, with the Service Accounts tab selected, we enter ANF\mssqlserver as the Account Name for the SQL Server Database Engine.
At the next step of the wizard, Database Engine Configuration, we select the Data Directories tab, and input our ANF share path (\\ANFSMB…) at Data root directory.
When we now try to go to the next step of the wizard, the following error message is displayed.
As we can see, the error is very descriptive and there’s no doubt on what is causing it. Let’s configure SeSecurityPrivilege and re-run the installation wizard.
Step 1 – add domain user to ANF security privilege users
In the Azure Portal, navigate to the ANF Active Directory connection, select Edit, add the mssqlserver user (excluding domain name) to the Security privilige users field and click OK.
Step 2 – add mssqlserver user to local security policy
Utilizing an account that has administrator access on the local machine, run secpol.msc
Navigate to Local Policies –> User Rights Assignment –> Manage auditing and security log
Add the ANF\mssqlserver user.
Now logout the ANF\mssqlserver RDP session and log back on. We’ll run whoami and whoami /priv once more to observe the current privileges.
Note that SeSecurityPrivilege is now listed, but set to Disabled. This is as expected, the status will only change to Enabled when a user/process is actively utilizing the privileges.
When we now re-run the installation wizard, the validation at the Database Engine Configuration step will succeed and the following information is displayed.
Share permissions are set to everyone by default, click Yes to continue the wizard.
Congratulations, you can now proceed installing SQL Server on your ANF SMB share.