Kerberos and SPN issue

Hello,

I've created a new service account for some SQL services and it needs to be "Trusted for Delegation". I've copied the SPNs of the original SQL service account. I took all the SPNs I found over there but that did not work properly. I'm not too familiar with how SPNs are working therefore I'm not sure how to setup the account properly so it doesn't break connections in the network. For example, after creating that account, people were not able to use 'Linked Server' in SQL.


Sorry for the long message.


Thanks!
Alan DalaITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
To be completely honest.  I am a fan of configuring my SQL Server service accounts to have permissions delegated to the OU that contains my SQL Servers.  When starting up, SQL Server tries to automatically register its SPN, but very rarely do you see people that delegate the permissions for the OUs.  Kerberos is somewhat voodoo'ish in my humbled opinion.

Additionally, you hit the nail on the head with the fact that linked servers are weird...  They have what I like to refer to as the double hop problem with regards to authentication.  I honestly would warn you to be wary of linked servers.  Not only do they have the double hop problem, SQL Server can not introspect the stats on the linked server, so the execution plans are flat out evil little liars.

Here is information that should get you through what you need done, though:

Manually setting an SPN - https://technet.microsoft.com/en-us/library/ms191153.aspx#Manual (** note, you'll want to use setspn -s instead of setspn -a if you are windows server 2008 or higher as it checks for duplicate SPNs first)

General info on using setspn - https://technet.microsoft.com/en-us/library/cc731241(WS.10).aspx (** note, with great power comes great responsibility...  read this one fully before throwing around setspn commands)

Delegating permissions for automatic SPN registration - https://support.microsoft.com/en-us/kb/319723/ (** note, i believe this is not recommended with multiple DCs and failover clusters)

Linked servers are weird and have the double hop issue (how I like to refer to it).  Here are details on the delegation settings you alluded to - https://technet.microsoft.com/en-us/library/ms189580(v=sql.105).aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Will SzymkowskiSenior Solution ArchitectCommented:
All you should need to do for Services Accounts is make sure that it is a local admin (or whatever permissions are required locally) on the server where it needs to run the services and enter in the correct password.

Will.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Alan Dala , do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.