I have a fresh database I've setup, which I can connect to and perform operations like normal via the SQL Server Management Studio. I have two accounts I created for my application to use, and both can log in via SSMS. My program is running java and using the 'mssql-jdbc-6.4.0.jre8.jar' driver, and when I attempt to connect I can see the following error on the database log:
Error: 18456, Severity: 14, Sate: 149
Login failed for user 'application_read_only_user'. Reason:
Login-based server access validation failed with an infrastructure
error. Login lacks connect endpoint permission. [CLIENT: 127.0.0.1]
I have looked at several sources trying to solve this issue. The official Microsoft documentation does not seem to list state 149, nor do any results I've found online.
I have not modified my endpoints from the original settings, so I only have the following items:
- Dedicated Admin Connection
- TSQL Local Machine
- TSQL Named Pipes
- TSQL Default TCP
- TSQL Default VIA
I have tried setting the SQL Server services to run as Local System.
There are a lot of similar questions on this site, and stack overflow, but none of them have been able to help. They all either have a different state, or do not list a state.
Best Answer
Analysis
If you have a search for the Error 18456 you will eventually find the following blog post from Aaron Bertrand:
Which lists the following for
states 146...149
:The this post link in the above quote references the article "Why do I get the infrastructure error for login failures?" over on CSS SQL Server Engineers blog/site.
The article cites these two possible reasons as the cause for the "Login-based server access validation failed with an infrastructure error":
Because SQL Server 2016+ has new error messages for Level 146...149 errors, you have been presented with the specific error message Login lacks connect endpoint permission. (Level 149), which leads you to the second option from above.
Have a look at your current enpoint permissions with the following script:
You should have at least one entry for the combination of
GRANT
,public
andTSQL Default TCP
Solution
Check the CONNECT permissions on the ENDPOINT for the SQL Server Login for TCP or simply grant the permission to the account:
(replace
public
with your<SQL Server Login>
if you only want to assign that permission to a specific account)To see a list of endpoints run:
Further Reading / Reference List