Unable to add certain Active Directory users as Windows Logons to SQL Server 2008 on Windows Server 2008 R2

By pcurd

Last week I faced an issue where I was unable to create Windows Logons for SQL Server 2008 from Active Directory users on a Windows Server 2008 R2 server when a Windows Server 2008 server was able to.

The error was a 15401 error which are quite common and usually mean your Server Principal names are wrong, however I had checked this and knew it was not an authentication issue. See my post on Server Principal Names for more details on this. Plus I was able to add other users. The error was “Error 15401 – Windows NT user or group ‘domain\username’ not found”.

There is a Knowledge Base article which describes the problem (with a hotfix) but the symptoms are not the same as the knowledge base so I hope this makes it easier to find. The knowledge base article is KB976494 (Error 1789 when you use the LookupAccountName function on a computer that is running Windows 7 or Windows Server 2008 R2) http://support.microsoft.com/kb/976494.

Find Query Plans on Microsoft SQL Server

By pcurd

Today I was asked by Luke Smith a question about Query Plans in Microsoft SQL Server and how to return the Cached Query Plans to check they are decent.

I began to think about the DMV (Dynamic Management View) sys.dm_exec_cached_plans and whilst that includes some important information, it doesn’t include the plan itself.  So then I turned to another DMV, sys.dm_exec_query_plan passing it the plan_handle from the first DMV.

Finally, to be useful as a diagnostic tool I wanted the query text so I added in a third DMV sys.dm_exec_sql_text again passing the plan_handle. I then sorted by usecounts which does what it says on the tin and limted to those that are not system procedures, are actually plans, as opposed to records from parse trees or extended stored procedures, and have been used more than 10 times.  This returned a lot of data for me so I limited to the top 100, but this could obviously be tuned to a different environment.

Running this in SQL Server Management Studio has the bonus of being able to click on the XML in the query_plan and view the plan graphically which is definitely better than the XML!
Read more »

Renaming Microsoft SQL Server servers and the effects on SPNs

By pcurd

Last week I saw a post on Simon Sabin’s blog about SQL Server service accounts and SPNs and made a comment about the importance of SPNs when renaming a SQL Server or migrating several servers into .. less than several. I felt this an area worth expanding a little more.

I’ll describe a hypothetical SQL Server infrastructure, changes to be made and how I would resolve the Kerberos issues that would result.

The base infrastructure:

A simple SQL Server environment with two Microsoft SQL Servers – SQLA and SQLB. The dataset on these two servers is different, no databases are shared. The domain for this company is “example” and the DC is “example.com” and so their usernames are formed “example/UserName”.

The new infrastructure:

A new SQL Server is purchased with power enough to run the entire dataset and it is to be called SQLA. The service account is to be a domain user called “sqlservice”.

However, there are a lot of applications that link to SQLB by name and recoding them all is considered too much work. (A classic example is Access which doesn’t make changing the source of tables easy without relinking)

A solution:

Migrate the total dataset to the new server, assign it a name of SQLA and take account of Simon’s SPN advice – i.e. use Network Service or a domain account to run the SQL Service. Use DNS to create a record for “SQLB” pointing to SQLA. If you want to be really fancy, assign the original IP address of SQLB as an additional IP on SQLA.

The result:

Connecting to “SQLA” via NTLM, SQL Database logins (assuming they were migrated too) and Kerberos works fine – as you’d expect. However, connecting to “SQLB” only works for NTLM and SQL Database logins – Kerberos fails along the lines of “Cannot generate SSPI Context”.

The solution:

As you may have guessed from the title of this article, the solution lies with SPNs. When you register for a Kerberos token you are doing so against a server name – and in this case, when talking to “SQLB”, that server name is wrong. So how does one send the correct name? Well in the example above, you can’t. You are stuck sending “SQLB”. The only solution is to make the name not wrong. To do this, we register another SPN against SQLA – effectively allowing it to understand and use the tokens made against “SQLB”.

The syntax is along these lines: (Please note, written from memory so some tweaking may be in order)

SetSPN -a mssqlsrv/SQLB.example.com:1433 example\sqlservice
SetSPN -a mssqlsrv/SQLB.example.com example\sqlservice
SetSPN -a host/SQLB.example.com
SetSPN -a host/SQLB

More details on SetSPN can be found on MSDN at http://msdn.microsoft.com/en-us/library/ms178119.aspx (Registering Kerberos Service Principle Names by Using Http.sys).