These commands were updated Feb 8, 2017 (v0.8.709) to incorporate feedback ❤
Right, so the first thing you need to know is: I’m not Chrissy. My name’s Drew, and I’m here tell you about an exciting new release of dbatools: schwifty!
In this release we’ve added three new commands to help you manage SPNs for SQL Server Kerberos authentication. If you have a lot of linked servers in your environment, then this is probably a welcome addition to the toolset for you.
These commands leverage SQL Server instance info from commands that already existed in the dbatools code base and active directory PowerShell support to determine required SPNs for all instances on a given computer and whether they’re set or not. If they aren’t, we have commands to not only set the SPNs for you, but also enable constrained delegation to accounts for the SPNs that were just added.
Before these functions were added to dbatools, you had a couple options when it came to checking/adding SPNs:
- setspn.exe and ActiveDirectory Users and Computers. setspn.exe is a windows command that you could use to add an SPN to given Active Directory account. And it works great! You do need to know the SPN and account you want to set it for ahead of time; it doesn’t really care about what kind of SPN you’re setting, but it’ll let you set it. You could also use it to check for dupes, list out what SPNs exist for an account… it’s a pretty well documented command. Setting the SPN is only part of what makes SQL Server Kerberos authentication, work, though. You still need to set delegation on the account to the services in question. That normally requires you going into the Active Directory Users and Computers application and adding delegation outside of the setspn.exe command prompt. Yuck!
Microsoft Kerberos Configuration Manager for SQL Server. Microsoft does have a tool to help you manage your SPNs for SQL Server too. It’s called Kerberos Configuration Manager for SQL Server and it’s awesome because not only does it tell you what SPNs need set, but it checks to see if they are there. If they aren’t, it’ll try and fix them for you, or generate a .cmd file that you can run on your own. It’s a neat tool that essentially generates SPNs based on best practice straight from Microsoft. While the auto fixing and script generation are neat, the program can be a little slow. Also: up until very recently (2017 Jan 31), it wasn’t compatible with SQL Server 2016. It also doesn’t set delegation for your accounts, either. Less yuck, but still: yuck!
Now we have four new commands to help manage SPNs – Get-DbaSpn, Test-DbaSpn, Set-DbaSpn, and Remove-DbaSpn. Our goal with the new SPN commands was to make them fast, and make them at least as useful as the GUI tool provided by Microsoft, but to allow multi-server administration and automation of setting correct SPNs and enabling constrained delegation. And we’ve done it! These commands are based on guidance from MSDN about setting SQL Server Service Principal names which you can read more about here.
Oh and for the performance minded among you, we’ve built these functions to be super fast; faster than the traditional setspn.exe and Configuration Manager options by quite a bit. While the GUI tool took up to 51 seconds per server, our testing showed the ability to return required SPN info for 9 servers in 9 seconds. That’s 1 server per second, granted, we don’t test for SSAS or SSRS at this time, but we do have it planned, and it’ll still be quick.
Take a look below for a detailed description of the new commands in this release. You can use these new functions by grabbing our latest release (0.8.709).
A couple things to note when using these functions:
- You’ll need at least permission to read from your domain’s Active Directory, and in the case of adding SPNs, the ability to modify. That means you’ll need to run the commands as a user with those permissions, or use the -Credential parameters.
- Fully explore Get-DbaSpn and Test-DbaSpn commands before moving on to Set-DbaSpn. We also included -WhatIf support in Set-DbaSpn so you can see what SPNs will be added to which accounts before you actually apply them. We’ve done our best to make sure we fully support a variety of different configurations and network names, but you should know what will be applied before you actually do it.
Returns a list of any service principal names (SPNs) set for a given server or active directory account name
Returns of listing of “required” SPNs for a given computer’s instance of SQL Server. The cmdlet will discover all instances of SQL Server on a given computer name. For each instance found, the cmdlet will generate a list of required SPNs based on active TCP/IP ports. The cmdlet will also warn you if an instance is using dynamic ports. Each returned SPN object will also specify if the SPN is currently set or not.
You can even check your entire estate in just one line. Here, you can see us testing several instances in our lab.
Or use Test to help easily Set all required SPNs
Connects to Active Directory and sets a given SPN to a given account. Will also set constrained delegation to the account to the recently added SPN.
Both Set and Remove fully support -WhatIf
Connects to Active Directory and removes a given SPN to a given account. Will also remove the associated constrained delegation.
The following screenshot shows a way to easily remove SPNS of decommissioned servers.
We also have a few other commands that can help you deal with SPNs.
- Repair-DbaInstanceName and Repair-DbaServerName
When a SQL Server’s host OS is renamed, the SQL Server should be as well. This helps with Availability Groups and Kerberos. Repair-DbaInstanceName helps determine if your OS and SQL Server names match, and thus, if a rename is required. If a rename is required, run Repair-DbaServerName.
This command returns the transport protocol and authentication scheme of the connection. This is useful to determine if your connection is using Kerberos. By default, the ConnectName, ServerName, Transport and AuthScheme of the current connection will be returned.
These commands are just the start of our support for SPNs. Right now, they’re just focused on SQL-related services on your computers. In the future, we’re going to extend these scripts to look at ALL services on your computers and tie them back to service accounts and SPNs. This includes SSAS, SSIS, and SSRS SPN support. We’re also going to keep looking at the commands and adding support for anything we didn’t think of when we released, which is why it’s important that you give us feedback (more on that below).
Thanks for reading, and I hope you enjoy these new commands! If you have any issues or questions about their use, feel free to drop by our Slack channel, #dbatools, and let us know. We’d love to hear from you!
11 thoughts on “new release – schwifty – all about SPNs”
wow… why have i been missing you ??!!
Very well done,,, so it seems, so far,, i should have been more time-resident here than in other scatter-minded places…
Every get in the wrong checkout lane, then start wondering if you should switch over to another lane that ” appears” to be moving faster …. ??…..
Pingback: Active Directory Authentication with SQL Server on Ubuntu – Port 1433
Pingback: Active Directory Authentication with SQL Server on CentOS – Port 1433
Pingback: The mountaintop: A PASS Summit 48 Hour Retrospective – Port 1433
Very nice tool. One thing I would recommend is to check if any instances discovered are actually online. I’m in the middle of retiring a number of instances. The services are stopped and disabled, but they report back as SPN Missing in the tool. If an instance is not running, should it have an SPN? 🙂
Very helpfull tool as dbatools generally.
You explain that next version will manage SSIS SPN.
Is it already done ?
And if not, do you now which prefix to use with SSIS ? (for SQL db engine it’s MSSQLSvc/).
This just saved my butt, you legends 😉
Would be nice, however version 1.0.29, when ever I run any of the SPN tools I always get “AD lookup failure. This may be because the domain cannot be resolved for the SQL Server service account (BXXXXC\SRVC_SSE_XXXX_XXX)”, it even fails to find the service account under the SAM name “Searching for SRVC_SSE_XXXX_XXX under domain BXXXXX in SamAccountName format” I have checked the account and yes it’s SAM name is exactly spelled as stated in the text statement. How can I find out if it’s a possible permissions issue, I have enabled the verbose logging and unable to identify anything there.
Hey Gregory, please file an issue at dbatools.io/issues so that we can have more insight into the problem.
Hi CHRISSY LEMAIRE , How can we get the list of server to verify the spn?
Where we can get the $servers detail, can you please explain it.
Glad to see someone else in the DBA community is a Rick & Morty fan.