The SQL Dude!

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…..

Basics

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)

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

Table 2. Default Public Server Permissions out of the box with SQL Server

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;

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.

Notes:

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 .