Hello, I have set up two fresh SQL Servers and have set up SPN, both servers report the following...
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/SERVERNAME ] for the SQL Server service.
I have set up a linked server between the pair of them and then from my desktop SSMS tried to test the connection and get the error.
Hi
@Anonymous
,
Is there any update on this case?
Please make sure SQL Server service account was trusted for delegation in AD.
You can go to domain controller -> open active directory users and computers -> users -> right-click the SQL Server Service account in users folder -> Properties.
Then go to delegation tab in the Properties dialog box, ensure that "Trust this user for delegation to any service (Kerberos only)" or "Trust this user for delegation to specified services (Kerberos only) – Use Kerberos only "is selected. If you choose the " Trust this user for delegation to specified services (Kerberos only)", please add the SQL Server service. ( please do the same for the delegation tab in the Properties of server's computer object in active directory users and computers.)
Then go to the account tab in properties and ensure that the "account is sensitive and cannot be delegated" option is not selected.
Please refer to
this article
which might help.
Best Regards,
Amelia
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our
documentation
to enable e-mail notifications if you want to receive the related email notification for this thread.
You need to make sure Kerberos works on both servers. If you can RDP into the server that set up the linked server and connect to the linked server fine, verify it is using Kerberos, you can check the auth_scheme from the sys.dm_exec_connections on the target server to verify it. For example,
select c.auth_scheme,* from sys.dm_exec_sessions s join sys.dm_exec_connections c on s.session_id=c.session_id where s.host_name ='yourhostname'.
Make sure it is Kerberos used by the connection.
Hi there,
what you describe is a classic double hop scenario.
local SSMS->Server A -lkd srv conn -> Server B
Just having an SPN for both servers is not enough.
The account running the sql server engine needs the information where to delegate to.
So, if you have a SQL Server A which runs under yourdomain\mysvcAccforServerA, than this account needs the right to delegate credentials to another SPN, eg MSSQLSvc/MyServerb.yourdomain
Regards
