Connection Error After Removing Public Permissions
One of the requirements for STIGging an instance of SQL Server (or applying other enterprise security standards) is to remove all permissions from SQL Server's public role and the CONNECT permission from the database guest accounts. Incidentally, SQL Server 2008 has far fewer permissions assigned to the public role than previous versions, but it still has some.
I'm often asked if removing those permissions will break anything. The obvious answer is that it will break anything that depends on them. Which leads to the question of what depends on them. I haven't found a list, but there is one issue that has come up that I haven't seen covered elsewhere, so I'm posting it here:
Removing all permissions from the pubic role and guest accounts will prevent non sysadmin accounts from connecting to SQL Server until those non sysadmin accounts are granted connection privileges.
The main symptom is that a connection attempt by a non sysadmin account will get a login error with Error 18456 and the message "Login failed for user 'username'". If you look at the client error details, it will show Severity: 14 and State: 1. There are a variety of conditions that will produce this error, and State:1 is always reported to the user to avoid disclosing information that might help a cracker break a login account. If you're the DBA, you can go into the SQL Server Error Log and get the real State, which for this scenario may be State: 12, and it will provide this error message: "Login failed for user 'username'. Reason: Token-based server access validation failed with an infrastructure error." If you're using a SQL login, it will say "Reason: Login-based server access validation failed with an infrastructure error."
This will happen despite having "Grant" selected on the Status page of the New Login dialog.
UPDATE: This may also produce Error: 18056, Severity: 20, State: 11, "The client was unable to reuse a session with SPID <#>, which had been reset for connection pooling." or "...cannot connect to the <databasename> database...".
The way to correct this problem is by granting connection privileges to the server and to specific endpoints for specific users or groups. So if I have a domain named Contoso and a group named Dev, and want to give that group the ability to connect via TCP/IP as non-sysadmin's, I would use these commands:
GRANT CONNECT SQL TO "Contoso\Dev"
GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO "Contoso\Dev"
If you want to see a list of the endpoints you have available, just query "SELECT * FROM sys.endpoints".
UPDATE 2: This may also produce Error: 18456, Severity: 14, State: 11, if you're attempting to connect with Named Pipes by default and you have disabled Named Pipes. All you have to do is change your connection method to TCP/IP. In SQL Server Management Studio's connection dialog, click the Options button, then choose TCP/IP in the Network protocol drop-down box.
UPDATE 3: Roy Richardson recently pointed out to me that after wiping out the CONNECT and VIEW ANY DATABASE persmissions from the public role, and adding individual CONNECT permissions, non-sa accounts won't be able to see database structures in SQL Server Management Studio, but they can still connect to databases and execute whatever commands they have permissions for.
Revisions: Minor changes were made to this post on 12Aug2011.