Link Server Error: Login failed for user 'Domain\user'. (Microsoft SQL Server, Error: 18456) RRS feed

  • Question




    I am trying to create a linked server between 2 SQL Server 2005 boxes.

    When I create the linked server I specify a login for the connection.

    The strange part is that I get the following error when it goes to test the connection:


    The test connection to the linked server failed

    Login failed for user 'Domain\user'. (Microsoft SQL Server, Error: 18456)



    I can use the SQL mangement studio to conenct to the database using this same login.

    The login has all the server roles assigned to it also.


    Can anyone shed some light as to what may be the problem?






    Thursday, October 18, 2007 6:44 PM


All replies


    Well you cannot provide a WIndows user and a passwort, you can redirect a Windows login to a SQL Server login and password. That is what the SQL Server login credentials are for. Otherwise you will have to use the impersonation for connecting to the server.

    Jens K. Suessmeyer


    Thursday, October 18, 2007 9:05 PM
  • What you are saying does not make sense to me.

    Which probably means that I don’t understand what you are explaining to me.


    I have a domain login Domain\user


    I start SQL Management Studio and connect to the database using this Domain\user (Windows Authentication) and I can access the database tables, etc. My domain\user is listed in the Security at the Instance level and at the Database level.


    Now I connect to another database and set up a linked server to the first database. On the security page I select the “be made using this security context” radio button and enter in the Domain\user for my remote login and my password.


    What you are saying is that I cannot use my Domain\user account but I would have to change my database from Windows Authentication only to SQL Server and Windows Authentication and then create a SQL login with the same permissions that I have setup for my domain/user account.


    So what I am not understanding is that if I can log into the database using Domain\user why can’t I use this same login for the connection to the database via the linked server?


    Friday, October 19, 2007 11:32 AM
  • Have a look at this blog posting by Nan Tu of the SQL Server Protocols team.


    Thursday, October 25, 2007 12:31 PM
  • This seems like the problem but I could not correct it in the time available to me so I jsut created a SQL Login to work around the problem for now.


    Thursday, October 25, 2007 12:43 PM