SQL Server SPN & Kerberos Authentication

SQL Server uses NTLM Authentication if the SPN Fails to be created when SQL Server is started. It can be configured to use Kerberos by registering the SPN for the SQL Server;

NTLM Vs Kerberos What’s it all about?

NTLM & Kerberos are authentication protocols. NTLM was the default protocol used in old windows versions, but it’s still used today. If for any reason Kerberos fails

Kerberos	NTLM
Kerberos supports delegation of authentication in multi-tier application.	NTLM does not support delegation of authentication.
Kerberos supports two factor authentication such as smart card logon.	NTLM does not provide smart card logon.
Kerberos has the feature of mutual authentication.	NTLM does not have the feature of mutual authentication.
Kerberos provides high security.	While NTLM is less secured as compared to kerberos.

So what does this have to do with me?

The main reason we are interested in it for SQL Server is the delegation of authentication. meaning we can start connecting to sql server through SSMS from other servers. Without Kerberos Authenticaiton, when you try and connect to SSMS remotely, you will get an “Cannot generate SSPI context’ Error.

Gotcha, So how do we use it?

In order to use Kerberos authentication, you need to register the Service Principal Name (SPN) for SQL Server. SQL Server tries to do this automatically when the SQL Server Service Starts. you will see an entry on the SQL Error log, declaring if the SPN Was created Successfully or not.

SQL Server can only create the SPN Automatically if the SQL Server Service Account has permissions to create the SPN in Active Directory. The permissions required for this are the “Read servicePrincipalName” and “Write servicePrincipalName”.

And how do we apply the permissions to register the SPN Automatically?

Open Active Directory Users and Computers.
To open Active Directory Users and Computers, click Start, click Run, type dsa.msc, and then press ENTER.
Click View, and verify that the Advanced Features check box is selected.
Click Advanced Features, if it is not selected.
In the console tree, right-click the node that represents the domain to which you want to allow a disjoint namespace, and then click Properties.
On Security tab, click Advanced.
On the Permissions tab, click Add.
In Enter the object name to select, type the group or user account name to which you want to delegate permission (This is the SQL Server Service account), and then click OK.
Configure the Apply onto box for Computer objects.
At the bottom of the Permissions box, select the Allow check box that corresponds to the Validated write to service principal name permissions, and then click OK on the three open dialog boxes to confirm your changes.
Close Active Directory Users and Computers.

But I cannot give these permissions, cant i create a SPN Manually?

Of course. however it’s recommended that the permissions are granted.

Run the following command in an elevated command prompt, with your Server/Account Details;

setspn -A MSSQLSvc/<Server Name> <Account Name>

Alternatively, Microsoft have a very useful tool called “Microsoft Kerberos Configuration Manager” This tool lets you connect to the server and fix any SPN Issues you might have

Selecting an object from a property

This is a nice little piece of power shell taken from “PowerShell PowerUsers”

Get-ADUser -filter * -properties SAMACCOUNTNAME,Manager -SearchBase “OU=Manchester,OU=EH Users,DC=english-heritage,DC=org,DC=uk” | Select-Object SAMACCOUNTNAME, @{Name=”Manager”; Expression={(Get-ADUser $_.Manager).Name}}

the section highlighted in bold is very powerful, this could be used to pull back any object by specifying the property you want to look at, in this case we are looking at another AD User which is the manager of our initial user!

 

Powershell – AD Group Creation

Here is the finalized script for the Active Directory Group creation. This was used to create 194 AD Groups for our SCCM Environment. users will be added via a different script.
This creates both Domain Local and Global Groups. — See Attached.

ADAccounts.ps1

Powershell Read a CSV File into an array

To give you an idea of how powershell imports and stores a CSV File in an array i have provided the 3 following images, this gives a clear break down of a basic way to read the CSV display the array’s it has stored and how to call the data in that array.

import-csv “C:\Temp\PowerShell\ADMembers\Members.CSV”

This is the command we use to import the CSV File, Powershells stores this information, you can either store it in a variable OR you can work with it on the fly.

ARRAY/VARIABLE

$Membership = import-csv “C:\Temp\PowerShell\ADMembers\Members.CSV”

Here you can see we are loading the entire CSV into $MEMBER from this we can display the columns by declaring the column name after the variable for example;

$Membership.APP
$Membership.User

From here you can use the data in many ways, it all depends on what you want to do…

On The Fly
My ultimate goal for working with this CSV file was to Add users to groups in Active Directory we had an csv file that was provided from another database to work with, it was given in the following format;

APP,User
Adobe,User1
Adobe,User2
Adobe,User3
Office,User1
Office,User4
Office,User2
Flash,User4
Flash,User3

For this i used the foreach loop;

import-csv “C:\Temp\PowerShell\ADMembers\Members.CSV” |
foreach-object {“Adding {1} to {0}” -f $_.app,$_.user}

We read the CSV As normal and then Pipe the CSV into the Foreach command,
$_ uses the current row of the array…
-f enables us to call out each column independantly, these are decalred after the -f, SO;
$_(Current Row).APP(Column) is set as {0} AND $_(Current Row).user(Column) is set as {1}
This little peice of script gives the following result;

Obviously from here we just need to write the section to add the users into the correct group, this is covered in a previous post.

Creating AD Groups and Adding Members….

This script is subject to change, as part of our transformation project my employer asked us to create a number of AD Groups which we can use to add users to in order to deploy applications via SCCM. although this is slightly out of the remit of my day to day work i thought it would be best to be involved so the naming conventions that we use all tie in together.

Generally in SCCM we are loading our applications in with the following convention;

<Vendor>_<ApplicationName>_<Version>_<Release>

This means we end up with something similar to;

Adobe_Acrobat_10_R01

inorder for them all to conform i had extracted a list of applications into a CSV File. The customer is due to provide a list of users and what applications they use, this list will be scrutinised by our licencing team and approved to be published to our live enviroment.

The Powershell i have created is subject to change as the file they provide may not be as expected! this was setup as a demonstraton to the project managers in a test envroment to show them the theoretical output. — see the attached files.

AppsPerUser.csv || Applications.csv || Function CreateADGroup.ps1