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).
By pcurd
During the migration of T-SQL code from Microsoft SQL Server 2000 to Microsoft SQL Server 2008 I found a few procedures that were running very slow. They all used the IN keyword to filter records based on the content of a concatenated key (i.e. a key using two or more fields added to each other).
I have written a quick script to test the performance of three different ways to do this (IN, EXISTS and LEFT JOIN) and the difference is staggering.
Using IN on a concatenated key for this test is in the order of 200 times slower.
The basic summary is that the technique:
select * from #performancetesttable tt
where
tt.field1 + tt.field2 not in (select tt2.field1 + tt2.field2 from #performancetesttable2 tt2)
is much much slower on Microsoft SQL Server 2008 than:
select * from #performancetesttable tt
left join #performancetesttable2 tt2 on tt.field1 + tt.field2 = tt2.field1 + tt2.field2
where tt2.field1 is null
even though in Microsoft SQL Server 2000 the performance is about the same.
The hardware difference on the two machines is greatly in the Microsoft SQL Server 2008 favour – being a dual six core machine with 32GB RAM as opposed to dual dual core machine with 4GB of RAM (only 2GB used by Microsoft SQL Server 2000)
Using IN – Microsoft SQL Server 2008 takes 20250 ms.
Using IN – Microsoft SQL Server 2000 takes 106 ms.
Using EXISTS – Microsoft SQL Server 2008 takes 103 ms.
Using EXISTS – Microsoft SQL Server 2000 takes 46 ms.
Using LEFT JOIN – Microsoft SQL Server 2008 takes 23 ms.
Using LEFT JOIN – Microsoft SQL Server 2000 takes 33 ms.
Read more for the SQL script to test this for yourself
By pcurd
The latest event in the DeveloperDeveloperDeveloper family, DeveloperDeveloperDeveloper 8 happened at the Thames Valley Park Microsoft site on Saturday 30th of January and I was amongst the 340 or so lucky ticket holders.
This year one of my friends, Rob Ashton (@RobAshton) was speaking along with some old favourites including Simon Sabin (@Simon_Sabin), Richard Hopton (@RichardHopton), Ben Hall (@Ben_Hall) and Barry Dorrans (@blowdart). I was also pleased to see Jon Skeet (@jonskeet) presenting as I had never before had a chance to see him live.
The turnout seems impressive which is to be expected considering the length of the waiting list – over 200 people within 15 minutes of the event “selling out”. At lunch, as always, seating was at a premium and even the grok talks, organised by Zi Makki (@ZiMakki), were full.
As DDD8 was the last community event Barry Dorrans was likely to present at for a while (due to his recent appointment to Microsoft), I think it is only fitting how popular the event has been. Even Barry’s book (Beginning ASP.NET Security) sold out. I was fortunate to get a signed copy which I will be sure to keep in my collection.
Jon Skeet was also kind enough to sign a copy of his latest book (C# in Depth) which I impulsed purchased.
The sessions I attended were:
Test Driven Development to save time, money and your sanity by Richard Hopton
Richard has a clear and simple approach to explaining TDD and although most of the content wasn’t new to me, his techniques and clarifications certainly made my own approaches seem more sensible. His session showed an introduction to the whys and hows of TDD and his code demo was efficient and explanatory, showing TDD and writing tests in an understandable way.
Multi-Tenant ASP.NET MVC Projects by Rob Ashton
Rob did an excellent job handling the room and although a lot of his content could be considered seriously technical, he explained it with ease and clarity. His to the point code demos conveyed the subject matter and complimented his slides and explanations exceptionally. His dry sense of humour came through and the controversial topic met with many questions that he handled with grace and technical accuracy.
C# 4 by Jon Skeet
I had been told that Jon was an excellent speaker and his reputation is well deserved, I enjoyed the level of detail and appreciated the amount of code – showing the changes rather than explaining the changes not only made them make more sense but also showed how and why you’d use the new features.
Not Everything is an Object by Gary Short (@garyshort)
I have never studied any kind of Functional Programming so was looking forward to hearing about it from Gary who has always been an excellent speaker when I have seen him before and I was not disappointed. As well as a good introduction to the fundamentals of Functional Programming, Gary launched into a humorous and informative history of programming language development. I know I’ll take at least something out of his comparison of inanimate objects and classes.
A Developer’s Guide to Encryption by Barry Dorrans
Knowing this was going to be Barry’s last talk at a DDD for the forseeable future, and last community event before “jumping the fence”, it was bound to be an entertaining talk and it was. Several of the organisers had arranged some.. interuptions themed around Barry’s departure. After several videos from people like Liam Westley (@westleyl) and Phil Winstanley (@plip) (including Barry’s poor book being burned to keep Phil’s hands warm) a video from Barry’s past showed up – The Crystal Maze. I’ll let you guess how that went down! The presentation was excellent (between videos) and as always Barry’s technical content was great. Of course, Barry’s book (Beginning ASP.NET Security) will fill the gaps!
#
next up is #Hedpe #
Thank you! 
Thank you! 
) <- \O/