July 30, 2015 at 7:19 pm
Hi,
I'm not entirely sure how to describe this. I have databases on two servers that I use Management Studio to connect to from my own PC using OS authentication. I can query the databases like this no problem.
However when I try to query databases on both servers at the same time (which should work through linked servers) I get an error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
If I actually log into one of the servers directly, then run SSMS and query from there, the query works as expected using the same login details as before - no "Login failed..." error message. It seems like somehow it knows when I'm on a different PC using SSMS.
Is there some kind of permissions I need to set up on my database servers that allow linked servers from other machines?
Cheers,
Mike.
July 31, 2015 at 3:48 am
hi,
is this at home or at work?
do you use a windows account for your sql or the default?
Normally for this, both PC and server need to be in the same domain and you need to have a SPN registered for both sql servers (the account under which it runs) and you need to allow delegation in adsiedit.msc. (allowedToDelegateTo and Service principal Name). than this should work perfectly.
if at home, you can add the NT AUTHORITY\ANONYMOUS LOGIN security principal or use sql authentication
July 31, 2015 at 4:02 am
Hi, thanks for your reply.
This is at work, I connect to my PC and servers (Windows) and databases using windows domain authentication, everything in the same domain. If I am on my PC and connect to the databases I can't see one server when connected to the other. If I am on the server I can see the databases on the other server. On our old servers this worked fine, but the DBA who set it all up left and now on new servers we can't get it set up.
Cheers,
Mike.
July 31, 2015 at 4:09 am
hi,
this is a part of the document I made for internal use in my company, need for both instances.
Open ADSI Edit
Find the good username under which SQL server runs.
Properties
Locate the ServicePrincipalName property
Press edit, validate the list.
Use this SPN as template:
MSSQLSvc/server.domain:1433
Set the good server name, example:
MSSQLSvc/server.domain:1433
Fill in and press add
See if it is added to the list.
Press OK
Find the allowedToDelegateTo
Press edit.
Add the same SPN
Press Add.
When done, press Ok, then Apply.
PS, verify if the old one didn't use SQL authentication, if it is used of course.
August 2, 2015 at 4:33 pm
Thanks, I will pass that info on to my IT department and see how we get on.
Mike.
October 4, 2015 at 4:07 pm
Sorry I didn't come back earlier - I passed the info to my IT department and they got it all working. Thanks heaps 🙂
Cheers,
Mike.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply