Sql 2008 "Public Not Granted Server Permission" policy

Question

I recently started looking into the new Policy Management in SQL Server 2008 and when I checked for compliance with the "Public Not Granted Server Permission" policy, my server failed the check. The reason was, and I am pretty sure this is the default, public has TCP Endpoint permission so that users can connect to the server. If I were to remove this, I would have to assign users individually the right to connect.

This seems silly to me. I cannot figure out why Microsoft would recommend changing this. I have no guest account, so only specified users can access the server. The server is behind a firewall, and only one subnet in the company can even access it. Does anyone have any insight on this?

Answer 1

Security best practices start with locking EVERYTHING down, then only opening up pieces as necessary to the people that actually need to get to it.

By allowing the "public" access to even connect, regardless of the fact that they don't have rights to do anything, opens the door just a crack. Sometimes, that's enough to exploit a buffer overflow or another type of attack.

UPDATE

A Login and a User are two different things. A login is server level, a User is database specific. Every User should have a corresponding login; but not every login will have a database user account.

Let's say you are using Active Directory integrated security; and you've assigned a particular AD group to have access to the server. Now, only some of those within the group may actually have access to certain databases. Those are the people you want to allow access to connect. Whereas the other members of the group don't have regular database user accounts.

By definition this is a problem as some of the group members can connect and list databases on the server even though they don't have access to any of those databases. That's the "crack" in the door I'm talking about.

If you can stop those people from even being able to connect then you've gone a long way towards securing your server.

There are other server level permissions that the Public role grants to logins. If possible, you should turn it off just like you would a guest account.