September 15, 2017 at 5:06 am
I have 2 SQL Server 2014 databases on 2 servers
DB1 = auth_scheme KERBEROS
DB2 = auth_scheme NTLM
I am trying to set up a linked server between the two but keep getting the error
ogin failed for user NT AUTHORITY\ANONYMOUS LOGON MS Sql Server Error: 18456)
The user is a sysadmin on both servers and a db owner on both databases (windows authenticated user)
Help please ..... do both DB have to be KERBEROS
September 15, 2017 at 5:56 am
You've hit the triple hop problem.
You need to ensure that the two machines have SPN's registered and the service accounts are trusted for delegation.
That way the Kerberos tickets can flow between the computers in question and pass through the users credentials instead of defaulting to anonymous.
September 15, 2017 at 6:10 am
prestowd-sql - Friday, September 15, 2017 5:06 AMI have 2 SQL Server 2014 databases on 2 serversDB1 = auth_scheme KERBEROS
DB2 = auth_scheme NTLMI am trying to set up a linked server between the two but keep getting the error
ogin failed for user NT AUTHORITY\ANONYMOUS LOGON MS Sql Server Error: 18456)The user is a sysadmin on both servers and a db owner on both databases (windows authenticated user)
Help please ..... do both DB have to be KERBEROS
Yes both need to be using Kerberos. Try using this Microsoft tool to help fine the issues:
Microsoft Kerberos Configuration Manager for SQL Server
Sue
September 15, 2017 at 7:16 am
Thanks for the replies. I'm a complete newbie when it comes to this ...
Having researched I see that the instruction is to add delegation permissions (trusting to any service (Kerberos only) to the server but then in another post it says that whilst that is the easy way it is not very secure.
I had an environment on SQL 2008 and windows 2008 that worked fine (3rd party databases)- both Kerberos but they don't appear to have any delegation set when I look at the properties of that server.
Is there another way of setting this up?
September 15, 2017 at 7:38 am
prestowd-sql - Friday, September 15, 2017 7:16 AMThanks for the replies. I'm a complete newbie when it comes to this ...
Having researched I see that the instruction is to add delegation permissions (trusting to any service (Kerberos only) to the server but then in another post it says that whilst that is the easy way it is not very secure.
I had an environment on SQL 2008 and windows 2008 that worked fine (3rd party databases)- both Kerberos but they don't appear to have any delegation set when I look at the properties of that server.
Is there another way of setting this up?
On your 2008 environment, the service accounts probably created their own SPN. The accounts will try to do that on startup of SQL Server.
If you go through this article, it explains the process and walks you through some of it:
Register a Service Principal Name for Kerberos Connections
I think the easiest method though is do download the Kerberos Configuration Manager for SQL Server in the link in my first post and use that.
Sue
September 15, 2017 at 8:07 am
Thanks. I'm looking at the DB1 which is KERBEROS . In the SQL Server Configuration Manager
the SQL Server (MSSQLSERVER) has Log On As set to NT Service\MSSQLSERVER
where would I find that log on account so I can look at properties and permissions - I've looked in Active Directory but can see anything that matches there ....
hanks for the help so far
September 15, 2017 at 8:42 am
prestowd-sql - Friday, September 15, 2017 8:07 AMThanks. I'm looking at the DB1 which is KERBEROS . In the SQL Server Configuration Manager
the SQL Server (MSSQLSERVER) has Log On As set to NT Service\MSSQLSERVERwhere would I find that log on account so I can look at properties and permissions - I've looked in Active Directory but can see anything that matches there ....
hanks for the help so far
Looks like you are using one of the local built in accounts (local system, network service, etc). You'll need to check the computer/server object itself.
Sue
September 15, 2017 at 10:07 am
Adding to what Sue_H said, I don't think you can nicely set up a local account (such as NT Service\MSSQLSERVER) with KERBEROS without using delegation. I think in that case you need to allow the computer for delegation instead of an account.
The recommended SQL Server service setup is to use an AD account to run the service as. Making a change like this does require service restart though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply