Speak 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.
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @AuthenticationMode INT EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', @AuthenticationMode OUTPUT SELECT CASE @AuthenticationMode WHEN 1 THEN 'Windows Authentication' WHEN 2 THEN 'Windows and SQL Server Authentication' ELSE 'Unknown' END AS [Authentication Mode] |
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:
|
1 2 3 4 |
SELECT * FROM sys.server_triggers WHERE is_ms_shipped = 0 AND is_disabled = 0 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT sp2.[permission_name], e.state_desc, e.[name] AS endpoint_name, e.principal_id, sp.[sid], e.is_admin_endpoint, sp.is_disabled, sp.[name] AS granted_name, e.protocol_desc 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.class_desc = 'ENDPOINT' AND e.is_admin_endpoint = 0 |
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:
|
1 2 3 4 5 6 |
SELECT 'USE [master]; GRANT CONNECT ON ENDPOINT::[' + [name] COLLATE SQL_Latin1_General_CP1_CI_AI + '] TO [public];' AS GrantCmd FROM sys.endpoints WHERE is_admin_endpoint = 0 |
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 🙂
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!
Very good article!
I have a similar problem. On a SQL Server Standard machine I cannot connect to localhost, I have already made the necessary configurations in SQL Configuration Manager .. but without success .. Any tips?
Very interesting! Congratulations
Dirceu, Good morning!
Excellent article !!
I have a little bit of a problem. I cannot connect to a Sql Server Standard instance via localhost, I have already made the settings via SQL Server Manager but I was not successful .. have a tip?
Hello Dirceu, good morning.
Very cool this post, I had never witnessed this scenario myself.
I will keep the procedures you have adopted.
Hugs.