Problem
We have over a hundred SQL Server instances with versions running from SQL Server 2000 to SQL Server 2012 with Availability Groups. The issue is that we want to use Kerberos Authentication for remote client connections to the SQL Server instances whenever possible. Now setting up Kerberos has been written about on MSSQLTips and many other sites so we can easily setup the SPNs for the instances. The real problem comes with maintaining an accurate and up to date listing of the SPNs for all these instances. Well we can do that with a little PowerShell and some T-SQL. We can have PowerShell read the SQL Server SPNs from Active Directory (AD) and then put them into a SQL Server table we have previously created by using the .NET SQL Client to load them with a SQL Server View. Once we have a SQL Server table we can use the whole T-SQL tool set to find what we want from the data which is accurate since it comes from Active Directory where the SPNs are stored.
Solution
My solution uses PowerShell to read the SPN attribute from User and Computer objects in AD and then uses the .NET SQL client functionality accessed from PowerShell to load the data into a SQL Server data table. I will use a SQL Server View to bulk load the data and still have an Identity column on each row. A default Datetime column will timestamp the row so I have some audit capability.
The steps we will take involve creating the SQL Server Table and View in an existing (or new) database. This will be followed by running a PowerShell script that will read the ServicePrincipalName attribute from User and Computer objects, filter them for only SQL Servers and put them into the DataTable array. The final action of the PowerShell script then bulk loads the DataTable array into SQL Server.
When we have this up to date information we can query the SQL Server table to find when an SPN was added, which objects have SPNs and how many. With this type of information we can determine if we need to add an SPN or if we should remove an SPN as we should when SQL Server instances are retired. It also allows us to easily troubleshoot issues with SPNs since we have the entire list of SQL Server SPNs in our domain. We could modify the script to do an entire Forest of Domains, but that is something I leave to those who need that functionality.
Create the SQL Server Table and View
Let’s start with the SPN History table, we will create a table to store the data in. The code is shown below.
CREATE TABLE [dbo].[SPN_History]( [PrimaryKey] [int] IDENTITY(1,1) NOT NULL, [DN] [varchar](400) NOT NULL, [cn] [varchar](256) NOT NULL, [sAMAccountName] [varchar](50) NOT NULL, [sAMAccountType] [varchar](50) NOT NULL, [servicePrincipalName] [varchar](256) NULL, [Daterun] [datetime] NULL, PRIMARY KEY CLUSTERED ( [PrimaryKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[SPN_History] ADD CONSTRAINT [DF_SPN_History_Daterun] DEFAULT (getdate()) FOR [Daterun] GO
Now we need to add the View for the Bulk Insert in PowerShell. We create a standard View that will have all the columns, but the Identity column; it will be created automatically by default for us as each row is inserted. We also will have the DateTime column insert the current timestamp by default as well. The code for the View is given as:
CREATE VIEW SPNHIST_Insert AS SELECT [DN] , [cn] , [sAMAccountName] , [sAMAccountType] , [servicePrincipalName] , [Daterun] FROM [dbo].[SPN_History] GO
Now we have all the required SQL objects that we will be using. These are simple T-SQL statements and are straightforward in the implementation. You could, if you wanted to, extend these for more information, but I find this is all I need.
PowerShell script to pull SPN attributes from Active Directory and insert into a SQL Server Table
The PowerShell I will be using is based on Version 2 and does not use any special add-ins such as SQL Server or AD, just .NET functionality. You can use the AD add-in and modify the script to use the Get-ADObject to speed it up, but that requires you to install the RSAT tools on your PC so I opted for a simpler solution, a little more code, but less work finding and installing tools.
If we take a look at the PowerShell script; we can see how we find the data in AD, take the parts we want and place into a DataTable and finally insert the data into the SQL Server table.
#
$root = New-Object DirectoryServices.DirectoryEntry 'LDAP://dc=mydomain,dc=ldap'
$searcher = New-Object DirectoryServices.DirectorySearcher
$searcher.SearchRoot = $root
$searcher.Filter = "(&(objectClass=user)(servicePrincipalName=MSSQL*))"
$searcher.PageSize = 1000
$searcher.propertiesToLoad.Add("DistinguishedName,name,sAMAccountName,sAMAccountType,servicePrincipalName")
$DomainSPNs = $searcher.FindAll()
#
$myarray = New-Object System.Data.DataTable
$myarray.Columns.Add("DistinguishedName") | Out-Null
$myarray.Columns.Add("Name") | Out-Null
$myarray.Columns.Add("sAMAccountName") | Out-Null
$myarray.Columns.Add("sAMAccountType") | Out-Null
$myarray.Columns.Add("ServicePrincipalName") | Out-Null
#
#$DomainSPNs
#if ($DomainSPNs -ne $null) {
Foreach($row in $DomainSPNs) {
$row1=$row.GetDirectoryEntry()
Foreach($row2 in $row1.ServicePrincipalName.Value){
if($row2.subString(0,5) -eq "MSSQL"){
$row = $myarray.NewRow()
$row.Item('DistinguishedName') = $row1.Get("DistinguishedName").ToString()
$row.Item('Name') = $row1.Get("name").ToString()
$row.Item('sAMAccountName') = $row1.Get("sAMAccountName").ToString()
Switch ($row1.Get("sAMAccountType").ToString())
{ 805306369 {$row.Item('sAMAccountType') = "Computer"}
805306368 {$row.Item('sAMAccountType') = "User"}
}
$row.Item('ServicePrincipalName') = $row2
$myarray.Rows.Add($row)
}
}
}
#$myarray
$connectionString = "Data Source=MySQLServer;Integrated Security=true;Initial Catalog=SPNDB;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "SPNHIST_Insert"
$bulkCopy.WriteToServer($myarray)
We can break the script into parts by functionality. The first section is the connection to AD and the extraction of the SPN attributes.
$root = New-Object DirectoryServices.DirectoryEntry 'LDAP://dc=mydomain,dc=ldap'
$searcher = New-Object DirectoryServices.DirectorySearcher
$searcher.SearchRoot = $root
$searcher.Filter = "(&(objectClass=user)(servicePrincipalName=MSSQL*))"
$searcher.PageSize = 1000
$searcher.propertiesToLoad.Add("DistinguishedName,name,sAMAccountName,sAMAccountType,servicePrincipalName")
$DomainSPNs = $searcher.FindAll()
The next section is the creation of the DataTable array for storing the data that we have extracted and will push into the SQL Server table.
$myarray = New-Object System.Data.DataTable
$myarray.Columns.Add("DistinguishedName") | Out-Null
$myarray.Columns.Add("Name") | Out-Null
$myarray.Columns.Add("sAMAccountName") | Out-Null
$myarray.Columns.Add("sAMAccountType") | Out-Null
$myarray.Columns.Add("ServicePrincipalName") | Out-Null
Now we have a looping section where we loop through each AD object and each SPN that is associated to that AD object. It is possible and highly likely that you will have multiple SPNs for some objects such as User objects that are used as the Service Account for running the SQL Server Service for each instance. If you are using SQL Server 2012 Availability Groups (and Listener) you will definitely have more than one SPN associated to your Service account for Kerberos authentication.
Foreach($row in $DomainSPNs) {
$row1=$row.GetDirectoryEntry()
Foreach($row2 in $row1.ServicePrincipalName.Value){
if($row2.subString(0,5) -eq "MSSQL"){
$row = $myarray.NewRow()
$row.Item('DistinguishedName') = $row1.Get("DistinguishedName").ToString()
$row.Item('Name') = $row1.Get("name").ToString()
$row.Item('sAMAccountName') = $row1.Get("sAMAccountName").ToString()
Switch ($row1.Get("sAMAccountType").ToString())
{ 805306369 {$row.Item('sAMAccountType') = "Computer"}
805306368 {$row.Item('sAMAccountType') = "User"}
}
$row.Item('ServicePrincipalName') = $row2
$myarray.Rows.Add($row)
}
}
}
We now have the last section where we take the DataTable array and load it into the SQL Server table using the SQL Server view we created for that purpose. Maybe not elegant, but it is a simple and straightforward method to get the data into the SQL Server table. As I learned in Graduate School, possession of the solution is 9/10s of the law.
$connectionString = "Data Source=MySQLServer;Integrated Security=true;Initial Catalog=SPNDB;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "SPNHIST_Insert"
$bulkCopy.WriteToServer($myarray)
We have our data extracted, formatted into a DataTable array and uploaded to a SQL Server table in a database. All we have to do now is query this information and we have accomplished our goal, having an up to date listing of SPNs in our environment.
Query the SPN data in SQL Server
We will show some simple queries for the SPN data and you can, once you have the data, write your own to do whatever you need. The first query is one to find the SPNs associated to User objects which will primarily be service accounts if you are using that security model for your instances.
SELECT [PrimaryKey] ,[DN] ,[cn] ,[sAMAccountName] ,[sAMAccountType] ,[servicePrincipalName] ,[Daterun] FROM [SPNDB].[dbo].[SPN_History] WHERE DN NOT LIKE '%workstation%' AND [Daterun] >= '2014-01-09 00:00:00.000' AND sAMAccountType = 'User'
The results of this will exclude in this case SQL Express instances on workstations in AD Organization Units (OU) that are filtered by the DN or DistinguishedName of the AD object.
In the above results you can see the populated table with the SPN information that we extracted from AD. In the result set we have the SPNs for a 3-node Windows 2012 WSFC that is running a SQL Server 2012 Availability Group with a Listener. Each node of the cluster has the SPNs for the local SQL instance for both TCPIP (:1433) and Named Pipes Protocols and the SPNs for the Listener L_JHAVG1. We also have the SPNs for a SQL Instance that is not part of the 2012 Availability Group.
The next query is for Computer objects and will return those SQL Instances where the SQL Server service is running as Local System or Network Service on the computer.
SELECT
[PrimaryKey]
,[DN]
,[cn]
,[sAMAccountName]
,[sAMAccountType]
,[servicePrincipalName]
,[Daterun]
FROM
[SPNDB].[dbo].[SPN_History]
WHERE
DN NOT LIKE '%workstation%'
AND [Daterun] >= '2014-01-30 00:00:00.000'
AND sAMAccountType = 'Computer'
ORDER BY
[cn]
The above query results show SPNs for Computer objects. We have default instances as well as Named instances, MSSQLSvc/PPDBDEV.MyDomain.LDAP:SQL2K801 on port 1856 as MSSQLSvc/ppdev.MyDomain.LDAP:1856 is an example. For a default SQL Server Express instance we have MSSQLSvc/MyDomainSPSHARED01.MyDomain.LDAP:SQLEXPRESS as an example of what it would appear as.
Next Steps
We can add additional data to the table that we extract from AD or use this same template to find Kerberos Constrained Delegated services. That is the next installment of using SPNs and the attributes of AD. Why use Kerberos Constrained Delegation, to avoid the Double Hop issue and allow us to pass the AD credentials from one Service to another Service on a different server. This can be leveraged to use the requesting users ID in Linked Servers explicitly and give much tighter access and auditing. No intermediate generic account or the account running the SQL Server service.
We can even use this to track other SPNs by just modifying the search condition in the LDAP Search filter, (&(objectClass=user)(servicePrincipalName=MSSQL*)) and the search in the loop, if($row2.subString(0,5) -eq “MSSQL”). We can then have a database with any or all SPNs in our AD domain. You can even extend the PowerShell to do an entire AD Forest.
- To learn more about manual SPN registration look here: Register a SPN for SQL Server Authentication with Kerberos
- To read more about why you may want to use Kerberos: Understanding When SQL Server Kerberos Delegation is Needed
