Ryan Adams Blog

SQL, Active Directory, Scripting

A fellow community member sent me an email saying he was having trouble authenticating with Kerberos.  Here is the error he was receiving:

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: <named pipe>].

You’ll notice that he was using Named Pipes and a lot of folks might think that is the reason for the error and that SQL Server doesn’t support Kerberos over Named Pipes.  However, that’s not the case as it is version dependent.  Kerberos over Named Pipes started being supported in SQL Server 2008 and this person was running SQL Server 2008 R2.  In this instance, that was not the issue.

I walked him through some troubleshooting scenarios to narrow down the cause.  I had him verify his SPNs to make sure they were correct, were located on the correct object in Active Directory, and he had SPNs for both the FQDN and NetBIOS names.  That all checked out so I had him verify that port 88 (Kerberos) was open between the client and server.  That also checked out fine.  I asked him this one last thing:

Also, make sure you are using Windows authentication since Kerberos does not work with SQL authentication.  If you are using Windows authentication then make sure the client and server are in the same domain or a trusted domain.

That last sentence was the ticket.  He replied that his client and server were in different domains that were NOT trusted.  Unfortunately for him, there is nothing he can do short of moving the servers into the same domain or setting up a trust between them.  That’s a core concept for how Kerberos works.  Clients and servers MUST be in the same domain or a trusted domain.

Have you ever had an error when using a SQL Server Proxy Account?  I ran across a misleading error.  Let me qualify that a little.  It wasn’t misleading in the sense of it saying one thing and meaning another.  It was misleading because it’s one of those errors you see for multiple things and I happen to see this one fairly often.  Let’s look at the error first…

Unable to start execution of step1 (reason: Error authenticating proxy SERVER1\Administrator. System error: Logon failure: unknown user name or bad password.).  The step failed.

You can see from the error that it came from a SQLAgent job.  You can also see that the job step was using a proxy account.  The last part about “unknown user name or bad password” is the part I see most often in errors.  Before you read on to the next paragraph, what’s the first thing that comes to mind as to the cause of this error?  No seriously…stop reading…what’s your first instinct?

Did you guess that it was a bad password?  You didn’t stop reading and think did you?  That would be way too obvious and easy!  My guess?  A Kerberos double hop issue.  That did not turn out to be the case.  It’s not the most common error message for a Kerb issue as you’re more likely to see an “Anonymous User” or “SSPI Context” error, but it’s not uncommon.  So what’s the issue?

The issue turned out to be someone configuring the SQLAgent service account to use a UPN or User Principal Name.  As a DBA you may be wondering what that is.  My former life was Active Directory and the simple explanation is that it’s an account name in the format “User@Domain.com”.  All you really need to know is that SQL Server does NOT support the use of UPN names.

I want to point out two things here.  The first is the user name you see in the error message.  “Server1\Administrator” is called a SAM account name.  If it had been in a UPN format the issue would have been very obvious.  However, that account is the credential that the proxy account is using.  In fact the job should have executed under the security context of that account as opposed to the account the SQLAgent service account was running under.  Since the SQLAgent was set to run using a UPN name we failed before we ever got that far.  So  how did I figure out the issue?  You’ll recall that I said I thought it was a Kerberos error, so I went to look at the SQL Server Service account so I could check the SPNs and that’s when I saw the bad account name.

The second thing I want to point out is that SQL Server Configuration Manager will NOT allow you to use a UPN.  That means the account was mostly likely set using Services.msc.  This is just another example of why you should ALWAYS use SQL Server Configuration Manager to make account changes to your SQL services.

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

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

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

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.

It was recently brought to my attention that a post with the script I talk about below could not be found on my blog.  Either I thought I blogged about it and never did or it got lost when I had to change hosting providers late last year.  My apologies for those who have been looking for it, but let’s rectify that and get down to it.

In my presentation on “How Active Directory Affects SQL Server” I talk about different ways people can gain access to your server.  The most common first step is to look at the logins in SQL to verify if someone has access.  That’s a great first step and the good news is that these days people have learned to take it a step further and check the groups that have logins as well.  It seems like that should do it, but it’s not enough.

Active Directory allows us to nest groups.  That means a group can be a member of another group.  There are three types of groups in AD and there are rules around which types can be nested in other types.  I’ll save that discussion for another day, but if you’re curious I have a diagram in my slide deck from the above presentation.  In order to fully verify access to your server, you have to traverse every single group up to the top of the nesting chain.  In a large environment it’s time consuming and easy to overlook an account when going back and forth.

The following script will look at the service accounts of all SQL instances on your machine and verify they do not have local administrative access.  I’ll put a note here that this default installation behavior was removed in SQL 2012, but that doesn’t mean someone didn’t grant the permission after the fact.  I realize this is written in VBScript and I’ll work on converting it to PowerShell, but keep in mind I wrote this in 2007.

Let me know if you have any issues or need a change in functionality and I’ll see what I can do.  As always, the use of this script is at your own risk and should be tested in a development environment.  I assume no responsibility for your use of the code.  You’ll need to rename the extension to .vbs.


Welcome back folks for day 2 of the PASS Summit in Charlotte, NC.  We are 6 or 7 minutes away from getting under way.   I’ll be live blogging this morning’s keynote so refresh your page often.  I’ll be taking short notes to keep you up to date as opposed to paragraph form.

We are off and running with another packed room.  We are seeing a video highlighting attendees and the things they have learned and the things they are most looking forward to.  Douglas McDowell, EVP of Finance, hits the stage and is going to give a financial snapshot of the PASS organization.  Doug reminds us that PASS is a not for profit organization and the Summit allows the organization to help fund all the other things it does for the community like local and virtual chapters.  Doug highlights the various income streams like Summit, Virtual Chapters, and the BAC conference.  He mentions that the BAC conference made about 100k in funds.

Doug is highlighting some of the transformations in how budgeting is done.  The biggest change is having a rainy day or emergency fund and we now have 1M in reserve.  This is a great insurance plan for the organization to make sure it can stay healthy in case of cancelled conferences due to natural disaster or bad turn in economic conditions.

Doug is now showing the different places where PASS is placing its funds.  7.6M in community spending and about 30% is focused on international growth.  Doug hands it over to PASS President Bill Graziano.

Bill is thanking the Directors on the board that are moving on.  Douglas McDowell is the first.  Rob Farley is next who ran the SQLSaturday portfolio.  Last is Rushabh Mehta who has been on the board for 8 years serving in Marketing, Finance, President, and Past President.

Bill hands it over to the next President of PASS Thomas LaRock.  Tom introduces the board changes and new board members.  Our new board members are Jen Stirrup, Tim Ford, and Amy Lewis.  Congratulations to them all!  I served on the Nomination Committee this year and I know that they will all do amazing things for our community.

Tom mentions the time and dates for the BAC conference and 2014 Summit. He is also telling us about all the amazing things happening this week like the community appreciation party at the Nascar Hall of Fame and the SQLClinic where you can talk to the MS folks that make the product we all love so much.

David DeWitt is on the stage to present his 5th keynote!  Today he’ll be telling us all about Hekaton.  MS marketing changed the name from Hekaton to In Memory OLTP and David encourages to vote via twitter using those hashtags.  David is explaining why MS decided they needed a new query engine.  His reason is that the software is mature and processors are starting to top out.  A common question he gets is why not just pin the tables in memory?  Latches for shared data structures like the buffer pool.  He explains how latches work and the contention and CPU they can cause.  Now we take a look at the need for concurrency control.  We have two simple rules.  First is before access the query must acquire the appropriates lock type from the lock manager.  The second is releasing the lock.  David explains that locking and latches are much more problematic with in memory databases.

Hekaton is a lock free data structure and thus there are also no latches.  Hekaton is a third query engine inside SQL along with the standard disk data engine and column store engine.  To use Hekaton we start by creating a memory optimized table and it MUST contain a Primary Key. Next we populate the table and that is done just the same as we do today and then it’s ready to use!  Typical performance boost is 3x for ad-hoc T-SQL and 5x for Stored Procedures.

