locked
SQL authentication fails outside of the Server itself RRS feed

  • Question

  • We have a SQL server named instance which was installed using mixed mode authentication, TCP/IP and browser services are running.

    Issue:- SQL authentication succeeds when logged into the server actually. But when I access SSMS from my own computer, the SQL authentication fails. (Windows are not from either location). I checked SA and its working on server and outside of the server but other SQL logins are not working outside of the SQL server.

    Eventviewer:- Login failed for user ''. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors.

    Experts, please help resolving this. Thank you.


    • Edited by Database Junior Wednesday, July 6, 2016 3:54 PM Added eventviewer message
    Wednesday, July 6, 2016 3:49 PM

Answers

  • This is because someone has revoked permissions on the remote endpoints.

    Run this query:
    SELECT * FROM sys.server_permissions WHERE class = 105

    Normally you should see rows, all granting CONNECT to public on the four default endpoints.

    The remedy is this:

    GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public

    There are some possible variations on this, not the least if SCCM is in the mix, but let's try this first.

    Wednesday, July 6, 2016 9:27 PM
  • States 11 & 12 mean that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login.

    Reference


    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    Thursday, July 7, 2016 7:59 AM

All replies

  • Have you run SSMS as Administrator? It's seems to be a UAC related issue
    Wednesday, July 6, 2016 4:09 PM
  • Already tried "run as admin" but no luck.

    Also verified "Allow remote connections" are checked under SQL Server properties. Also remote access is configured to 1 too, which is default value.

    Thanks.

    Wednesday, July 6, 2016 4:15 PM
  • Tom, thank you for the link but still could not figure out what needs to be done to resolve this? Does it mean the Windows team has to remove SCOM which I could not find on the server itself? Thanks.
    Wednesday, July 6, 2016 6:15 PM
  • This is because someone has revoked permissions on the remote endpoints.

    Run this query:
    SELECT * FROM sys.server_permissions WHERE class = 105

    Normally you should see rows, all granting CONNECT to public on the four default endpoints.

    The remedy is this:

    GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public

    There are some possible variations on this, not the least if SCCM is in the mix, but let's try this first.

    Wednesday, July 6, 2016 9:27 PM
  • States 11 & 12 mean that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login.

    Reference


    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    Thursday, July 7, 2016 7:59 AM
  • States 11 & 12 mean that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login.

    This is a possible explanation when error occurs with a Windows login. However, the subject for the thread talks about SQL authentication. And in this case, my experience is that it always is a problem with missing CONNECT permissions, either to the server itself or on the endpoint. Obviously, this could also be the reason for this error when you get it with a Windows login as well.

    Thursday, July 7, 2016 8:16 AM
  • Someone thought they were smart and would revoke permissions from "public" to lock down the server.

    Run this and see if it fixes your issue:

    GRANT VIEW ANY DATABASE TO public
     GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO public
     GRANT CONNECT ON ENDPOINT::[TSQL Named Pipes] TO public
     GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public
     GRANT CONNECT ON ENDPOINT::[TSQL Default VIA] TO public
    

    Thursday, July 7, 2016 12:04 PM
  • GRANT VIEW ANY DATABASE TO public
     GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO public
     GRANT CONNECT ON ENDPOINT::[TSQL Named Pipes] TO public
     GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public
     GRANT CONNECT ON ENDPOINT::[TSQL Default VIA] TO public

    Note that the first command in this list has nothing to do with the login problems. This is a permission which is present by default, but if it has been revoked it was probably for a reason.

    Thursday, July 7, 2016 9:15 PM