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

Question

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

Answer 1

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.

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

Thanks!

--Dan

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

Hi @Avyayah ,

> 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.