April 20, 2017 at 4:24 pm
I have two servers that I am trying to link through a linked server connection. Server #1 is SQL Server 2008 R2 and Server #2 is SQL Server 2008. I have attempted to make the connection through Management Studio and also by using T SQL commands. The results are the same either way. I have made sure that the login I am using is on both servers with the appropriate privileges and that the SPN is registered. I have many other servers where I have successfully linked servers in the past with no issues. But in this case I consistently get this error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
Everything I know about this points to an SPN problem but I have worked with the system admins and we are quite certain that everything is set properly.
Using Management Studio on the General page I set the server type as SQL Server. On the Security page I chose "Be made using the login's current security context." And on the Server Options page I set RPC and RPC Out to "True". Server#1 (2008R2) is up to date on service packs, but Server #2 (2008) is only on SP3. Could this be the issue? I have plans to apply SP4 but since it is a production server I need to jump through a bunch of hoops first.
April 20, 2017 at 6:44 pm
Doesn't look like credentials are being passed though. How did you verify the SPN was registered - did you use SetSPN -L to check? Did you check for duplicates? Is the service account trusted for delegation? You could also check the Kerberos configuration using this tool:
Microsoft Kerberos Configuration Manager for SQL Server
Sue
April 20, 2017 at 9:14 pm
Are you logging in with a SQL Server login or are you using Windows Authentication? If you are using a SQL Server login (which would throw the error you're seeing when attempting to forward credentials), then you must configure the linked server to impersonate the remote login you created (on the security tab of the Linked Server properties). SQL Server logins only have the context of the instance on which the login is defined.
Eddie Wuerch
MCM: SQL
April 21, 2017 at 8:49 am
dobbie - Thursday, April 20, 2017 4:24 PMI have two servers that I am trying to link through a linked server connection. Server #1 is SQL Server 2008 R2 and Server #2 is SQL Server 2008. I have attempted to make the connection through Management Studio and also by using T SQL commands. The results are the same either way. I have made sure that the login I am using is on both servers with the appropriate privileges and that the SPN is registered. I have many other servers where I have successfully linked servers in the past with no issues. But in this case I consistently get this error Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
Everything I know about this points to an SPN problem but I have worked with the system admins and we are quite certain that everything is set properly.
Using Management Studio on the General page I set the server type as SQL Server. On the Security page I chose "Be made using the login's current security context." And on the Server Options page I set RPC and RPC Out to "True". Server#1 (2008R2) is up to date on service packs, but Server #2 (2008) is only on SP3. Could this be the issue? I have plans to apply SP4 but since it is a production server I need to jump through a bunch of hoops first.Are the sql instances on both the sides running under a domain account?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply