Post reply

Error: 18456, Severity: 14, State: 149, no matter what I try

  • March 30, 2023 at 7:49 pm

    #4167245

    Hello experts,

    I'm seeing a strange issue. A user reported that they could not log in with their AD account. When I checked the logs, I saw the following error:

    Error: 18456, Severity: 14, State: 149.

    Login-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission.

    I went through all the suggestions I could find on Google, StackExchange, etc. Such as

    • Check and grant CONNECT and CONNECT SQL for the endpoints
    • Confirm the server name
    • Try the IP address instead
    • Try a SQL login instead of a Windows login
    • Restart SQL services and try again
    • Grant the SQL login sysadmin temporarily

    Nothing has worked. I don't know if this is something wrong with the permissions, which it seems like it is saying. But if the login doesn't work with sysadmin permissions I have no idea what else to grant.

    Attached is a screenshot labeled as follows:

    1. Connect permissions for the AD account with the error
    2. Connect permissions for an AD that is not getting the error
    3. Connect permission for the SQL login I created to test

    As far as I can tell, all of those are identical.

    Has anyone run into this issue who has any further ideas?

    Thanks for any help!

    -- webrunner

    References:

    https://sqlblog.org/2020/07/28/troubleshooting-error-18456

    https://learn.microsoft.com/en-us/archive/blogs/psssql/why-do-i-get-the-infrastructure-error-for-login-failures

    • This topic was modified 1 year ago by  webrunner.
    • This topic was modified 1 year ago by  webrunner.

    Attachments:
    You must be logged in to view attached files.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • March 31, 2023 at 6:52 pm

    #4167703

    One more thing I found: The SQL login works when I remote to the SQL host and log in locally. So it is something that happens for external connections.

    Thanks again for any help.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • April 3, 2023 at 10:26 am

    #4168349

    Hi there webrunner,

    Thanks for posting lots of detail! Really helps, and I learnt something too whilst reading 🙂

    Assuming you have already checked the various endpoint permissions, and given the SQL Login works locally but not remotely... you didn't mention if the AD Login works remotely. My guess is, some policies have been applied to the OS, maybe something which is restricting Kerberos authentication; a local AD login with work via NTLM, whereas remote will be (should be!) Kerberos.

    Is there anything useful in the Windows OS Events, under the usual Security (do you have Advanced Audit Policy enabled?) and also:

    Applications and Services Logs > Microsoft > Windows >NTLM

    Let us know if you find anything,

    Andy

  • April 3, 2023 at 12:27 pm

    #4168389

    I've got no idea but ChatGPT suggested:

    Error 18456 is a common error in Microsoft SQL Server, indicating that a login attempt has failed due to invalid login credentials or insufficient permissions.

    State 149 indicates that the login attempt was unsuccessful because the account is locked out. This could be due to several failed login attempts, as a security measure to prevent brute force attacks.

    To resolve this issue, you can unlock the account using the following steps:

    1. Connect to the SQL Server instance using an account with administrative privileges.
    2. Open SQL Server Management Studio and navigate to the Security folder for the SQL Server instance.
    3. Expand the folder to view the login accounts, and find the account that is locked out.
    4. Right-click on the account and select Properties.
    5. In the Login Properties dialog box, go to the General tab.
    6. Clear the checkbox next to "Login is locked out", and click OK.

    Once the account is unlocked, the user should be able to login successfully. If the issue persists, you may need to check the login credentials or permissions for the account, or investigate further to determine the root cause of the lockout.

  • April 3, 2023 at 12:29 pm

    #4168391

    and yet:  "The SQL login works when I remote to the SQL host and log in locally"

    So probably not locked out.

  • April 3, 2023 at 12:38 pm

    #4168445

    Andy sql wrote:

    and yet:  "The SQL login works when I remote to the SQL host and log in locally"

    So probably not locked out.

    If it works when they remote to the host and connect locally, when doesn't it work?

  • April 3, 2023 at 1:44 pm

    #4168470

    Hi Andy, Jonathan,

    Thanks so much for your help. Sorry, just to clarify - I have not tested the AD login locally. That user doesn't have (i.e., is not allowed to have) permission to remote to the SQL Server, so I am not sure if we can test that. So I could check with the AD team to see if that AD account has any restrictions on it. But, yes, the main issue is that the AD login does not work remotely.

    The SQL login is the one that works locally but  not remotely. So far I have created two test SQL logins, and they both show the same behavior. Maybe that indicates something about how the server itself is set up regarding authentication. I also checked Kerberos Configuration Manager and it reported missing SPNs, so I have asked for those to be fixed. But I don't think that would affect SQL logins.

    Thanks again, will keep you posted.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • April 3, 2023 at 1:54 pm

    #4168483

    So it it the case that there is an AD account that you can remotely connect but the SQL login cannot remotely connect?

  • April 3, 2023 at 2:09 pm

    #4168499

    Great question, sorry I didn't provide this info earlier.

    So far, I can log in locally and remotely using my AD login. The colleague who reported the issue can also log in locally and remotely (the account throwing the login errors is for a person who works for him).

    Seems like I need to create a matrix or list of who can log in and who cannot, remotely and locally. I'll work on that and will let you know. Hopefully a pattern emerges.

    Thanks again.

    -- webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • April 3, 2023 at 9:30 pm

    #4168678

    Try enabling named pipes and shared memory protocols and give it a try.

    =======================================================================

  • April 4, 2023 at 8:40 pm

    #4169162

    Thanks to everyone for their help. The issue was resolved by another colleague who discovered that there was a custom endpoint set up. Once the permission to that custom connection was granted, the user was able to connect.

    I'll need to read up on custom endpoints - I did not know about them before.

    Thanks,

    Leroy

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 11 posts - 1 through 10 (of 10 total)

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