locked
Unable to login from server itself RRS feed

  • Question

  • Hi guys

    One of our tech admins is able to connect to sql server via management studio using windows auth from his desktop, but if he tries the same thing from the server itself, he's unable to login.  I'm a little baffled about this as his windows account hasn't been granted login permissions in the first place.  Any ideas?

    Tks


    Steven

    Tuesday, June 17, 2014 7:57 PM

Answers

  • 7. The login error he gets from the server is Cannot connect to ServerName.  Additional Information: Login failed for domain\userName (Microsoft SQL Server, Error 18456)

    What does SQL Server errorlog have to say about this login failure? There should be a state number that indicates why he can't login.

    Also, when loggining locally, try specifying the server as tcp:127.0.0.1 Add instance name as usual, if this is a named instance.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Sofiya Li Friday, June 27, 2014 2:56 AM
    Wednesday, June 18, 2014 9:31 PM
  • Hello Steven,

    2. @@SERVERNAME is a function available from at least SQL Server 2000 up to 2014

    7. You get this error message when the current Windows Login don't have permissions to logon to SQL Server. When he logon to the Windows Server, is he using his domain account or maybe a local account?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Sofiya Li Friday, June 27, 2014 2:56 AM
    Thursday, June 19, 2014 6:22 AM

All replies

  • Does he have a SQL Alias defined on his machine ?

    What does unable to login mean, which error message does he get ?

    Maybe he is connecting to another instance. Can he do a SELECT @@ServerName to see to which server he is connecting ?

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Tuesday, June 17, 2014 8:15 PM
  • but if he tries the same thing from the server itself, he's unable to login.  

    Hello Steven,

    Which error message is he getting when he tries to logon? Is maybe the "Shared Memory" protocol disabled for SQL Server, which is used for local connections?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sofiya Li Wednesday, June 18, 2014 8:33 AM
    Wednesday, June 18, 2014 5:52 AM
  • Yes, and this is why I asked him to check in the case of the successful connection he is connected to the right server / instance and not simply connects to "a" server which is accessible for him.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Wednesday, June 18, 2014 5:55 AM
  • Hi  guys

    More details from the tech rep having the issue:

    1. He's using Management Studio 2008 to connect to SQL 2005 Server from his desktop
    2. SELECT @@ServerName doesn't appear to be a recognized command when issued from Management Studio 2005
    3. I'm able to see his connection in SQL Profiler from his desktop
    4. Using the same SQL Profiler session, I see nothing when he attempts to connect from the server
    5. Shared Memory is enabled
    6. I'm able to connect to the SQL instance from the server (though I'm added to the SQL Logins so it's not exactly the same case)
    7. The login error he gets from the server is Cannot connect to ServerName.  Additional Information: Login failed for domain\userName (Microsoft SQL Server, Error 18456)


    Steven

    Wednesday, June 18, 2014 2:06 PM
  • 7. The login error he gets from the server is Cannot connect to ServerName.  Additional Information: Login failed for domain\userName (Microsoft SQL Server, Error 18456)

    What does SQL Server errorlog have to say about this login failure? There should be a state number that indicates why he can't login.

    Also, when loggining locally, try specifying the server as tcp:127.0.0.1 Add instance name as usual, if this is a named instance.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Sofiya Li Friday, June 27, 2014 2:56 AM
    Wednesday, June 18, 2014 9:31 PM
  • Hello Steven,

    2. @@SERVERNAME is a function available from at least SQL Server 2000 up to 2014

    7. You get this error message when the current Windows Login don't have permissions to logon to SQL Server. When he logon to the Windows Server, is he using his domain account or maybe a local account?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Sofiya Li Friday, June 27, 2014 2:56 AM
    Thursday, June 19, 2014 6:22 AM