David is showing a demo of how a lock and latch free data structure works.  David is very proud of his animation!  We can see how an updater can run without blocking or slowing down a reader.  Due to locking and latches the standard method is burning up!

Now we dive into concurrency control which uses optimistic, multi-version, and time stamps.  We’ll look at each of these.  Time stamps get a time from the global clock.  At the start of the transaction we get a start time and multiple transactions can get the same time, but for the end time it guarantees a unique time.  We are zooming in to see how it all works with a single row of data as an example.

David is wrapping up for us as he quickly reviews everything he just covered.  Absolutely mind blowing as usual.  I’m looking forward to playing with Hekaton myself with a product that has locking hints that cause me huge latch and lock waits.  This will also get it off of the disk so I have high hopes.

Thank you all for joining me today at this amazing event!  If you want more from the PASS Summit don’t forget about PASStv.  I’ll be doing a session on PASStv on Friday.  Here is the link.  My session is the one on Policy-Based Management and Central Management Server.


Welcome to the PASS Summit 2013 LIVE keynote blog.  I’ll be live blogging this morning’s keynote so refresh your page often.  I’ll be taking short notes to keep you up to date as opposed to paragraph form.

We are about 5 minutes from start time and this place is packed.  The room is huge and there is tons of buzz as the family unites for our big kickoff!

PASS President Bill Graziano hits the stage to welcome us all to the Summit and introduce the board of directors who work tirelessly to help keep our organization thriving in conjunction with PASS HQ.

700k hours of free technical training was delivered by the PASS organization this past year.  Bill thanks all the volunteers that make this possible.

We are watching a video using PowerMap to zoom us around the world and witness the growth of the SQLSaturday movement.   It’s amazing to see how we have grown!

Amy Lewis is announced to be this year’s PASSion award winner. If you don’t know here she is AMAZING and since she was just elected to the PASS board we know there are great things to come.

I just go announced as the honorable mention for the PASSion award.  I’m floored and honored!

Quentin Clark the Corporate Vice President of Microsoft hits the stage. He also thanks all the volunteers that make this community tick with PASSion.  The PASS organization is doing an awesome job of thanking all of our volunteers.

Quentin is going to introduce what’s ahead for SQL Server and right now is showing us what they have done in the last year.  He announces that SQL Server CTP2 is released today.

We are seeing a live demo of an online game selling store.  First we see the times for what happens in a game search and then we see the time it takes to purchase it.  These are simulated with 20k people doing it at the same time.  Now they are converting the table to an in memory table and we go from 6 seconds to .07.  We have now coined the phrase “Wicked Fast”.

Quentin is talking about some breakthroughs in availability and recovery like Always-on secondaries in Azure and the ability to backup to Azure.  They are now showing a demo of backing up to Azure.  It’s baked into SQL 2014 and they also announce multiple encryption options for your backups.  They also have a separate download to add the ability to backup to Azure fuctionality for SQL 2008.  It’s really cool to be able to backup locally and to Azure at the same time.

We are now seeing some customer case studies on PDW with DW loads going from days to hours.  They are showing a video of a case study from the city of Barcelona.  They use HDInsight and the data to see what their citizens need and cater to them, especially during their big city carnival.  Being able to decipher this data in near real time allows them to respond that way from things like sanitation censors and even casual observations tweeted from citizens.

We are seeing Microsoft’s plan for real time insights from data like Power Query and the Data Catalog.  We have ways to manipulate the data with Power Pivot and a rich presentation layer with Power View and Power Map.

Kamal a BI Program Manager comes on stage to show us a demo.  He is going to show us an example using data from Skype.  We start in Excel with Power Query.  He does mention that the data is not real data to protect exposing Skype proprietary data.  We connect to the data, pull it in, and convert it to JSON all with just a few clicks.  The big selling point here is how easy it is.  As a data professional we know other methods to get the data, but it’s complicated.  This gives that power to a regular business user with great ease.  Now that we have the fake Skype data we are going to pull in some more information using Power Query to correlate that with a web data search for countries and their population.  This allows us to easily see calls per capita.  Now that we have our data we get a data model that we can easily use on multiple devices.  We see that it works on a Surface 2, and an Android, and laptop, and even a “fruity device” if you want to lower you standards.  I’m sure you folks can guess what device that is!

Now Kamal logs into Power BI in Office 365 and simply types in “calls per capita per population” and instantly we see a chart using the same data model.  He does it again with “calls by country and destination as map” and we get the same thing on that data model, but we get a beautiful map.  You can signup at powerbi.com and check it out by querying data with every day language.

Microsoft is announcing a Power BI contest to show them how you are pushing the boundaries with Power BI.  www.facebook.com/microsoftbi to enter the contest and they are giving away Surface 2s and Xbox Ones to the winners.

Quentin wraps it up as he challenges us to push the boundaries with Power BI.

Thanks for joining me to today and make sure to come back tomorrow for day 2!

Things have been too quiet here on the blog, but there’s a reason (a few actually).  This year has been nuts for me, but it’s all good stuff!  We just moved, and moving a family is not fun.  It was a long process from selling the old house to searching and finding a new one.  Like I said, the moving part is not fun but we couldn’t be happier now that we’re mostly settled in.  Unlike the old house, this neighborhood actually has a community and the house is right across the street from 1 of 4 neighborhood parks.  Now I can finally work on getting my oldest daughter’s training wheels off without getting run over by cars!

It’s also been a busy SQL community year for me.  I’m the President of the PASS Performance Virtual Chapter, serve on the board of directors for the North Texas SQL Server User Group, serve as a PASS Regional Mentor, and serve on the PASS 2013 Nomination Committee.  It’s a ton of work, but it’s rewarding to help others and I love every second of it!  Of course I have a day job that keeps me hopping as well.

One of the most exciting things to happen is all the speaking engagements I have been fortunate enough to do.  This is where a lot of my blogging time has gone…into creating new presentations.  There is nothing more fun and exciting than teaching someone something new or explaining things so they truly get it.  I was chosen to be a Technical Lead on SQLCruise this year (Tim Ford must have lost his mind).  It was an amazing experience to be able to dive deep with attendees after hours.  I am truly humbled to speak at these events among absolute SQL rock stars.  So where will I be?  Here we go…

If you’re going to be at any of these events, I hope you’ll come see me!  Expect the blog to start back up soon.  I’ve been writing posts, they are just in draft until I can put the finishing touches on them.

SQLCruise Technical LeadSummit2013 24HOP DevConnections SQLSat

Holy Schnikes!PASS Summit 2013

I am beyond excited to announce that I have been accepted to speak at the annual SQL PASS Summit 2013 in Charlotte, NC.  This will be my first time to speak at this event and I can’t wait!  The session I will be presenting is titled “How Active Directory Affects SQL Server”.

I have been working with SQL Server since the 2000 release and I have been working with Active Directory since it hit the shelf.  Prior to becoming a full time production DBA for Verizon, I was an AD guy and actually helped design and architect their worldwide Active Directory infrastructure.  I couldn’t be happier that this was the session that got picked from my submissions.  It’s a perfect melding of my two favorite technologies and I have a unique and rare perspective of both how they can work together and how they can work against each other.  For instance, have you enabled Instant File Initialization for your SQL Server instance?  Good!  Did you go back and make sure the setting was still there 90 minutes later?  If you haven’t then you better go double check it because Active Directory has a mechanism to not only change it, but persist that change.  If Active Directory was SQL Server’s kid then it just pulled a Ferris Bueller.

If you are planning to attend the conference make sure to add my session to your schedule.  I will explain the mechanism I alluded to in the above example along with all the other things good and bad that it can do, plus much more.  Here is the abstract:

If you have ever had a Kerberos or SSPI context error, then you won’t want to miss this session. SQL Server has a large surface area and Active Directory can influence a big part of it. I will discuss AD DNS configuration, Group Policy Objects, Kerberos (of course), and how all of them affect your SQL Server. By the end of the session you’ll have a check list of things to discuss with your domain administrator when you return to work.