Post reply

Login failed for user \.

  • January 29, 2004 at 9:13 am

    #160734

    I am trying to execute the following SQL Statement from MyServer...

    ---------------------------------------------------------------------------

    DECLARE @Name

    SET @Name = SUSER_SNAME()

    --The @Name = 'Domain1\MyUserID'

    EXEC sp_AddLinkedServer 'YourServer', 'SQL Server'

    EXEC sp_addlinkedsrvlogin @rmtservername = 'YourServer', @userself = 'TRUE'

    SELECT * FROM YourServer.YourDB.DBO.YourTable

    EXEC sp_droplinkedsrvlogin @rmtservername = 'YourServer', locallogin = @Name

    ---------------------------------------------------------------------------

    I am added as a user on YourServer and YourDatabase

    On the Select Statement an Error Occurs

    Server: Msg 18456, Level 14, State 1, Line 1

    Login failed for user '\'.

    Does anyone know how to solve this.  It seems obvious that it has something to do with the Domain delimiter of my username.

     

  • January 29, 2004 at 10:11 am

    #492199

    use SET QUOTED_IDENTIFIERS ON/OFF and try.

  • January 29, 2004 at 11:00 am

    #492215

    Are you executing the query from Query Analyzer from say a client machine?

     

    K. Brian Kelley
    @kbriankelley

  • January 29, 2004 at 11:13 am

    #492221

    Are both servers in same domain?

  • January 29, 2004 at 11:21 am

    #492223

    Is this a typo?

    DECLARE @Name

    Or did you actually declare @Name as some datatype (ie. VARCHAR(20))?

    -SQLBill

  • January 29, 2004 at 11:31 am

    #492225

    It is being executed from a client machine

    It is a type-o

    and I'll try the QUOTED_IDENTIFIERS

    Thanks

  • January 29, 2004 at 11:41 am

    #492230

    If it's being executed from a client machine and both the connection from the client machine to the first SQL Server and the connection from the first SQL Server to the second SQL Server are using Windows authentication, you have a potential "double hop" issue. Is this the case? Windows auth on both connections?

     

    K. Brian Kelley
    @kbriankelley

  • January 29, 2004 at 12:35 pm

    #492245

    I am connected to MyServer via SQL Query Analyzer and Windows Authentication.  I am not connected to YourServer except via a Linked Server from within MyServer.  As stated above I used @UseSelf = 'TRUE'.  Does this try to used Windows Authentication?  How does this result in the error 'Login failed for user \.'?

  • January 29, 2004 at 1:09 pm

    #492249

    Yes, if @UseSelf = 'TRUE' and you're connecting to the SQL Server with Windows authentication, it will attempt to make the connection to the linked server using the same credentials, meaning Windows authentication. This is a double-hop issue.

    NTLM, the authentication mechanism under NT 4.0 domains, prohibits re-using the credentials in this manner. Active Directory allows for this using what is known as delegation. However, the account in question has to be set up for delegation and the SQL Server you connect to has to be set up to delegate as well. This occurs within the Active Directory administration side.

    Failing that, you'll have to connect on one of the hops with Windows authentication. The article I wrote on Query Analyzer (Part I) goes into this in a bit of detail. There are also some MS Knowledge Base articles on it.

     

    K. Brian Kelley
    @kbriankelley

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply