Linked Servers connection issues

  • 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.

  • 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

  • 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.

  • 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.

  • Thanks, I will pass that info on to my IT department and see how we get on.

    Mike.

  • 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