I thought I had seen it all in SQL Server. But life finds a way of throwing a few surprises and WTF moments in your way. I happened to be working with a customer who had some Windows users and these users had complete access over SQL Server. They were able to login, access all databases & tables, create new databases, drop databases (yes!) etc. Now, these Windows logins weren’t a part of the SQL logins in sys.syslogins at all. How did they gain access? Where are they getting the complete equivalent of sysadmin permissions from?
Thus begins the story of the curious case of public logins and how Dr.Sherlock went about solving the mystery. Conan Doyle on…..
1. Check if this Windows login is part of sys.syslogins
2. Check if this Windows login is part of any Groups, that are part of the logins.
3. If so, check what permissions this group has on the instance/database and what roles these Groups belong to.
Okay, what this told me was that there were 2 Windows Groups added as logins to SQL Server, but this login was a part of only 1 group and this group AD\GroupXYZ did not have sysadmin [EXEC sys.sp_helpsrvrolemember ‘sysadmin’] or database access. ??@!#*@!#!@#!
Since my initial testing had revealed that with this login create/drop/backup etc. were possible, this login is obviously having some higher level privilege in SQL Server akin to what the SA would have. I had already checked sysadmin role and this login/group wasn’t a part of the role. There are 3 scenarios in SQL Server where logins have implied access to a database. By “implied” I mean that a login need not be mapped to any database, but this login can still have complete access to the database.
1. Part of the sysadmin server role.
2. Database OWNER aka DBO inside the database
3. CONTROL SERVER permission is granted at server level. (SQL 2005 & above only)
Not many people know or pay attention to the 3rd point about Control Server permissions. CONTROL SERVER is a new permission available starting with SQL Server 2005 and it grants the same access level as being a member of the sysadmin fixed server role. But still, I didn’t have an answer to how this login was able to access the database ProdDB. I opened a new query window with this login and started a profiler trace from my SA account adding all the “Security Audit” events to the trace, and filtering it on the SPID number from my query window connection. Here is what I observed when I ran a “select * from dbo.employees” table. 1. The login name was “sudarn2\testuser”, which is my windows login.
Not many people know or pay attention to the 3rd point about Control Server permissions. CONTROL SERVER is a new permission available starting with SQL Server 2005 and it grants the same access level as being a member of the sysadmin fixed server role. But still, I didn’t have an answer to how this login was able to access the database ProdDB. I opened a new query window with this login and started a profiler trace from my SA account adding all the “Security Audit” events to the trace, and filtering it on the SPID number from my query window connection.
Here is what I observed when I ran a “select * from dbo.employees” table.
1. The login name was “sudarn2\testuser”, which is my windows login.
2. The login was accessing my database “test” as a public user. Aha!
As you can see above, the select succeeded and I was able to read all the data as a public user! You can see each column definition for the event Audit Schema Object Access Event Class here.
This is starting to make sense now as to how the user is able to access all the databases, which being mapped to none. This user was accessing the database under the context of public user. But out-of-the-box, SQL Server definitely does not have any such permission granted to public user. This must be some customized public user then.
I use the following query to find out what Server Level permissions the public user had been granted using the DMV sys.server_permissions.
SELECT SPr.name as [UserName],SP.permission_name,SP.class_desc,'Server' AS 'Securable',SP.state_desc, SPr.type_desc as [Grantee_Type] FROM sys.server_permissions SP INNER JOIN sys.server_principals SPr ON SP.grantee_principal_id = SPr.principal_id WHERE class = 100 AND SPr.name = 'Public' UNION SELECT SPr.name,SP.permission_name,SP.class_desc,SPr2.name AS 'Securable',SP.state_desc, SPr.type_desc as [Grantee_Type] FROM sys.server_permissions SP INNER JOIN sys.server_principals SPr ON SP.grantee_principal_id = SPr.principal_id INNER JOIN sys.server_principals SPr2 ON SP.major_id = SPr2.principal_id WHERE class = 101 AND SPr.name = 'Public' UNION SELECT SPr.name,SP.permission_name,SP.class_desc,ep.protocol_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS 'Securable',SP.state_desc, SPr.type_desc as [Grantee_Type] FROM sys.server_permissions SP INNER JOIN sys.endpoints ep ON SP.major_id = ep.endpoint_id INNER JOIN sys.server_principals SPr ON SP.grantee_principal_id = SPr.principal_id WHERE SP.class = 105 AND SPr.name = 'Public'
Table 1. Public Server Permissions on this problematic instance
|public||VIEW ANY DATABASE||SERVER||GRANT|
|public||ADMINISTER BULK OPERATIONS||SERVER||GRANT|
|public||ALTER ANY CONNECTION||SERVER||GRANT|
|public||ALTER ANY CREDENTIAL||SERVER||GRANT|
|public||ALTER ANY DATABASE||SERVER||GRANT|
|public||ALTER ANY ENDPOINT||SERVER||GRANT|
|public||ALTER ANY EVENT NOTIFICATION||SERVER||GRANT|
|public||ALTER ANY LINKED SERVER||SERVER||GRANT|
|public||ALTER ANY LOGIN||SERVER||GRANT|
|public||ALTER ANY SERVER AUDIT||SERVER||GRANT|
|public||ALTER SERVER STATE||SERVER||GRANT|
|public||CREATE ANY DATABASE||SERVER||GRANT|
|public||EXTERNAL ACCESS ASSEMBLY||SERVER||GRANT|
|public||VIEW ANY DEFINITION||SERVER||GRANT|
Table 2. Default Public Server Permissions out of the box with SQL Server
|public||VIEW ANY DATABASE||SERVER||Server||GRANT|
As you see in the Table 2 above, the public role has been granted a bunch of Server Level permissions which are un-necessary and pose a security risk. The most important of them all is the CONTROL SERVER permission, as this alone is enough to gain sufficient permissions equivalent to a sysadmin. The control server granted to public implies other permissions like ALTER ANY DATABASE, SHUTDOWN, ALTER ANY LOGIN etc. The full list of permissions implied by Control Server is given here.
So we finally have an answer to who (public) and how (control server granted to public). This was how an un-mapped user was able to access a user database like ProdDB and read/edit data etc., all because someone threw the security best practise book into the trash and decided to grant pretty much every server permission to the public role.
What to do to fix this gaping security hole?
Since we have identified the permission which grants public this unwanted access, we need to either REVOKE it or DENY it. Here is the T-SQL to do this,
REVOKE CONTROL SERVER FROM public; (or) DENY CONTROL SERVER TO public;
After the above command was executed by SA, the windows user is now unable to access any database he isn’t mapped to, which is what we wanted. But as a further test, we created a new SQL login called testuser and mapped it to 1 specific user database. When we tried to login using testuser, the login failed with this error.
When I checked the SQL error log, I saw the following 18456 error getting logged.
2012-03-28 23:33:33.570 Logon Error: 18456, Severity: 14, State: 11.
2012-03-28 23:33:33.570 Logon Login failed for user ‘SUDARN2\testuser’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
State:11 means the login is valid, but lacks server permissions. I used the same query above or this one below to see what permissions public role now had at the Server level.
SELECT State_Desc, Permission_Name, class_desc, COALESCE(OBJECT_NAME(major_id),DB_NAME(major_id)) SecurableName, SCHEMA_NAME(O.schema_id) [Schema], Grantees.Name GranteeName, Grantees.Type_Desc GranteeType FROM sys.server_permissions Perms INNER JOIN sys.server_principals Grantees ON Perms.Grantee_Principal_Id = Grantees.Principal_Id LEFT OUTER JOIN sys.all_objects O ON Perms.major_id = O.object_id where Grantees.Name = 'public' ORDER BY GranteeName
I saw the after revoking the CONTROL SERVER permissions, the other implied permissions were gone and now only the “VIEW ANY DATABASE” permissions was granted. Going back to Table 2. above we know that by default SQL Server has granted 5 Server permissions to public and out of this 4 of them are on ENDPOINTS. One of each protocol type: TCP, Named Pipes, Shared Memory and VIA. These were missing.
I granted these back so as to reset public back to the original out-of-box configuration.
grant CONNECT ON ENDPOINT::[TSQL Named Pipes] to public; grant CONNECT ON ENDPOINT::[TSQL Local Machine] to public; grant CONNECT ON ENDPOINT::[TSQL Default TCP] to public; grant CONNECT ON ENDPOINT::[TSQL Default VIA] to public;
Once I did this, the test login testuser was able to successfully make a connection to SQL Server.
1. For those of you DBA’s out there, who don’t want any user/login accessing the SQL Server instance, the 4 endpoint permissions above would be an ideal candidate to revoke from public. Once this is done, no one except sysadmins can connect to SQL Server.
2. You can revoke these 4 endpoint permissions using the below T-SQL commands,
Revoke CONNECT ON ENDPOINT::[TSQL Named Pipes] From public; Revoke CONNECT ON ENDPOINT::[TSQL Local Machine] From public; Revoke CONNECT ON ENDPOINT::[TSQL Default TCP] From public; Revoke CONNECT ON ENDPOINT::[TSQL Default VIA] From public;
3. Just running the above is not enough, since you need to take care of granting connect permissions to your actual application logins, e.g. db_appadmin. If you revoke as given in step #2 and stop there, then you will get into deep trouble and none of your application users will be able to connect to SQL Server. The login will fail with Msg: 18456, State:11. You will need to specifically grant connect on endpoint permissions to each of those logins (for all the 4 protocols) which needs to connect to the SQL instance.
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [loginname] e.g. GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [db_appadmin]
Well, thus ends the strange but interestingly curious story of public role access and how to control it. I have some more T-SQL code and samples covering public & Guest users. Read through this blog post of mine for more details on how to go about curbing public rights and its pros & cons.
It’s all elementary my dear Watson .