I have a new SQL Server 2019 system that I set up. After migrating applications from the old server to the new server, I started getting reports where the AD users could not connect to the SQL instance. They received a generic message that the “Login failed”. Looking at the Event Viewer log info, I received a more detailed message as can be seen below:
There were many hits for this error on the Internet, but none of them really helped me. “Login lacks connect endpoint permission” is the root cause of the problem. How do I get past this error?
After reading information I found, and educating myself on how SQL Server endpoints worked, I felt that I was at a dead end. SQL Server ships with 4 endpoints out of the box and I had not denied any endpoints to anyone, which is the most common reason one might encounter this error. Just to make sure, I granted CONNECT on the default TCP endpoint to PUBLIC:
grant CONNECT ON ENDPOINT::[TSQL Default TCP] to public;
That did not fix my problem. I even tried granting CONNECT on that endpoint to a specific user and still received the same error. Nothing is denying access to this endpoint and all users should be able to use it so why am I getting the error?
When poking around in SSMS, I could see the default TCP endpoint. I also found another endpoint named ConfigMgrEndpoint.
Where did that endpoint come from? A quick Google search revealed that ConfigMgrEndpoint is created to support Microsoft’s SCCM product. Sure enough, this SQL Server will hold our SCCM repository. Now I didn’t create this endpoint but I bet that when we fired up the SCCM wizard after we moved the SCCM repository database, the wizard created the endpoint for me. To see how this endpoint was created, I right clicked on the endpoint can selected Script Endpoint As –> CREATE To –> New Window. I then saw the code and immediately spotted my problem:
CREATE ENDPOINT [ConfigMgrEndpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 1433, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = ENABLED
, MESSAGE_FORWARD_SIZE = 5
, AUTHENTICATION = CERTIFICATE [ConfigMgrEndpointCert]
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
My problem is that this endpoint was created on port 1433, which is also the port for the default TCP endpoint. So two endpoints on the same port. The root cause of this problem was that the wizard that created this endpoint did not grant CONNECT on the endpoint to anyone. I was able to solve my problem by simply issuing this command:
grant CONNECT ON ENDPOINT::[ConfigMgrEndpoint] to public;
My AD accounts could now connect to the instance!
