Recently by a customer, we are facing the case to lose all mount points on a secondary node in a HA SQL Server AlwaysOn infrastructure due to a big problem on the Datacenter.
We need to build from scratch all mount points for the Data, Log and Tempdb.
After multiple researches, I didn’t find how to give back correctly the permission for the SQL Server account on these mount points.
The disk environment is very simple:
One drive D: and 3 mount points under the folder D:\SQLData\MSQL11.\MSSQL :

  • Tmpdb
  • Data
  • Log

After attaching the mount points, all permissions are gone.

The first VERY VERY important step is not to go in the security tab after clicking to see the properties but to click on the properties beside the type: Mounted Volume like you can see on this screenshot:

After, go to the security tab and click Edit.

After you add a new User with the following parameters:

  • Location needs to be the Server name and not the Active Directoy
  • The Object Name need to be “nt service\mssql$instance name”

Check the name, click OK and give Full control to the account on the Mount Point.
This method can be scripted in PowerShell but for one instance on one node, I just do it manually.

Et voila, back to the business with our node and ready to have again High Availability in AlwaysOn!

Post Views: 1,171
Thumbnail [60x60]
by
Stéphane Haby