Saturday, September 26, 2009

Windows 7 Kerberos updates and foreign realms

I bit the bullet, bought a new 500Gb Seagate drive for my laptop, VM’d my Vista install, swapped the drive and changed my main laptop to Windows 7 over the weekend, and apart from Sony PC Suite, which just sits in the system tray looking dead, everything is running better than ever… 4Gb of ram certainly helps things along but this is faster than XP Sp3 in my FITA (Finger In The Air) opinion and light years ahead of Vista aka Windows Me 2007 …
So I added the laptop to my AD domain e.g. and then while at a client site mapped a drive to a server in their AD domain e.g., saved the credentials and all worked as expected… but later while I was debugging a kerberos issue with some users on XP I forgot I was on 7 and ran klist (now an OS standard tool at least in 7 RC1) and noticed something strange but very pleasing. Rather than just seeing a TGT from my domain e.g. me @ I now saw TGTs for the me @ and cifs TGS tickets for the server in! Sweetness! I’m getting TGTs and tickets for resource from an untrusted realm / forest / domain, no more failover to NTLM outside the forest / trust boundary, brilliant! but how? I need to know…This did not happen in XP or Vista, so what have Microsoft done? I’ve only noticed this behaviour today and have started digging out details on Kerberos improvements in Windows 7 but thus far i’ve found zip, nada, nowt… once I do I’ll be sure to post…
Anyway random stuff and quite interesting, at least to me…
Got a response from Microsoft Response from MS which says the functionality was always there but imho it wasn't... but going to dig out an xp and vista vpc and retest just to be sure...
Update 7/1/09 :- I recompiled and used this old tool of mine to try and validate the tickets under Windows 7 and Windows XP. Running this tool under Windows 7 gives totally different results to running KLIST.EXE under Windows 7 but the results from kticket.exe are consistent between Windows 7 and Windows XP. So is it just Klist.exe that is different between Windows 7 and Windows XP or is there some underlying fundamental difference with Kerberos in Windows 7... Only way to be sure is going to take a network trace and compare....
Update 9/26/09:- not forgotten this one, should be getting the traces this week...

The difference in TRUNCATE and DELETE in Sql Server - Raymond Lewallen - CodeBetter.Com - Stuff you need to Code Better!

Shamelessly stole this as I just found it very useful setting up some resiliency through replication on a DB...
The difference in TRUNCATE and DELETE in Sql Server - Raymond Lewallen - CodeBetter.Com - Stuff you need to Code Better!:
"The difference in TRUNCATE and DELETE in Sql Server
I’ve answered this question many times, and answered it again this weekend. What is the difference when doing a DELETE TableA instead of TRUNCATE TableA? A common misconception is that they do the same thing. Not so. In fact, there are many differences between the two.
DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted.
You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other constraint in place.
TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.
You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the constraints, TRUNCATE the table, and reapply the constraints.
TRUNCATE will reset any identity columns to the default seed value. This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns. After TRUNCATEing your table, when you insert a new record into the empty table, the identity column will have a value of 1. DELETE will not do this. In the same scenario, if you DELETEd your rows, when inserting a new row into the empty table, the identity column will have a value of 265."

Tuesday, September 15, 2009

SQL Authentication issues and MaxTokenSize

Saved my bacon yesterday so selflessly reposted the link below:

DBA's and web developers at our company are experiencing issues with connecting to SQL instances using SQL Server Management Studio and other SQL tools using Windows Integrated Authentication. Our company is large, with well over 70,000 users and groups in Active Directory. When we look in the NT event log on the SQL Server we see both MSSQL and Kerberos errors. What could be causing this?

The issue occurs when a user has membership in many Active Directory groups, usually over 100 (this number includes explicit membership as well as inherited membership from other groups). The default value for the MaxTokenSize is 12000 decimal, although there is no registry entry for the default value. In the great majority of organizations this size is more than adequate; however, in large organizations users' tokens may be larger than the default. Since Kerberos won't accept broken tokens, authentication fails. If you are encountering this issue you will see two errors indicating that the default MaxTokenSize is not sufficient:

NT error log entry from the Kerberos service

Kerberos message in the NT event log

NT error log entry from the MSSQL service

MSSQL message in the NT event log

Microsoft has a utility called TokenSZ that can be used to determine the MaxTokenSize for a user. There are a number of switches that can be used with the utility, but the general syntax to calculate the max token size is:

Sample TokenSZ Syntax

E:\>tokensz /compute_tokensize /user:Administrator / /password:OU812

Results of executing TOKENSZ

Output after executing TokenSZ

At the top of the window above you can see that the default token size is 12000. The text outlined in red is the actual Max Token size for the Administrator of the domain...well below the MaxToken default. The company I am currently assigned to is encountering MaxTokenSizes between 12000 to 15000 so a modification was required.

A registry entry modification or addition is required to modify the default MaxTokenSize allowed by the server. The location of the registry change is in the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters section. Note that the server on which TOKENSZ was executed does not have an entry for MaxTokenSize:

Parameter Values in the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters section

To create the registry entry, open Registry Editor by clicking Start -> Run, then typing regedit. Once Registry Editor is open navigate to the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters section. Right-click the white area on the right side and choose New -> DWORD value:

Creating a new DWORD value

Rename the entry to "MaxTokenSize", double-click to edit it, choose Decimal, and enter 65535:

Editing the value of the Registry entry

Any server workstation or server that interacts with SQL Server will require the registry entry. Also, the machine will require a reboot for the change to take effect. Once the reboot has occurred you can execute TokenSZ again to see if the MaxTokenSize value has changed:

Result of TokenSZ execution after modifying the MaxTokenSize value

Search Brian Hehir's sites