I presented for the PASS DBA Virtual Chapter several weeks ago and talked about different ways that Active Directory can affect your SQL Server. We only had an hour so I had to scale the content back and because of that I had a ton of questions. I answered what we had time for on the web cast and now I’ll answer the ones I couldn’t get to in this blog post.
Q. When we get to the Kerberos part, I’d love to find out how to resolve this error: The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x6d3, state: 4. Administrator should deregister this SPN manually to avoid client authentication errors.
A. Every time SQL Server starts up it attempts to register its SPNs and every time it stops SQL Server tries to unregister its SPNs. If the SQL Server service runs under the Local System, Local Service, or Network Service accounts the SPN goes on the computer object in AD and SQL will have the permissions it needs by default to register and unregister its SPNs. The error we see in the question indicates that it cannot unregister the SPN so either SQL Server is using a domain account to run the service or someone tampered with the permissions on the computer object in AD. When using a domain account the SPNs go on that same account and there are several ways to unregister them. If your Active Directory is prior to Windows 2008 then you can use ADSI edit, but if you’re on a newer version then it’s built right into Active Directory Users and Computers. The last option is to use SETSPN.EXE. Here is the syntax to delete an SPN followed by a screen shot of where to find it in ADUC:
SETSPN.EXE -D SQLSvc/myspnfromerror useraccountname_or_computeraccountname
Edit SPN with ADUC
Q. Where can we find the script to check nested group membership?
A. SQL Service Account Recursive Group Membership
Q. Where do I find the inheritance settings?
A. This is referring to checking the security on your database files. Remember my tip to always check down to the file level and not just stop at the folder level. If inheritance gets removed anywhere in the chain there very well may be lingering permissions below. You can right click and select properties on any folder or file on your machine and go to the security tab. Click on the advanced button and then on the edit permissions button. If you look at the screenshot below you’ll see three things that have to do with inheritance. The first is the “apply to” column for each permission on this object. This column tells us if that permission only applies to this particular folder and goes no further, if it applies to this folder and every folder under it, or if it applies to every file in those folders. The second is the first check box at the bottom which says that the permissions of the folder above this one should be inherited and also applied to this folder. The third thing we see is the last check box which says to take the permissions for this folder and apply them to every child folder and object below it.
Inheritable Permissions
Q. Any advice on what is best to choose – multiple HOST (A) records vs multiple CNAME for DNS Alias?
A. My opinion here is that it’s a pick your own poison. They both can have their issues if not maintained properly, but I would use a CNAME record myself. Using an alias makes more logical sense to me and you gain the ability to change the host name without affecting your users or applications. The only down side is forgetting to re-point it if the host changes. Multiple host records, on the other hand, just makes troubleshooting more difficult. Real life experience is when troubleshooting you always want to look and verify forward and reverse lookups and multiple host records make reverse lookups more confusing.
Q. How can you use a GPO to assign a unique account to the SQL Service?
A. There are two ways to do this. I’ll give you the short answers here and write another blog post with the long answers. Once I write it, I’ll put a link here for you. The first answer is to write a script and put the script in a GPO as a startup script. Yes you could use the other script options as well (shutdown, logon, and logoff) but this is something you’ll want to set at startup. The second option is to use Group Policy Preferences and I’ll cover that in the other post.
Q. How does SQL Server determine permissions for an Active Directory user who is in multiple groups? Is there an order it goes in? Does it merge permissions for all the groups you are in when checking permissions?
A. The short answer is yes it merges it and there is no particular order. If you are using NTLM then it all gets returned in the token from the Domain Controller. If you are using Kerberos then it all gets included in the ticket. The domain controller will issue a TGT or Ticket Granting Ticket that contains the account SID and the SID of every group it is a member of recursively. There are some limits here about how many SIDs can be in a TGT and how large in size it can be. We can get really deep here, but I’ll supress my AD side and leave that for another day.
Q. What is the difference between SQL Server running under the LocalService account as compared to a domain account?
A. In order to answer this question we also have to look at the LocalSystem and NetworkService accounts. The bottom line is that using a domain service account gives you control over what it can and cannot access on the server. You don’t get that ability when using the built in accounts and are subject to what they were built to provide. LocalSystem gives full control over the entire server (including all of AD if it’s on a Domain Controller). That means if SQL gets comprised while running under that account it could be exploited to gain access to the entire server and AD DB. The inverse is true if the LocalSystem gets compromised and access is gained to your databases. LocalService and NetworkService don’t have quite the payload as they only have the equivalent privileges of the local Users group. The difference between them is that LocalService can only access network resources that allow anonymous access where the NetworkService account authenticates to network resources using the computer object account.
Q. Can you talk about AD/DNS replication in the context of geographically dispersed clusters?
A. I talked about the impact with local cluster nodes, but I didn’t specifically point out anything regarding a Geo cluster. The same principals apply here, but on a greater scale. It boils down to replication. When you span a large distance that impact becomes much greater. There are a ton of dependencies like network configuration and bandwidth, replication topology, and replication configuration like Urgent Replication. The absolute safest thing to do here is manually set your SPNs. My experience has show that you can let SQL do it if you have urgent replication turned on, great bandwidth, an excellent replication topology, and cluster nodes that are local to each other. I can tell you that Microsoft will suggest doing it manually even with local cluster nodes, but I also work for a company where the previously mentioned things are not a question. However, even in my situation I would set the SPNs manually if it were a Geo cluster instead of local cluster nodes.
Q. Do you need to restart servers to apply a GPO and if so can you tell the GPO to do it?
A. The vast majority of GPO settings do NOT require a restart. The server just needs to refresh the policy. By default the computer will refresh every 90 minutes with a 30 minute random offset, which means it could happen anywhere between 90 and 120 minutes. You can also force the machine to refresh it’s GPO settings by opening a command prompt and running “gpupdate /force”. As with everything there are always exceptions to the rules. Even if you force an update there are some settings that will not take place until the computer is restarted. I have seen some instances where two reboots are required. GPOs have hundreds of possible settings so I won’t pretend to know all the ones that require a reboot, but I can tell you that scripts that run at logon/logoff and startup/shutdown are some of them.
Q. We set up constrained delegation for SQL Server, but would like to also extend that delegation to work with file share access. What changes are necessary to include other services?
A. Constrained delegation can be tricky, but you will have to define every single service that you want to allow. The following screen shot shows where to define that on the user account. Click the add button and add each and every service you want to allow.
Constrained Delegation
Q. Is there any performance degradation by having many DNS aliases for one IP address? I’m currently looking at creating separate aliases for different systems on the same SQL box. This applies to 150 servers, totaling about 500 aliases. Is this bad for DNS?
A. From the perspective of clients resolving the names there would not be any impact. From a support perspective this could be an administrative nightmare if the host name ever changes or gets deleted, because you have to update or delete 500 CNAME records. From a DNS server perspective 500 alias records should not have a huge impact in of itself, but they do take up space and have to be replicated so there is a minor impact.
Q. If a Windows login is a member of 2 (or more) Windows groups and each group has a login with a default DB, which default DB applies?
A. This is an excellent question, because I’ve never been asked that and don’t know the answer. I decided it was time to test this out in my lab. What I found is that which ever group comes first when querying sys.syslogins is the default DB you will get. I can’t script out that system view to see if there is an ordered index on one of the columns to absolutely guarantee those results every time (I suspect CreateDate, look at it and you’ll see why). I ran profiler during a login as well and all I can see is the login event and not the system querying to figure out which DB should be its default. So I can’t guarantee my answer here, but I believe it will come down to the login create date and which ever login was created first will be your winner. If anyone else knows for absolute certain and can prove it one way or another I would love to hear about it.
