Login lacks connect endpoint permission in SQL Server - Error 18456, State: 149

Question

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.

Answer 1

Analysis

If you have a search for the Error 18456 you will eventually find the following blog post from Aaron Bertrand:

• Troubleshooting Error 18456 (SQL Blog / Aaron)

Which lists the following for states 146...149:

These states replace states 11 and 12 above, but only in SQL Server 2016 or better. The goal was to make the actual underlying issue easier for the sysadmin to diagnose between SQL auth and Windows auth logins, and between connect and endpoint permissions (all without giving any further info to the user trying to log in). For more details, see the latter part of this post.

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":

1. Does the login have the SERVER class permission named CONNECT SQL for this server instance?

2. Does the login have the ENDPOINT class permission named CONNECT for the specific endpoint on which the client application established the connection?

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:

SELECT * FROM sys.server_permissions AS sp2 
    JOIN sys.server_principals AS sp
        ON sp2.grantee_principal_id = sp.principal_id
    LEFT OUTER JOIN sys.endpoints AS e
        on sp2.major_id = e.endpoint_id
WHERE sp2.permission_name = 'CONNECT' 
AND sp2.class_desc = 'ENDPOINT'

You should have at least one entry for the combination of GRANT, public and TSQL 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:

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public;

(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:

select * from sys.endpoints

Further Reading / Reference List

• Troubleshooting Error 18456 (SQL Blog / Aaron)
• Why do I get the infrastructure error for login failures? (CSS SQL Server Engineers)
• Endpoints Catalog Views (Transact-SQL) (Microsoft Docs)
• GRANT Endpoint Permissions (Transact-SQL) (Microsoft Docs)

Answer 2

I've got the same problem because I was trying to connect to port 1434 instead of port 1433. mssql-server is listening to both. I can connect to 1434 and use SA account without problem but that did not work for other accounts.

Answer 3

I eventually managed to solve this issue. By default, after installation, the TCP/IP connection had been disabled. I needed to open up 'SQL Server Configuration Manager', go to the network configuration section and enable TCP/IP.

Under the 'IP Addresses' menu on TCP/IP I also needed to make sure the IP4 option was active, and the IPAll port matched what I expected the server to be listening under.

hot2use's answer had a lot of potentially useful options for figuring out the issue, but in the end none of them were able to diagnose my exact problem. All results returned from querying the server itself said everything was working correctly, it wasn't until I looked at this tool that I found some settings were not as I expected.