SPN’s for Linked Servers

SPN’s for Linked Servers

There’s lots of problems with linked servers and lots of reasons to not use it, but let’s assume you want to join 2 servers together, how do you manage the security?

The main issue with the security is when you map a login on the source server to a destination server. When you do this, you provide anyone who can access the source server access to the linked server. Sure, you can lock it down with different logins in the top section there, but whenever I’ve seen this, people take the lazy option and choose “Be made using this security context:” and fill in an account that has quite privileged access on the linked server. Hopefully you can see the security issue here…

The option “Be made using the login’s current security context” solves some of this issue – the user that logs in to the primary server is the user that logs in to the linked server. They will get the same access that they would have got connecting directly to the linked server.

You’ll need to do your own research on the security risks, but I’m sure you can see it’s a better option.

To make this option work, you need to have the SPNs setup properly. SPNs are awesome when they work, and a nightmare when they don’t. You’ll know it’s not setup correctly in this instance if you get the following error:

“Login failed for user NT AUTHORITY\ANONYMOUS LOGON”

To make linked servers work using this security option, you need to make sure you have the following SPN specified for the account running SQL Server: MSSQLSvc/servername.domain

SetSPN -s "MSSQLSvc/Servername" "domain\account"
SetSPN -s "MSSQLSvc/Servername.domain" "domain\account"

Once you’ve done this, you should be good to go. If you’re still stuck, I’d recommend this fantastic article on SQL Shack: https://www.sqlshack.com/how-to-link-two-sql-server-instances-with-kerberos/