Recently, I set up a SQL 2014 development system for a new vendor database. Initially, I gave the developers sysadmin permission to perform the install. When the install was complete, I reviewed the setup, and saw that a new SQL login had been added for the application that had been granted sysadmin permission.
I removed the sysadmin permission from both the Windows Group that the developers were using to log in to the server and to the SQL login that had been added for the application.
And, I got calls that neither the developers nor the application could log in to the server. For the Windows Group, the following error was logged in the SQL Event Log:
Error: 18456, Severity: 14, State: 11
Login failed for user ‘Domain\DevGroup’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
For the SQL login, this error was logged:
Error: 18456, Severity: 14, State: 12
Login failed for user ‘ApplicationUser’. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors.
I googled the errors, but the information that I turned up indicated that deleting the login and re-creating it would solve the problem. It did not.
Granting the following permissions explicitly did fix the issue:
GRANT CONNECT SQL TO [ApplicationUser]; GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO "ApplicationUser";
As of SQL 2014, Microsoft no longer grants CONNECT ON ENDPOINT permission to the public role as it did in previous versions. This improves the default security of an instance, but as soon as I removed sysadmin permission, the users couldn’t connect to the instance at all.