March 30, 2023 at 7:49 pm
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
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:
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
-------------------
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
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
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
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:
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
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 1:44 pm
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
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
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
Try enabling named pipes and shared memory protocols and give it a try.
=======================================================================
April 4, 2023 at 8:40 pm
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