0

I am having trouble with a Linked Server.

I would typically not use a linked server, but legacy issues are forcing my hand.

I am attempting to set up a linked server between two new production SQL servers. I have configured SQL on both machines with domain service accounts running MSSQLSvc on each respective SQL instance.

The domain service accounts have SPNs registered; I have verified this through AD attribute editor(sp?). I can perform single hop queries on the actual servers, but no luck with the double hop from my laptop (domain admin).

I have used the Kerberos Config tool on each server and configured what I can.

This is where google falls short on this issue. When I use the Kerberos Config Tool on one of the servers I get this for my service account running MSSQLsvc:

TCP must be enabled to use Kerberos authentication.

In the SQL server config manager, I have TCP/IP enabled.

I have restarted services and the server on several occasions.

Any thoughts? I can provide more information.

1

Verify that both SQL Servers can use Kerberos

  1. connect from your laptop to SQLSERVER1 using windows auth.

  2. Run this query: select auth_scheme from sys.dm_exec_connections where session_id = @@spid It should return "Kerberos".

  3. If it returns "NTLM":

  4. Verify no duplicate spn's (SETSPN -X)
  5. Verify correct spn's (SETSPN -L)
  6. Repeat for SQLSERVER2

Once you can connect to both servers using Kerberos, then you need to ensure that SQLSERVER1 is trusted to delegate to SQLSERVER2. This is set in AD on the service account.

  • Thank you for the response. I am seeing NTLM on SQLServer1. For step 4 I don't get any duplicates. For step 5 I don't see a MSSQLSvc spn.. I added one and it is still not showing. I do have MSOLAPsvc.3 spns however. – PremiumUnleaded Feb 23 '18 at 18:50
  • Can you clarify that you added one and it's still not showing? maybe post the setspn command you used to add it? – StrayCatDBA Feb 23 '18 at 19:18
  • I was using "setspn -L Hostname" and not getting the MSSQLSvc. It does show under "setspn -L domain\svcaccount". I am still seeing NTLM after a restart of the service on SQLServer1 – PremiumUnleaded Feb 23 '18 at 19:22
  • That is correct, setspn should show the spn under the domain account it's running under. Also, just to clarify, is the svc account a regular domain account, or a managed service account? – StrayCatDBA Feb 23 '18 at 20:54
  • I am not very skilled with AD. I don't know the difference between the two account types. The service accounts are in our Service Account OU. How would I tell the difference? – PremiumUnleaded Feb 23 '18 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.