September 6, 2017 at 2:26 pm
I'm setting up a linked server connection from SQL Server 2014 instance "301" to SQL Server 2014 instance "300." I'm using all the default settings for the SQL Server provider. The instances reside on separate VM's but are on the same VLAN and there isn't a hop between them (that I'm aware of). Nonetheless, I've registered both VM's with SPN's and I'm still receiving the dreaded anonymous logon failure. Unfortunately, my suspicion is that this has something to do with both instance services running under a local computer account (not my call) instead of a domain-level service account. Can anyone confirm this? Or does anyone have an idea what might be wrong?
September 7, 2017 at 1:02 pm
TheGreenShepherd - Wednesday, September 6, 2017 2:26 PMI'm setting up a linked server connection from SQL Server 2014 instance "301" to SQL Server 2014 instance "300." I'm using all the default settings for the SQL Server provider. The instances reside on separate VM's but are on the same VLAN and there isn't a hop between them (that I'm aware of). Nonetheless, I've registered both VM's with SPN's and I'm still receiving the dreaded anonymous logon failure. Unfortunately, my suspicion is that this has something to do with both instance services running under a local computer account (not my call) instead of a domain-level service account. Can anyone confirm this? Or does anyone have an idea what might be wrong?
When creating the SPNs for the machine accounts, you also need to enable "trusted for delegation" for the machine account.
What is setup for the security mapping on the linked server? That would be another one to look at.
Sue
September 7, 2017 at 1:51 pm
Sue_H - Thursday, September 7, 2017 1:02 PMTheGreenShepherd - Wednesday, September 6, 2017 2:26 PMI'm setting up a linked server connection from SQL Server 2014 instance "301" to SQL Server 2014 instance "300." I'm using all the default settings for the SQL Server provider. The instances reside on separate VM's but are on the same VLAN and there isn't a hop between them (that I'm aware of). Nonetheless, I've registered both VM's with SPN's and I'm still receiving the dreaded anonymous logon failure. Unfortunately, my suspicion is that this has something to do with both instance services running under a local computer account (not my call) instead of a domain-level service account. Can anyone confirm this? Or does anyone have an idea what might be wrong?When creating the SPNs for the machine accounts, you also need to enable "trusted for delegation" for the machine account.
What is setup for the security mapping on the linked server? That would be another one to look at.Sue
Thanks. I'm inquiring re: trusted for delegation. As far as the security mapping goes, I've configured the linked server to use the security context of the user. In this case, that user has sysadmin privileges on the target server.
September 7, 2017 at 2:50 pm
TheGreenShepherd - Thursday, September 7, 2017 1:51 PMThanks. I'm inquiring re: trusted for delegation. As far as the security mapping goes, I've configured the linked server to use the security context of the user. In this case, that user has sysadmin privileges on the target server.
It probably is but it's worth checking. In theory, it should work with the local account if delegation is setup for the machine accounts. And if you have a linked server between two SQL Servers, you would need kerberos working. But kerberos is a bear to troubleshoot sometimes.
Have you ever used klist? It's the replacement for kerbtray so you can see the tickets and often find whatever the issues are. Had an issue once where it was due to the times not being in sync enough on the server. You can set a time difference in the policies. But it's things like that which can make Kerberos issues a nightmare. If you monitor the tickets, you can often find kerberos errrors and then look those errors up.
Here are a couple of articles which could help - they have weird little things that can help in troubleshooting:
How to set up a Kerberos Authentication Scenario with SQL Server Linked Servers
My Kerberos Checklist…
Sue
September 8, 2017 at 3:01 pm
Just to clarify what Sue mentioned the trust for delegation setting needs to be set on the account running the SQL Server service on the instance with the linked server. That delegation needs to point to the SPN registered on the account running SQL Server that is the target of the linked server. So if instance 301 and 300 are using a local system or virtual account to run SQL then in Active Directory the computer object for 301 needs to have an entry in delegation set pointing to the MSSQLSvc entry on the computer account for 300.
Additionally, I would check that neither account in Active Directory have the "Account is sensitive and cannot be delegated" option set. I believe this is only possible when using a user service account to run SQL, but I am not 100% positive on that.
Joie Andrew
"Since 1982"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply