Sometimes while reproducing a situation results in another new error and I get an idea for a new blog. In this blog we would talk about an error Login-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission.
The earlier blog was about connecting to SQL Server using Dedicated Admin Connection (DAC) in SQL Server. When I tried connecting to SQL using SQLCMD to DAC, I received below error.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user ‘daclogin’..
Here is the screenshot.
Above is one of the most common error which doesn’t tell the cause of the issue. Whenever you get such error, you should always look at SQL Server ERRORLOG SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location
In ERRORLOG, I saw the following message:
Error: 18456, Severity: 14, State: 149.
Login failed for user ‘daclogin’. Reason: Login-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission. [CLIENT: 127.0.0.1]
WORKAROUND/SOLUTION
The key part of the error message was “Login lacks connect endpoint permission.”. I checked further and realized that this account “dacadmin” which I created was part of “public” role.
As soon as I gave him sysadmin, it was able to log in.
Have you encountered the same error in non-DAC connection also? What was the solution?
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
I have done that in my SQL Server, but it doesn’t work yet. Any idea? I have created the login for my computer account and given to it sysadmin access, but it did not log in. Thanks for any help.
And remember about ports in your connection: 1434 is for DAC (sysadmin). Public user with less permissions shall connect to default SQL instance on 1433 port.