question

DJAdan-4490 avatar image
0 Votes"
DJAdan-4490 asked JeffreyWilliams-3310 commented

ALTER DATABASE <dbname> MODIFY FILE ( name = 'xxx', file_name = 'K:\NEWDIR\xxx_db.mdf') -- Permissions Problem in a Failover Cluster

Hi All,

Current environment: Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Feb 9 2021 12:08:50 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

I am currently running SQL Server in a Failover Cluster, and we are in the process of migrating our data storage to a new array. I have to modify file_name from old location (J:\ drive) to the new location (K:\ drive).

The steps I am using are as follows:

  1. ALTER DATABASE xxx SET OFFLINE;

  2. Go to OS and FILE COPY the .mdf to the new location on the K:\NEWDIR\xxx_db.mdf

  3. Go to OS and FILE COPY the .ldf to the new location on the L:\NEWDIR]xxx_log.ldf

  4. ALTER DATABASE xxx MODIFY FILE (name ='xxx_db', filename = 'K:\NEWDIR\xxx_db.mdf' );

  5. ALTER DATABASE xxx MODIFY FILE (name ='xxx_log', filename = 'L:\NEWDIR\xxx_log.ldf' );

  6. ALTER DATABASE xxx SET ONLINE;

The ALTER DATABASE xxx SET ONLINE; caused the error:

Msg 5120, Level 16, State 101, Line 10
Unable to open the physical file "K:\NEWDIR\xxx_db.mdf". Operating system error 5: "5(Access is denied.)".

So I resolved the error, going into file permissions and granting FULL CONTROL to the SQL Server Service Account. No problem.

My issue? I then compared the permission settings between the OLD .mdf and the NEW .mdf and I saw a difference that I can't figure out how to migrate.

The old .mdf has FULL CONTROL granted to "MSSQL$<instance_name>". I can't figure out how to migrate that permission to the new .mdf. I am unable to match the permissions "MSSQL$<instance_name>" that the old .mdf (and .ldf) had.

As a final test, I created a new database using SSMS in the new location, and by default, the permission mask was set to "MSSQL$<instance_name>" with FULL CONTROL.

Questions:

  1. How does SSMS assign the permissions to a newly created database?

  2. How do I replicate these permissions to a database that I have moved to a new location using the documented migration procedure?

  3. What am I missing here?

Thank you for your help.

--Dan



sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DJAdan-4490 avatar image
0 Votes"
DJAdan-4490 answered DJAdan-4490 commented

Hi Cathy,

Your recommendation is exactly why I am confused. I am using a DOMAIN\ACCOUNT as my SQL Server account, as you can see in the screen shot below. As you can see, I am NOT using a virtual service account.



100565-sql-server-domain-account.png


I'll give ROBOCOPY a try and see if I can preserve permissions that way.

Thanks!

--Dan



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Only the service account requires access to the files, whatever that is.

If you installed the server first using the virtual account, then the files got those rights. Then when you changed it to the domain account it got assigned rights to the domain account.

0 Votes 0 ·

Hi Tom,

That makes sense. It is very possible (likely) that the original SQL Server installation used Virtual Service accounts, and subsequently was changed to use a DOMAIN\Account.

This would explain why the old permissions were still present.

What I don't understand, is why when I created a new TestDB, it took on the old service account permissions mask.

But thanks again. I'm good to go ...

--Dan

0 Votes 0 ·
JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered JeffreyWilliams-3310 commented

You can add that account manually in Windows - the issue is finding the account, but that isn't too difficult. Right-click on the folder or file and go to Properties | Security - and click on Edit and Add. In the dialog that opens - select Location and change to the server location.

Then - type in the object name box: NT SERVICE\M and check names.

This will bring up a list of all of the NT SERVICE accounts that start with the letter M. Pick the correct one for your instance of SQL Server. For SQL Server Agent - type in NT SERVICE\S to list all account that start with S and find the one for SQL Server Agent for that instance.

You can also go to the Advanced dialog and perform the lookup the same way. Use the Advanced section to enable/disable inheritance if setting the permissions on a folder. From Advanced you can propagate the permissions to child objects based on the parent object if needed.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This will bring up a list of all of the NT SERVICE accounts that start with the letter M. Pick the correct one for your instance of SQL Server. For SQL Server Agent - type in NT SERVICE\S to list all account that start with S and find the one for SQL Server Agent for that instance.

You have to go one step deeper to change the scope, as by default the search may be for the domain.

When I think of it, I think this is a group. No permissions are assigned to the service account, only membership in that group. I know I've seen Tibor discuss this. (And he knows this better than I do.)

0 Votes 0 ·

I included the step - you change the location in the dialog to the local computer instead of the domain. The NT SERVICE accounts are not groups - but they may be added to groups for specific permissions. For example, added to Performance Log Users and Performance Monitor Users so SQL can access perfmon stats. They can also be added to local policies - for example to the policy 'lock pages in memory' or 'perform volume maintenance tasks' (instant initialization).

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @SahaSaha-5270,

But where does the name MSSQL$<instance_name> come from

This is virtual account and it is auto-managed. NT SERVICE\MSSQLSERVER is for the default SQL server instance, NT SERVICE\MSSQL$instancename is for named SQL instance. please note Virtual accounts can't be used for SQL Server Failover Cluster Instance, because the virtual account would not have the same SID on each node of the cluster. Refer to MS document Configure Windows Service Accounts and Permissions.

Suggest you using a domain \admin account to run SQL server service in SQL cluster environment.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

But where does the name MSSQL$<instance_name> come from, and how do I replicate the ACL?

That is the service account for SQL Server. On my machine the full name is NT Service\MSSQL$INSTANCE, and hat is a Managed Service Account which is local to my machine. Since this is a cluster, I would not really expect local accounts to be used, since there are more than one machine. But maybe it is a GMSA, Group Managed Service Account, which is the same thing but on domain level.

I would go for ROBOOPY, but as I said that's a Windows question, so my answer is a layman's answer.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DJAdan-4490 avatar image
0 Votes"
DJAdan-4490 answered

Thanks Erland,

Fair enough -- SQL Server assigns the permission, SSMS is just a tool. I get it! But where does the name MSSQL$<instance_name> come from, and how do I replicate the ACL?

Is ROBOCOPY the recommended method for ensuring the correct ACL is assigned?

Thanks again!

--Dan

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

How does SSMS assign the permissions to a newly created database?


SSMS does not assign any permissions at all: SQL Server does. And since SQL Server performs the action, well, it becomes the owner and gets full control and that.

As for how to copy files and retaining permissions exactly, that is more of a Windows question. but the ROBOCOPY has a couple of switches related to ACLs.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.