October 2, 2018 at 8:42 am
Recently, I updated the user SQL Server runs under from NT SERVICE\MSSQLSERVER to a domain account and restarted the SQL Server service--all through SQL Server Configuration Manager. I also went to the file system and essentially replaced MSSQLSERVER with the domain account and gave it the exact same permissions to the following directories (where applicable):
Instid\MSSQL\backup
Instid\MSSQL\binn
Instid\MSSQL\data
Instid\MSSQL\FTData
Instid\MSSQL\Install
Instid\MSSQL\Log
Instid\MSSQL\Repldata
Then I created a new database on the server and looked at the permissions of the mdf and ldf files it created in DATA. They have:
"OWNER RIGHTS"
"Administrators (Server\Administrators)"
"MSSQLSERVER"
I'm wondering where MSSQLSERVER is coming from. SQL Server isn't running as that user anymore and it's not like the permissions are being inherited from its parent (DATA) because that has no trace of "MSSQLSERVER".
I'm sure I'm missing something...can someone help clarify?
Thank you,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
October 2, 2018 at 11:30 am
Mike Scalise - Tuesday, October 2, 2018 8:42 AMRecently, I updated the user SQL Server runs under from NT SERVICE\MSSQLSERVER to a domain account and restarted the SQL Server service--all through SQL Server Configuration Manager. I also went to the file system and essentially replaced MSSQLSERVER with the domain account and gave it the exact same permissions to the following directories (where applicable):Instid\MSSQL\backup
Instid\MSSQL\binn
Instid\MSSQL\data
Instid\MSSQL\FTData
Instid\MSSQL\Install
Instid\MSSQL\Log
Instid\MSSQL\RepldataThen I created a new database on the server and looked at the permissions of the mdf and ldf files it created in DATA. They have:
"OWNER RIGHTS"
"Administrators (Server\Administrators)"
"MSSQLSERVER"I'm wondering where MSSQLSERVER is coming from. SQL Server isn't running as that user anymore and it's not like the permissions are being inherited from its parent (DATA) because that has no trace of "MSSQLSERVER".
I'm sure I'm missing something...can someone help clarify?
Thank you,
Mike
The permissions would be to MSSQLSERVER as it is granted to the per-service SID. Both accounts come into play. A group used to be used in SQL Server 2008 but that changed . But MSSQLSERVER is generally what is used for permissions local to the server.
Configure Windows Service Accounts and Permissions
Try reading this and see if it makes more sense:
SQL Server Service Account and Per-Service SID
Sue
October 2, 2018 at 11:47 am
Sue_H - Tuesday, October 2, 2018 11:30 AMMike Scalise - Tuesday, October 2, 2018 8:42 AMRecently, I updated the user SQL Server runs under from NT SERVICE\MSSQLSERVER to a domain account and restarted the SQL Server service--all through SQL Server Configuration Manager. I also went to the file system and essentially replaced MSSQLSERVER with the domain account and gave it the exact same permissions to the following directories (where applicable):Instid\MSSQL\backup
Instid\MSSQL\binn
Instid\MSSQL\data
Instid\MSSQL\FTData
Instid\MSSQL\Install
Instid\MSSQL\Log
Instid\MSSQL\RepldataThen I created a new database on the server and looked at the permissions of the mdf and ldf files it created in DATA. They have:
"OWNER RIGHTS"
"Administrators (Server\Administrators)"
"MSSQLSERVER"I'm wondering where MSSQLSERVER is coming from. SQL Server isn't running as that user anymore and it's not like the permissions are being inherited from its parent (DATA) because that has no trace of "MSSQLSERVER".
I'm sure I'm missing something...can someone help clarify?
Thank you,
Mike
The permissions would be to MSSQLSERVER as it is granted to the per-service SID. Both accounts come into play. A group used to be used in SQL Server 2008 but that changed . But MSSQLSERVER is generally what is used for permissions local to the server.
Configure Windows Service Accounts and PermissionsTry reading this and see if it makes more sense:
SQL Server Service Account and Per-Service SIDSue
Hi Sue,
Thanks for the response. I had read the Microsoft link you had posted on a different thread of mine. It was actually incredibly helpful. However, even after looking at it a second time and reading the Travis's page, it's still not clear to me how both come into play in this particular case. None of my SQL-related services are running under "MSSQLSERVER".
On a related note, you stated that MSSQLSERVER is generally what is used for permissions local to the server. Is it not a good idea to use a domain account to run SQL Server where possible?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
October 2, 2018 at 12:07 pm
Mike Scalise - Tuesday, October 2, 2018 11:47 AMSue_H - Tuesday, October 2, 2018 11:30 AMMike Scalise - Tuesday, October 2, 2018 8:42 AMRecently, I updated the user SQL Server runs under from NT SERVICE\MSSQLSERVER to a domain account and restarted the SQL Server service--all through SQL Server Configuration Manager. I also went to the file system and essentially replaced MSSQLSERVER with the domain account and gave it the exact same permissions to the following directories (where applicable):Instid\MSSQL\backup
Instid\MSSQL\binn
Instid\MSSQL\data
Instid\MSSQL\FTData
Instid\MSSQL\Install
Instid\MSSQL\Log
Instid\MSSQL\RepldataThen I created a new database on the server and looked at the permissions of the mdf and ldf files it created in DATA. They have:
"OWNER RIGHTS"
"Administrators (Server\Administrators)"
"MSSQLSERVER"I'm wondering where MSSQLSERVER is coming from. SQL Server isn't running as that user anymore and it's not like the permissions are being inherited from its parent (DATA) because that has no trace of "MSSQLSERVER".
I'm sure I'm missing something...can someone help clarify?
Thank you,
Mike
The permissions would be to MSSQLSERVER as it is granted to the per-service SID. Both accounts come into play. A group used to be used in SQL Server 2008 but that changed . But MSSQLSERVER is generally what is used for permissions local to the server.
Configure Windows Service Accounts and PermissionsTry reading this and see if it makes more sense:
SQL Server Service Account and Per-Service SIDSue
Hi Sue,
Thanks for the response. I had read the Microsoft link you had posted on a different thread of mine. It was actually incredibly helpful. However, even after looking at it a second time and reading the Travis's page, it's still not clear to me how both come into play in this particular case. None of my SQL-related services are running under "MSSQLSERVER".
On a related note, you stated that MSSQLSERVER is generally what is used for permissions local to the server. Is it not a good idea to use a domain account to run SQL Server where possible?
Thanks,
Mike
I thought I had answered that for you before but thought maybe I hadn't had enough coffee.
You have things using MSSQLSERVER whether you realize it or not. Check the sysadmins role on your server. Not like that explains much but you have to read that second link a few times.I would guess most people really don't know much about a lot of this and just assume the permissions are granted to the domain account which isn't the case. It's using the the service sid just as it used to use the groups with SQL Server 2008 and earlier. I can't remember the group names right now but it's essentially for the same thing - just managing some of the permissions so you don't have to redo them all of the time and have the local "core" permissions granted to the service SID.
The problem when the groups were used is that people would add other accounts to the groups so they would have the same permissions as SQL Server. And those other accounts didn't need that much. So MS went down this route to isolate the services more.
It's a bit convoluted in terms of how it all works and it has changed a bit on each version of SQL Server. This is an older explanation but much of it still applies:
Service/Database Accounts - NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT … what are they for
So basically both the domain account and MSSQLSERVER will have permissions. On SQL Server 2008 and earlier, the group and the domain account had the permissions. When you changed the service account, it would just change the membership in that group. Now MSSQLSERVER is for the service so in some cases the permissions are granted to the service which applies to the domain account running the service.
Sue
October 2, 2018 at 1:59 pm
Sue_H - Tuesday, October 2, 2018 12:07 PMMike Scalise - Tuesday, October 2, 2018 11:47 AMSue_H - Tuesday, October 2, 2018 11:30 AMMike Scalise - Tuesday, October 2, 2018 8:42 AMRecently, I updated the user SQL Server runs under from NT SERVICE\MSSQLSERVER to a domain account and restarted the SQL Server service--all through SQL Server Configuration Manager. I also went to the file system and essentially replaced MSSQLSERVER with the domain account and gave it the exact same permissions to the following directories (where applicable):Instid\MSSQL\backup
Instid\MSSQL\binn
Instid\MSSQL\data
Instid\MSSQL\FTData
Instid\MSSQL\Install
Instid\MSSQL\Log
Instid\MSSQL\RepldataThen I created a new database on the server and looked at the permissions of the mdf and ldf files it created in DATA. They have:
"OWNER RIGHTS"
"Administrators (Server\Administrators)"
"MSSQLSERVER"I'm wondering where MSSQLSERVER is coming from. SQL Server isn't running as that user anymore and it's not like the permissions are being inherited from its parent (DATA) because that has no trace of "MSSQLSERVER".
I'm sure I'm missing something...can someone help clarify?
Thank you,
Mike
The permissions would be to MSSQLSERVER as it is granted to the per-service SID. Both accounts come into play. A group used to be used in SQL Server 2008 but that changed . But MSSQLSERVER is generally what is used for permissions local to the server.
Configure Windows Service Accounts and PermissionsTry reading this and see if it makes more sense:
SQL Server Service Account and Per-Service SIDSue
Hi Sue,
Thanks for the response. I had read the Microsoft link you had posted on a different thread of mine. It was actually incredibly helpful. However, even after looking at it a second time and reading the Travis's page, it's still not clear to me how both come into play in this particular case. None of my SQL-related services are running under "MSSQLSERVER".
On a related note, you stated that MSSQLSERVER is generally what is used for permissions local to the server. Is it not a good idea to use a domain account to run SQL Server where possible?
Thanks,
Mike
I thought I had answered that for you before but thought maybe I hadn't had enough coffee.
You have things using MSSQLSERVER whether you realize it or not. Check the sysadmins role on your server. Not like that explains much but you have to read that second link a few times.I would guess most people really don't know much about a lot of this and just assume the permissions are granted to the domain account which isn't the case. It's using the the service sid just as it used to use the groups with SQL Server 2008 and earlier. I can't remember the group names right now but it's essentially for the same thing - just managing some of the permissions so you don't have to redo them all of the time and have the local "core" permissions granted to the service SID.
The problem when the groups were used is that people would add other accounts to the groups so they would have the same permissions as SQL Server. And those other accounts didn't need that much. So MS went down this route to isolate the services more.
It's a bit convoluted in terms of how it all works and it has changed a bit on each version of SQL Server. This is an older explanation but much of it still applies:
Service/Database Accounts - NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT … what are they forSo basically both the domain account and MSSQLSERVER will have permissions. On SQL Server 2008 and earlier, the group and the domain account had the permissions. When you changed the service account, it would just change the membership in that group. Now MSSQLSERVER is for the service so in some cases the permissions are granted to the service which applies to the domain account running the service.
Sue
Ok that actually helps clear it up a bit. Let me ask this, do you think I've "hurt" anything by removing MSSQLSERVER from the following directories' permissions and replaced it with the domain user? Or should both be in the permission set?
Instid\MSSQL\backup
Instid\MSSQL\binn
Instid\MSSQL\data
Instid\MSSQL\FTData
Instid\MSSQL\Install
Instid\MSSQL\Log
Instid\MSSQL\Repldata
I haven't noticed any negative consequences yet...and maybe if MSSQLSERVER is used regardless (as in your statement, "You have things using MSSQLSERVER whether you realize it or not") then maybe I haven't hurt anything by removing the user from the directories?
Thanks again. I really appreciate it.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
October 2, 2018 at 3:00 pm
Mike Scalise - Tuesday, October 2, 2018 1:59 PMSue_H - Tuesday, October 2, 2018 12:07 PMI thought I had answered that for you before but thought maybe I hadn't had enough coffee.
You have things using MSSQLSERVER whether you realize it or not. Check the sysadmins role on your server. Not like that explains much but you have to read that second link a few times.I would guess most people really don't know much about a lot of this and just assume the permissions are granted to the domain account which isn't the case. It's using the the service sid just as it used to use the groups with SQL Server 2008 and earlier. I can't remember the group names right now but it's essentially for the same thing - just managing some of the permissions so you don't have to redo them all of the time and have the local "core" permissions granted to the service SID.
The problem when the groups were used is that people would add other accounts to the groups so they would have the same permissions as SQL Server. And those other accounts didn't need that much. So MS went down this route to isolate the services more.
It's a bit convoluted in terms of how it all works and it has changed a bit on each version of SQL Server. This is an older explanation but much of it still applies:
Service/Database Accounts - NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT … what are they forSo basically both the domain account and MSSQLSERVER will have permissions. On SQL Server 2008 and earlier, the group and the domain account had the permissions. When you changed the service account, it would just change the membership in that group. Now MSSQLSERVER is for the service so in some cases the permissions are granted to the service which applies to the domain account running the service.
Sue
Ok that actually helps clear it up a bit. Let me ask this, do you think I've "hurt" anything by removing MSSQLSERVER from the following directories' permissions and replaced it with the domain user? Or should both be in the permission set?
Instid\MSSQL\backup
Instid\MSSQL\binn
Instid\MSSQL\data
Instid\MSSQL\FTData
Instid\MSSQL\Install
Instid\MSSQL\Log
Instid\MSSQL\RepldataI haven't noticed any negative consequences yet...and maybe if MSSQLSERVER is used regardless (as in your statement, "You have things using MSSQLSERVER whether you realize it or not") then maybe I haven't hurt anything by removing the user from the directories?
Thanks again. I really appreciate it.
Mike
It should be just the permissions to mssqlserver for those folders. It's been awhile since I tested it so I can't remember what happens if you then change the service account again. I won't have time to test it today but seems to be the likely time you would hit issues. Right now you have it granted to the specific account so you hopefully won't see issues right now. But on one of those sets of permissions I changed with service accounts when I was trying to figure out the different permissions, it added the mssqlserver back and then didn't delete the old account. I just can't remember where I hit that. So it left behind unnecessary permissions which you can get hit with on an audit.
But it is expecting the permissions to be granted to mssqlserver so changing it from that is likely not a good thing. Whatever you have for the service account is associated with the NT Service\Msssqlserver account.
If you wanted to go back and add the permissions back, you would need to use "NT SERVICE\MSSQLSERVER" (no quotes though) instead of just mssqlserver for the service account sid to be found. I would put it back to how it was originally.
Sue
October 4, 2018 at 6:57 am
Sue_H - Tuesday, October 2, 2018 3:00 PMMike Scalise - Tuesday, October 2, 2018 1:59 PMSue_H - Tuesday, October 2, 2018 12:07 PMI thought I had answered that for you before but thought maybe I hadn't had enough coffee.
You have things using MSSQLSERVER whether you realize it or not. Check the sysadmins role on your server. Not like that explains much but you have to read that second link a few times.I would guess most people really don't know much about a lot of this and just assume the permissions are granted to the domain account which isn't the case. It's using the the service sid just as it used to use the groups with SQL Server 2008 and earlier. I can't remember the group names right now but it's essentially for the same thing - just managing some of the permissions so you don't have to redo them all of the time and have the local "core" permissions granted to the service SID.
The problem when the groups were used is that people would add other accounts to the groups so they would have the same permissions as SQL Server. And those other accounts didn't need that much. So MS went down this route to isolate the services more.
It's a bit convoluted in terms of how it all works and it has changed a bit on each version of SQL Server. This is an older explanation but much of it still applies:
Service/Database Accounts - NT SERVICE\MSSQLSERVER & NT SERVICE\SQLSERVERAGENT … what are they forSo basically both the domain account and MSSQLSERVER will have permissions. On SQL Server 2008 and earlier, the group and the domain account had the permissions. When you changed the service account, it would just change the membership in that group. Now MSSQLSERVER is for the service so in some cases the permissions are granted to the service which applies to the domain account running the service.
Sue
Ok that actually helps clear it up a bit. Let me ask this, do you think I've "hurt" anything by removing MSSQLSERVER from the following directories' permissions and replaced it with the domain user? Or should both be in the permission set?
Instid\MSSQL\backup
Instid\MSSQL\binn
Instid\MSSQL\data
Instid\MSSQL\FTData
Instid\MSSQL\Install
Instid\MSSQL\Log
Instid\MSSQL\RepldataI haven't noticed any negative consequences yet...and maybe if MSSQLSERVER is used regardless (as in your statement, "You have things using MSSQLSERVER whether you realize it or not") then maybe I haven't hurt anything by removing the user from the directories?
Thanks again. I really appreciate it.
Mike
It should be just the permissions to mssqlserver for those folders. It's been awhile since I tested it so I can't remember what happens if you then change the service account again. I won't have time to test it today but seems to be the likely time you would hit issues. Right now you have it granted to the specific account so you hopefully won't see issues right now. But on one of those sets of permissions I changed with service accounts when I was trying to figure out the different permissions, it added the mssqlserver back and then didn't delete the old account. I just can't remember where I hit that. So it left behind unnecessary permissions which you can get hit with on an audit.
But it is expecting the permissions to be granted to mssqlserver so changing it from that is likely not a good thing. Whatever you have for the service account is associated with the NT Service\Msssqlserver account.
If you wanted to go back and add the permissions back, you would need to use "NT SERVICE\MSSQLSERVER" (no quotes though) instead of just mssqlserver for the service account sid to be found. I would put it back to how it was originally.Sue
Sue,
Thank you. I'll add back in NT SERVICE\MSSQLSERVER just to ensure there won't be any issues moving forward.
Again, I appreciate the responses!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply