SQL Server - Unable to Generate SSPI Context

The different sorts of authentication mechanisms in play in a Windows network can be pretty complex.  So when someone asked me, "Why do I get a 'Could not generate SSPI context' error when I try to log in to a SQL server?" I knew that there could be several answers to that question.  Go ahead and Google it yourself -- you won't get a definite *This is absolutely your problem* sort of answer.

First I remembered that there was a situation where RSA SecureID tokens (essentially certificates for our purposes) were used for various authentication tasks in the domain, and if one tried to authenticate to a SQL Server with Windows authentication without having one's RSA token plugged in, the "Could not generate SSPI context" error would be generated. Plug the SecureID device into a USB slot, and you'd log in to the SQL server just fine. But I knew that policy was not an issue in this situation...

Then I thought about how services not having their SPNs registered with Active Directory can cause authentication problems.  Specifically, if a SQL Server doesn't have its SPN registered properly in Active Directory, Kerberos authentication cannot be used.  But that still shouldn't prevent you from authenticating whatsoever... it'll just drop you down to NTLM instead of Kerberos.

Also, I was able to perform a logon with Windows auth to the same SQL Server at the same privilege and security level as the user, so I knew it had to be something at their end.

The only other thing I could think of was that something was just wrong with their security token that was confusing their SSPI?  Maybe it was corrupt somehow?  I'm not sure.  So, I recommended that the user run "klist purge" to purge all their domain controller-issued tickets, knowing that they would be refreshed as soon as they requested access to a domain resource...

Bingo.  Problem solved.

RSA UnSecureIDs

It seems like a lot of interesting computer security stuff is coming out of the French lately.  First we had Mimikatz, and now this:

RR-7944.pdf (765.37 kb)

I hadn't been interested enough to consider it before, but I do find it mildly surprising that a block cipher was used in such a high-profile implementation.  Although I have been known to use ECB (Electronic Codebook) to obscure really short pieces of text, it's typically a very poor choice for encrypting large blocks of text as it becomes easier to cryptanalyze the more encrypted sample text you have.  CBC seems pretty cool though as it introduces feedback and seems to eliminate all semblances of human-recognizable patterns. Anyhow, I digress as that actually has nothing to do with the sort of padding attacks that are outlined in that document above.  It's a pretty fascinating read.  Between that and Windows Internals my brain feels like it's getting sprayed with a fire hose of knowledge lately.

Windows Internals 6th Edition, and a Bonus Powershell Script

I started reading Windows Internals, 6th Edition about a week ago. In case you don't know, it was authored by Mark Russinovich, David Solomon and Alex Ionescu. It's been great so far, packed full of ridiculously detailed technical information on how the Windows operating system works at its most fundamental level. And there is no one on the planet who knows more about that very topic than those three guys. Weighing in at about 750 very dense pages - and that's just part 1 - it's not for the faint of heart. But if you do have the fortitude and desire to consume this kind of material, you'll be rewarded with being able to explain to people what the differences between the Kernel and the Executive are, how to examine the Kernel Processor Control Block in Windbg, etc.  Good stuff.

Now, context switch:

I wrote this little Powershell script a few days ago to help me automate some SQL stuff.  I realize that there are already other ways to do distributed SQL queries and so I'm sort of reinventing the wheel here, but hey... now it's in Powershell. Automation-ready and no Management Studio required.

<#
.NOTES
	Name  : Execute-DistributedSQLQuery.ps1
	Author: Ryan Ries
	Email : ryanries09@gmail.com
	Date  : June 07, 2012	

.LINK	
	http://www.myotherpcisacloud.com

.SYNOPSIS
	This script executes a SQL query across multiple SQL servers as defined
	either on the command line or in a file.

.DESCRIPTION
	This script executes a SQL query across multiple SQL servers as defined
	either on the command line or in a file. Use the -Servers parameter to
	define multiple SQL servers on the command line. Alternatively, use the
	-File parameter to specify a text file of SQL servers, one per line.
	Use the NonQuery switch if your SQL statement is not a SELECT-style
	query, but a stored procedure or other operation. If Username and 
	Password is specified, then SQL authentication will be used. Otherwise,
	SSPI will be used. If you want to specify a different database for each
	server, use a ! between the server name and the DB name. (On either
	the command line or in a file.) Otherwise, "master" will be the default
	database and you must specify the desired database name as part of
	your query.
	
	Use Get-Help <script> -Full for examples and more info.

.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -Servers SQLSERVER01,SQLSERVER02 -Query "SELECT * FROM DB.dbo.Inventory"
	Queries the Inventory table in the DB database on both SQLSERVER01 and SQLSERVER02. Uses SSPI authentication.
.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -File servers.txt -Query "SELECT * FROM DB.dbo.Inventory"
	Runs identical queries on each server found in servers.txt.
.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -File svrs.txt -Query "SELECT * FROM Inv" -Username ryan -Password xyz
	By specifying a username and password, the authentication method is changed from SSPI to SQL authentication.
.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -File servers.txt -Query "EXEC Clear_Inventory" -NonQuery
	Use the -NonQuery switch if executing a SQL statement that is not a SELECT query.
.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -Servers SVR01!DB1,SVR02!MgtDB -Query "SELECT * FROM Inv"
	You can specify a separate database on each server by separating the server\instance name and the database
	name with an exclamation mark. This is useful if you want to run an identical query on multiple SQL
	servers with differently-named databases. The exclamation mark syntax works both on the command line
	and in a file.
.EXAMPLE
	.\Execute-DistributedSQLQuery.ps1 -Servers SVR01,SVR02 -Query "SELECT * FROM DB.dbo.Inv"
	Remember that if no database is specified by using an exclamation mark, the master database
	will be selected by default, so to run a query on a different database on the server, you must
	specify that in your query.
#>
Param([Parameter(Mandatory=$false)][String[]]$Servers,
      [Parameter(Mandatory=$false)][ValidateScript({Test-Path $_ -PathType Leaf})][String]$File,
	  [Parameter(Mandatory=$false)][String]$Username,
	  [Parameter(Mandatory=$false)][String]$Password,
	  [Parameter(Mandatory=$true)] [String]$Query,
	  [Parameter(Mandatory=$false)][Switch]$NonQuery)

If(($Servers -And $File) -Or (!$Servers -And !$File))
{
	Throw "You must specify either -Servers or -File; not both, not neither."
}
If(($Username -And !$Password) -Or (!$Username -And $Password))
{
	Throw "You need to specify both Username and Password if using SQL authentication."
}

If($File) {	$Servers = Get-Content $File }

ForEach ($_ in $Servers)
{
	If($_.Split("!").Count -gt 1)
	{
		If($_.Split("!").Count -gt 2)
		{
			Throw "Error parsing Server.DB name. Did you use too many exclamation marks?"
		}
		$Instance = $_.Split("!")[0]
		$DB = $_.Split("!")[1]
	}
	Else
	{
		$Instance = $_
		$DB = "master"
	}
	
	If($Username)
	{
		$ConnectionString = "server=$Instance;database=$DB;user=$Username;password=$Password"
	}
	Else
	{	
		$ConnectionString = "server=$Instance;database=$DB;Integrated Security=SSPI"
	}	
	
	If($NonQuery)
	{
		$SQLConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
		$SQLConnection.Open()
		$SQLCommand = $SQLConnection.CreateCommand()   
		$SQLCommand.CommandText = $Query
		$rdr = $SQLCommand.ExecuteNonQuery();
		$SQLConnection.Close()
	}
	Else
	{
		$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter ($Query, $ConnectionString)
		$DataTable = New-Object System.Data.DataTable
		$DataAdapter.Fill($DataTable) | Out-Null
		$DataTable | Out-GridView -Title "$Instance      DB: $DB"
	}	
}

Ighashgpu and Cracking NTLM Hashes

Neat Fact of the Day:  Given an NTLM hash, the video card in my PC can attempt 1.5 - 2 Billion password attempts per second to crack that hash with brute-force, versus 9 million passwords per second with my CPU.

Video Card: Nvidia GTX 560 Ti (384 cores) using ighashgpu.

CPU: Intel i5 2500k @ 3.5GHz (4 cores)

Lesson learned: CUDA is freaking awesome. I can crack the NTLM hash of a 10-character password consisting only of digits in under five seconds.

Server 2012 Goodies

Windows Server 2012 is getting close now.  Of course I've been playing with the release candidate.  One of my favorite things so far is how easy it is to turn off the GUI, therefore turning the server into a Core installation. You can also turn off the GUI, but leave the Server Manager enabled.  Another thing that I think is going to be awesome is the Hyper-V Extensible Virtual Switch. It's obvious that Microsoft listened to its customers when they complained that the Hyper-V Virtual Switch in 2008 R2 was a bit anemic/featureless.  This is a huge enhancement, in my opinion, and is going to introduce some very interesting opportunities, such as migrating a VM to another datacenter without changing its IP address, etc.

There are a lot of other new enhancements.  I found this free eBook entitled Introducing Windows Server 2012. Check it out; there are tons of neat and enticing bits of information in there.