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]
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)