New Permissions in SQL Server 2014: IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER

New Permissions in SQL Server 2014.

IMPERSONATE ANY LOGIN, SELECT ALL USER SECURABLES, CONNECT ANY DATABASE and the old CONTROL SERVER

SQL Server 2014 brings altogether 5 new permissions. Two of those are on database level and only available in the Windows Azure SQL Database Edition – not in the box-version. (Thanks Erland Sommarskog for confirming this and pointing me to the quite hidden note in the documentation: GRANT Database Permissions)
The new permissions are as follows:

Class Desc.	Permission Name	Type	Parent Covering Permission Name
DATABASE	ALTER ANY DATABASE EVENT SESSION	AADS	ALTER ANY EVENT SESSION
DATABASE	KILL DATABASE CONNECTION	KIDC	ALTER ANY CONNECTION
SERVER	CONNECT ANY DATABASE	CADB
SERVER	IMPERSONATE ANY LOGIN	IAL
SERVER	SELECT ALL USER SECURABLES	SUS

So, what for and how can we use those permissions on Server level?

IMPERSONATE ANY LOGIN

Do you remember the problem with CONTROL SERVER? The biggest flaw of this permission was, that this permission also allowed Impersonation of any account, including privilege elevation to any sysadmin. I have documented this and other problems with CONTROL SERVER in detail here:

CONTROL SERVER vs. sysadmin/sa: permissions, system procedures, DBCC, automatic schema creation and privilege escalation – caveats

Now in SQL Server 2014, by introducing the permission IMPERSONATE ANY LOGIN, gives us ammunition to tackle this problem.

This Permission permits to impersonate any Login and User(!).

If we DENY this to the Principal with CONTROL SERVER permission, it prevents him from impersonating any Login directly. (Why do I say “directly”? – We’ll see a bit further down.)

So let’s see how to prevent a Login with CONTROL SERVER from elevating privileges by impersonating another login with help of the new permission:

Hooray!(?)

Privilege-Escalation-risc:

Really? Still? Of course.

Still we are running under the context of DBA_TheDude:

We cannot Impersonate the “UtilizeMe” Login, but we can just Log On using his password!

Another reason to not use SQL authentication by the way, as he would then need to find a valid Windows-Login’s Credentials – much harder to just creating his own backdoor-account.

1404_SQL_Server_2014_Logon_Screen_SQL_Authentication

So in order to further prevent our Administrator from elevating privileges to sysadmin, we also need to work with DENY ALTER ANY LOGIN and ALTER ANY SERVER ROLE.

Can we THEN finally use CONTROL SERVER completely safely?

NO!

In fact there are a few other things one can do to elevate permissions from a CONTROL SERVER-permitted account. More tricky in a way, but an attacker with some good knowledge about SQL Server (note, I am not saying “rocket-scientist”) will be able to do that.

I am aware that the “Separation of Duties in SQL Server 2014”-Whitepaper (Contained in the Microsoft® SQL Server® 2014 Product Guide) does in fact list the combination of GRANT CONTROL SERVER + DENY IMPERSONATE ANY LOGIN as a best practice, but yet…

So do I recommend using it in any way?

That is a hard question for me personally, as I would like to see much less people using/granting sa/sysadmin for daily tasks, and this permission had the potential to make an end to it.

Unfortunately it is far from perfect, and in security-terms, anything not flawless, is a risk.

But in terms of getting people away from using the highest privileges from the very beginning, I do see it as a step, since many may just not have the time and skills to break out of it.

I do recommend using it in combination with some solid Auditing and alerts in place. So anyone using this instead sa/sysasdmin still gets applause, as it shows you care and dare to limit permissions.

SELECT ALL USER SECURABLES

This permission can be used for preventing a highly privileged Principal that may be troubleshooting/analyzing the server from reading any user data. – Do not forget to also deny EXECUTE in all User databases though, otherwise he can just execute the stored procedures (if any exist) to get to the data. Also this is not bullet-proof as we already know from CONTROL SERVER and it’s restrictions.

What’s more safe, is the use for an Auditor that needs to read ALL data, but not change it – without the effort of creating users and permissions in all user databases.

CONNECT ANY DATABASE

This permission can be used quite well for having logins that can basically connect to any database and for example do code reviews – by combining it with the VIEW ANY DEFINITION permission. I do think this is actually of quite some use for many scenarios.

Happy “Server controlling”,

Andreas

0 replies

Want to join the discussion?
Feel free to contribute!