Get SQL Server SPN information for all servers using PowerShell

By:   |   Comments (8)   |   Related: 1 | 2 | 3 | > Security


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.

SPN User Object Query

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]

SPN Computer Object Query

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.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Brian P ODwyer Brian P ODwyer is a BI Analyst at Cook Children's Health Care System who knows both Infrastructure and SQL server

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, October 25, 2017 - 11:26:46 AM - David Masciangelo Back To Top (68780)

Thanks for the script.  Worked perfectly and now I can remove the SPN detritus in my AD.

Monday, March 31, 2014 - 6:00:47 PM - Brian ODwyer Back To Top (29930)

I am glad I could help, this was an issue for me, once you have created SPNs how do you track them.

I should have mentioned in the tip that the directory entry was a generic holder and that it should be modified for your own domain. As an example, if you have ibm.com as the AD domain name in DNS,  then your NETBIOS name is ibm and the LDAP would be dc=ibm,dc=com. It would reflect the DNS namespace that AD uses for itself which can be several levels deep, test.microsoft.com, is test for NETBIOS and gives, dc=test,dc=microsoft,dc=com in the LDAP root.

 

 

Monday, March 31, 2014 - 12:29:17 PM - Gregory Hartt Back To Top (29927)

I was having a tough time connecting to my domain with the code supplied above:

$root = New-Object DirectoryServices.DirectoryEntry 'LDAP://dc=mydomain,dc=ldap'

since my domain has a .corp tacked on to it, I changed it to this:

$root = New-Object DirectoryServices.DirectoryEntry 'LDAP://dc=domain,dc=corp'

 

...and it worked! thought I would share, and I wanted to say thanks, this helped me clean up alot of SPNs and find ones that needed to be created!!

Thursday, February 20, 2014 - 5:12:23 PM - Brian ODwyer Back To Top (29519)

You should be able to use a general user account level permissions, just need to be able to browse AD and read objects. In my environment I can do it with a regular user account not a Domain admin level account, but in your environment it may be different. There may be AD object ACLs that prevent you from seeing these attributes but you could check with the AD team or Security team if they control this.

You can download the Softerra LDAP Browser 4.5 and then run it to see what level of access you have in browsing AD via LDAP. It will make an anonymous connection to AD by default with the newer versions but you should use AD credentials and SSL (port 696) to secure data stream. When you make a connection via LDAP you should use your DN(distinguishedname) to connect, not your user ID. It consists of your cn, the name shown in AD Users and Computers in the General tab at the top, plus your location in the AD hiearchy such as OU=MyOU,DC=MyDomain,DC=Mytoplevel. So if your account was [email protected] as a UPN then it would perhaps look like, CN=Jim Joe Bob,OU=MyOu,DC=mycompany,DC=org where Jim Bob has a middle name Joe, but you would have to look at the name shown in the General tab to be sure.

Hope that helps and that you are not locked out of those attributes, in general you have browse rights to a lot of information in AD.

 

 

Thursday, February 20, 2014 - 2:46:50 PM - Bill Back To Top (29518)

What permissions are required to execute the Powershell script to get the AD information?

Wednesday, February 19, 2014 - 8:43:51 PM - Brian ODwyer Back To Top (29509)

Yes they are the AD attributes for User and Computer objects and there are many other attributes that are like this. This is a throwback to the old NT domain structure, many AD features still leverage this and it makes it difficult at times to get information out of AD without using bit arithmetic to get answers. The great thing about PowerShell is that there are AD providers to allow you get into AD and not have to deal with some of the these issues. AD was not a rewrite of the NT domain structure but layering of other features developed for Exchange on top of it.

Unfortunately, I cannot go into too much detail in the tip about AD and PowerShell but will answer any questions I can offline.

Thanks

BrianO

Wednesday, February 19, 2014 - 4:31:39 PM - Noel Back To Top (29508)

I found my own answer:

FROM : http://chrisbeams.wordpress.com/2009/05/10/active-directory-samaccounttype/

A sAMAccountType is a single valued indexed(present in the GC) attibute that uniquely defines user objects:

268435456 SAM_GROUP_OBJECT
268435457 SAM_NON_SECURITY_GROUP_OBJECT
536870912 SAM_ALIAS_OBJECT
536870913 SAM_NON_SECURITY_ALIAS_OBJECT
805306368 SAM_NORMAL_USER_ACCOUNT
805306369 SAM_MACHINE_ACCOUNT
805306370 SAM_TRUST_ACCOUNT
1073741824 SAM_APP_BASIC_GROUP
1073741825 SAM_APP_QUERY_GROUP
2147483647 SAM_ACCOUNT_TYPE_MAX

Wednesday, February 19, 2014 - 3:27:06 PM - Noel Back To Top (29507)

What do the numbers 805306369 & 805306368 designate?   

Switch ($row1.Get("sAMAccountType").ToString())
        {   805306369 {$row.Item('sAMAccountType') = "Computer"}
            805306368 {$row.Item('sAMAccountType') = "User"}














get free sql tips
agree to terms