SQL Server - User only connects to the instance with sysadmin permission - Login failed for user 'teste'. Reason: Login-based server access validation failed with an infrastructure error

Views: 290
Reading Time: 3 minutes

Hey guys!
In this post, I would like to share an error that was reported in a Whatsapp group and that I personally had never seen before, which was the error message below and the report that users who used SQL authentication were only able to connect to the instance if they had sysadmin privileges.

Login failed for user 'test'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors

Right away, I already asked him to check if the instance was allowing connections coming from SQL Server authentication, since the default is to be enabled to accept only connections with Windows authentication (AD):

Result:

First validation was performed successfully. Another point that can cause this type of error are the Logon triggers. I asked him to check if there were any logon triggers on the instance and, if so, disable the triggers to test if this was what he was blocking:

Result:

I disabled the trigger and there was nothing preventing the login. Let's look in the SQL Server log to see if we have any clue to the problem:

The error message is very clear. My next suspect now is the permissions on the instance's endpoints. I will analyze how the permissions of the Endpoints are:

Result:

Problem encountered! The role public is not allowed to access the endpoints. Someone has probably made some modification to the default permission and users with SQL authentication are not accessing endpoints. To resolve this, we will grant these permissions:

Result:

Now copy these Grant commands and apply to your instance to release CONNECT permission for the role public on all endpoints.

After that, users with SQL Server authentication were able to connect normally to the instance 🙂

1 Note: Removing the CONNECT permission on the role public endpoints is not “wrong”. The DBA just needs to think of ways to release this permission to users or SQL authentication groups, so that they can continue to access the environment without permission from sysadmin.

2 Note: This type of problem can happen with both Windows authentication and SQL authentication users.

I hope you enjoyed this case of error resolved from a Whatsapp group, and if one day it happens to you too, I hope this article helps 🙂
A big hug and until next time!