November 10, 2011 at 3:17 pm
I have been tasked with locking our SQL Servers down-- specifically eliminating/minimizing Domain Administrator permissions. The most recent article I have and the one I assume I should take direction from, is "How to make unwanted access to SQL Server 2005 by anoperating system administrator more difficult", at support.microsoft.com/kb/932881. I have a few questions about the process it outlines.
FIRST QUESTION: is this the "last word" on minimizing Domain Admin permissions? If not, should I be looking at something else?
My environment: We're running SQL2005 EE, several of which are clustered (the article does include additional steps to deal with clusters). Server OS is Windows Server 2003 R2 EE. Our server service accounts are all using domain accounts (MYDOMAIN\SQLSERVICEACCOUNT) that are NOT Domain Admin accounts.
I understand that the single most critical step for this entire process is to be absolutely sure you have the SA password-- and have logged in as SA to confirm it. And if you're dealing with a cluster, you also must be sure you add a login for the account the cluster service uses, and to grant sysadmin permission for it.
Regarding "BUILTIN\Administrators": In SSMS it appears under logins (on all but ONE of my SQL boxes; not sure when it was eliminated from there) and has Server Roles of public of course and sysadmin. When I remote to the SQL Server and examine Computer Managment/Local Users and Groups/Groups, I find ADMINISTRATORS. Members of ADMINISTRATORS on this server are:
* Administrator
* MYDOMAIN\Domain Admins
* MYDOMAIN\SQLSERVICEACCOUNT
QUESTION: Based on this (and the rest of the directions in the KB article) -- I am assuming "BUILTIN\Administrators" refers to the local ADMINISTRATORS group. Is that correct?
Step 2 of the KB says "Remove the logon permissions that were granted to the BUILTIN\Administrators group...right-click BUILTIN\Administrators, and then click Delete...".
QUESTION: This approach sort of terrifies me. Couldn't I get the same result by setting Deny and Disabled on the status screen for this login? I did read that if you delete it you can recreate it; I demonstrated this for myself on the single server we have where it didn't exist but cannot confirm that the act of recreating it ALSO reestablishes the logical relationship with the local administrators group.
Step 3 and 4 have you create a login and make it SYSADMIN, for the accounts used for the SQL and SQL Agent service.
QUESTIONS:
* Do I need to do the same for the service account used for Full Text Search?
* Would it work to Create a domain SQL admin group such as "MYDOMAIN\SQLAdmins"-- and to add the accounts used for the SQL Services and any folks that need to be SYSADMIN into that group. Then, add "MYDOMAIN\SQLAdmins" as a LOGIN and make it SYSADMIN. My concern is that there may be a requirement to have the actual account as a login-- and not only via domain group membership; I think I've run into issues with this in the past (though it may have been with SQL2000).
Step 5 says to drop the automatically created accounts using these commands:
DROP LOGIN [<Computer Name>\<SQLServer2005SQLServerUser>$<Computer Name>$MSSQLSERVER]
DROP LOGIN [<Computer Name>\<SQLServer2005AgentUser>$<Computer Name>$MSSQLSERVER]
QUESTIONS:
* Another paranoia question, similar to my question about "Step 2"-- could these just be set to Deny and Disabled?
* Instead of "SQLServer2005SQLServerUser" and "SQLServer2005AgentUser" I see "SQLServer2005MSSQLUser" and "SQLServer2005SQLAgentUser"-- were those "<>" bracketed in the KB article to account for variations in the names?
* Should the "SQLServer2005MSFTEUser" login be dropped too? (maybe not- since where I am seeing it, it's not an SA)
* I notice these accounts are of type "WINDOWS_GROUP"; looking at local groups on the server, I see the members of these groups are "NT AUTHORITY\SYSTEM (S-1-5-18)" and similar;
CLUSTER SPECIAL DIRECTIONS say to ensure the account used for the cluster service is SA on the server.
QUESTION: Could this account simply be another member of the "MYDOMAIN\SQLAdmins" group I proposed above?
November 11, 2011 at 4:25 pm
mstjean (11/10/2011)
FIRST QUESTION: is this the "last word" on minimizing Domain Admin permissions? If not, should I be looking at something else?
My environment: We're running SQL2005 EE, several of which are clustered (the article does include additional steps to deal with clusters). Server OS is Windows Server 2003 R2 EE. Our server service accounts are all using domain accounts (MYDOMAIN\SQLSERVICEACCOUNT) that are NOT Domain Admin accounts.
I understand that the single most critical step for this entire process is to be absolutely sure you have the SA password-- and have logged in as SA to confirm it. And if you're dealing with a cluster, you also must be sure you add a login for the account the cluster service uses, and to grant sysadmin permission for it.
Realize that if you're doing Windows Authentication, having the sa account doesn't gain you anything. Just make sure you have a login that can get back in that has sysadmin rights. The SQL Server DBA group you make mention of a little later is sufficient. Also realize that you can always get back into SQL Server 2005 and above by starting SQL Server manually in single user mode. Read up on this in books online.
Regarding "BUILTIN\Administrators": In SSMS it appears under logins (on all but ONE of my SQL boxes; not sure when it was eliminated from there) and has Server Roles of public of course and sysadmin. When I remote to the SQL Server and examine Computer Managment/Local Users and Groups/Groups, I find ADMINISTRATORS. Members of ADMINISTRATORS on this server are:
* Administrator
* MYDOMAIN\Domain Admins
* MYDOMAIN\SQLSERVICEACCOUNT
QUESTION: Based on this (and the rest of the directions in the KB article) -- I am assuming "BUILTIN\Administrators" refers to the local ADMINISTRATORS group. Is that correct?
BUILTIN\Administrators corresponds to the local Administrators group on the server, yes.
Step 2 of the KB says "Remove the logon permissions that were granted to the BUILTIN\Administrators group...right-click BUILTIN\Administrators, and then click Delete...".
QUESTION: This approach sort of terrifies me. Couldn't I get the same result by setting Deny and Disabled on the status screen for this login? I did read that if you delete it you can recreate it; I demonstrated this for myself on the single server we have where it didn't exist but cannot confirm that the act of recreating it ALSO reestablishes the logical relationship with the local administrators group.
Re-creating it establishes the relationship. The relationship is mapped by the SID and that doesn't change. This isn't the same as a SQL Server based login where the SID is new every time unless you manually specify it. SQL Server, in the case of Windows users and groups, takes the SID from the OS. That's why it'll always be the same.
Step 3 and 4 have you create a login and make it SYSADMIN, for the accounts used for the SQL and SQL Agent service.
QUESTIONS:
* Do I need to do the same for the service account used for Full Text Search?
* Would it work to Create a domain SQL admin group such as "MYDOMAIN\SQLAdmins"-- and to add the accounts used for the SQL Services and any folks that need to be SYSADMIN into that group. Then, add "MYDOMAIN\SQLAdmins" as a LOGIN and make it SYSADMIN. My concern is that there may be a requirement to have the actual account as a login-- and not only via domain group membership; I think I've run into issues with this in the past (though it may have been with SQL2000).
Step 5 says to drop the automatically created accounts using these commands:
DROP LOGIN [<Computer Name>\<SQLServer2005SQLServerUser>$<Computer Name>$MSSQLSERVER]
DROP LOGIN [<Computer Name>\<SQLServer2005AgentUser>$<Computer Name>$MSSQLSERVER]
QUESTIONS:
* Another paranoia question, similar to my question about "Step 2"-- could these just be set to Deny and Disabled?
* Instead of "SQLServer2005SQLServerUser" and "SQLServer2005AgentUser" I see "SQLServer2005MSSQLUser" and "SQLServer2005SQLAgentUser"-- were those "<>" bracketed in the KB article to account for variations in the names?
* Should the "SQLServer2005MSFTEUser" login be dropped too? (maybe not- since where I am seeing it, it's not an SA)
* I notice these accounts are of type "WINDOWS_GROUP"; looking at local groups on the server, I see the members of these groups are "NT AUTHORITY\SYSTEM (S-1-5-18)" and similar;
Don't drop the special groups and yes, there are some variations on the names, especially for Named Instances. NT AUTHORTY\SYSTEM should have access as well.[/quote]
The problem with creating special groups that SQL Server doesn't manage is if you ever change the service accounts. They you would be broke. SQL Server wouldn't know to update them.
As for Full Text, yes, because of the nature of what it does, it needs sysadmin rights.
CLUSTER SPECIAL DIRECTIONS say to ensure the account used for the cluster service is SA on the server.
QUESTION: Could this account simply be another member of the "MYDOMAIN\SQLAdmins" group I proposed above?
Actually, unless you really need that special diagnostic information, simply running with an account that has login rights is sufficient. If you read the text carefully, the KB says that if your policies are such that the cluster account can't have sysadmin rights, it's still ok. You just will have an error registered when it tries to collect them. Make sure you have this account with the proper permissions *BEFORE* removing anything else.
K. Brian Kelley
@kbriankelley
November 15, 2011 at 7:23 pm
First-- thanks for replying. I appreciate your time. I'd like to clarify one thing from your response:
You said...
...Just make sure you have a login that can get back in that has sysadmin rights. The SQL Server DBA group you make mention of a little later is sufficient...
My question you referenced...
...Step 3 and 4...QUESTIONS...Would it work to Create a domain SQL admin group such as "MYDOMAIN\SQLAdmins"-- and to add the accounts used for the SQL Services and any folks that need to be SYSADMIN into that group. Then, add "MYDOMAIN\SQLAdmins" as a LOGIN and make it SYSADMIN. My concern is that there may be a requirement to have the actual account as a login...
You followed up with...
...The problem with creating special groups that SQL Server doesn't manage is if you ever change the service accounts. They you would be broke. SQL Server wouldn't know to update them...
I hoped I could create a domain group and put the dba's and the required sql service accounts into it-- and grant sysadmin perms to that, but I think I've run into cases of unexpected results when a domain group was granted perms-- as opposed to an individual user-- where the user did not have full functionality. (This could have been back with SQL2000 or even 7)
I think you're saying this will work, but to be aware of the potential downside. Or am I opening the proverbial can of worms?
November 15, 2011 at 7:43 pm
I would keep the service account(s) and the DBAs separate. SQL Server, if you use SQL Server Configuration Manager to manage the service accounts (the only supported way, BTW), will set the proper permissions for the service accounts. These will be through local groups if you are talking a non-clustered instance or domain groups if you're talking a clustered instance. It's going to do this regardless of what you do.
Also, if you assign the service account membership in the same group as the DBAs, it will have permissions wherever the DBAs have permissions. This includes file shares, administrative rights over individual workstations, etc. This fails the principle of least privilege.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply