Creating a Kerberos Delegation Table for SQL Server Access

By:   |   Updated: 2014-03-05   |   Comments (1)   |   Related: > Security


Problem

We have over a hundred SQL Server instances covering versions SQL Server 2000 to SQL Server 2012. The issue is that we want to pass the User or Computer credentials from one Service to another SQL Server Service on a remote computer or for Linked Servers that are used to run remote T-SQL and as often is standard practice in many large environments generic SQL Logins or Windows Logins are used to run the Linked Server T-SQL. This leads to authentication and access problems not to mention the inability to audit who is doing what and where are they are doing it. Not the optimal way to manage Security. We find that we are stuck with the Double Hop (or more) problem unless we use Kerberos Authentication rather than NTLM Authentication.

Solution

This leads us to Kerberos Delegation. Kerberos Delegation can usually resolve these issues for us, but like with Kerberos's associated Service Principal Name (SPN) the information about Kerberos Delegation is stored as an attribute in Active Directory (AD). We have to get the msDS-AllowedToDelegateTo attribute information to find out how Kerberos Delegation is configured. The solution is to use PowerShell to read the Kerberos Delegation from AD and to use a variation of a prior SQL Table to store the information that we extracted from AD, specifically the msDS-AllowedToDelegateTo attribute. The PowerShell script will search AD, find the attributes and then upload it into a SQL Table using a SQL View.

Using this methodology we can get an up to date and accurate listing of Services/Servers that have Kerberos Delegation associated with them for SQL Server. We could use this to find any and all Kerberos Delegation with a slight modification.

Setting up Kerberos has been handled on MSSQLTips and many other sites as have the Kerberos Delegation issues here and why they show up in our environment.

My solution uses PowerShell to read the msDS-AllowedToDelegateTo 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 data table. We will use a SQL View to bulk load the data with an Identity column on each row. A default DateTime column will timestamp the row so we have some audit capability.

The steps we will take involve creating the SQL Table and View in an existing (or new) database. We follow by running a PowerShell script that will read the msDS-AllowedToDelegateTo attribute from User and Computer objects, filter them for only SQL Server and put them into 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 table to find when a Kerberos Delegation was added, which objects have Kerberos Delegations and how many they have. With this type of information we can troubleshoot and determine if we need to add a Service Principal Name (SPN) or Kerberos Delegation to a Computer or User object.

One could modify the script to do an entire Forest of Domains, but that is something left to those who need that functionality.

Create the SQL Server Table and View

Let's start with the Kerberos Delegation History table, we will create a table to store the data in. The code is shown below. 

 CREATE TABLE [dbo].[Kerberos_Delegated_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,
 [DelegatedSPN] [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].[Kerberos_Delegated_History] 
ADD  CONSTRAINT [DF_Kerberos_Delegated_History_Daterun]  
DEFAULT (getdate()) FOR [Daterun]
GO

Now we need to add the SQL View for the Bulk Insert in the 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 [dbo].[Kerberos_Delegated_Insert]
AS
SELECT
[DN]
, [cn]
, [sAMAccountName]
, [sAMAccountType]
, [DelegatedSPN]
, [Daterun]
FROM [dbo].[Kerberos_Delegated_History]
GO

Now we have all the required SQL parts 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.

PowerShell script to pull msDS-AllowedToDelegateTo attributes from AD 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 would need to modify the script to use the Get-ADObject to speed it up, but that requires RSAT tools on system running the PowerShell Script.

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 table. 

#
$root = New-Object DirectoryServices.DirectoryEntry 'LDAP://dc=mydomain,dc=ldap'
$searcher = New-Object DirectoryServices.DirectorySearcher
$searcher.SearchRoot = $root
$searcher.Filter = "(&(objectClass=user)(msDS-AllowedToDelegateTo=MSSQL*))"
$searcher.PageSize = 1000
$searcher.propertiesToLoad.Add("DistinguishedName,name,sAMAccountName,sAMAccountType,msDS-AllowedToDelegateTo")
$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("msDS-AllowedToDelegateTo") | Out-Null
#
Foreach($row in $DomainSPNs) {
    $row1=$row.GetDirectoryEntry()
    $row3 = @($row1.Get("msDS-AllowedToDelegateTo"))
    Foreach($row2 in $row3){
        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('msDS-AllowedToDelegateTo') = $row2.ToString()
        $myarray.Rows.Add($row)
        }
    }
}
#$myarray
$connectionString = "Data Source=MySQLServer;Integrated Security=true;Initial Catalog=SPNDB;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "Kerberos_Delegated_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)(msDS-AllowedToDelegateTo=MSSQL*))"
$searcher.PageSize = 1000
$searcher.propertiesToLoad.Add("DistinguishedName,name,sAMAccountName,sAMAccountType,msDS-AllowedToDelegateTo")
$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("msDS-AllowedToDelegateTo") | Out-Null

Now we have a looping section where we loop through each AD object and each msDS-AllowedToDelegateTo attribute that is associated to that AD object. It is possible you will have multiple msDS-AllowedToDelegateTo attributes for some objects such as User objects that are used as the Service Account for running the SQL Server Service for each instance.

Foreach($row in $DomainSPNs) {
$row1=$row.GetDirectoryEntry()
$row3 = @($row1.Get("msDS-AllowedToDelegateTo"))
Foreach($row in $DomainSPNs) {
    $row1=$row.GetDirectoryEntry()
    $row3 = @($row1.Get("msDS-AllowedToDelegateTo"))
    Foreach($row2 in $row3){
        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('msDS-AllowedToDelegateTo') = $row2.ToString()
        $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. It is a simple and straightforward method to get the data into the SQL table. 

$connectionString = "Data Source=MySQLServer;Integrated Security=true;Initial Catalog=SPNDB;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "Kerberos_Delegated_Insert"
$bulkCopy.WriteToServer($myarray)

We have our data extracted, formatted into a DataTable array and uploaded to a SQL 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 Kerberos Delegations in our environment.

Some simple queries of the Kerberos Delegation data

We will show some simple queries for the Kerberos Delegation 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 Kerberos Delegations 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]
 , [DelegatedSPN]
 , [Daterun]
FROM
 [SPNDB].[dbo].[Kerberos_Delegated_History]
WHERE
 [Daterun] >= '2014-02-10 12:58:36.707'
 AND [sAMAccountType] = 'User'

Kerberos Delegation User Object Query

In the above results you can see the populated table with the Kerberos Delegations information that we extracted from AD. In the result set the user JHAGsvc is delegated the SPNs for a 3-node Windows 2012 WSFC that is running a SQL 2012 Availability Group with a Listener. Each node of the cluster has two SPNs for the SQL Service of the local SQL instance for both TCPIP (:1433) and Named Pipes Protocols and the SQL Service SPNs for the Listener L_JHAVG1. This means that the User account JHAGsvc can pass Kerberos authentications to any of the SQL server instances as well as to the Listener from any of the local SQL Server instances using Linked Servers or from another service such as IIS to the SQL Service.

The next query is for Computer objects and will show Computer accounts that are allowed to delegate to the SQL Service on another remote machine. 

SELECT
 [PrimaryKey]
 , [DN]
 , [cn]
 , [sAMAccountName]
 , [sAMAccountType]
 , [DelegatedSPN]
 , [Daterun]
FROM
 [SPNDB].[dbo].[Kerberos_Delegated_History]
WHERE
 [Daterun] >= '2014-02-10 12:58:36.707'
 AND [sAMAccountType] = 'Computer'

Kerberos Delegation Computer Object Query

The above query results show Kerberos Delegations for Computer objects. The Computer object JHAG1 has the ability to delegate to the three instances in a SQL Server 2012 Availability Group and the Listener. This type of delegation is often used where the Local System or Network Service is running a service and needs to pass a User's credentials to a remote service.

Next Steps

We can add additional data to the table that we can extract from AD or use this same template to find other AD information we might like to have. This method of data extraction from AD and insertion into a SQL table is very generic; we could take this template and use it for many system level information questions.

We could use this to track all Kerberos Delegations 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"), removing MSSQL in the first and eliminating the second search condition. We can then have a database with Kerberos Delegations 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


Article Last Updated: 2014-03-05

Comments For This Article




Tuesday, January 13, 2015 - 12:03:26 PM - Ray Giacalone Back To Top (35925)

THX MISTER ODWYER! I love this script...its a great template for capturing values out of AD and populating into SQL Server tables.














get free sql tips
agree to terms