SQL Server Security Ponderings – Part 1 | DMI – Bobby Returns

Nowadays security and data privacy are in special focus. When I deliver a training, I always refer to a DBA as the “guardian of the data.” There are two aspects of being a guardian.

The first one is integrity. It includes tasks like checking database consistency, creating backups and in case of problems being prepared to fix the database by having well designed, comprehensive DR plan.

The second one is security. Making sure that only authorized users have access to the data, and only to those data that they really need.

There are a lot of source about security guidelines on the internet like blogs, technical articles, documentations. Though, I think there are some important options and a database combination which have lack of appropriate attention. In this article, I would like to focus on this combination of options and demonstrate why it is important to be aware of it and to handle it correctly.

Let’s play a Role Playing Game in which you are an Agent of the Impossible Database Mission Force with code name Bobby. By the way, Bobby’s first assignment was to delete the “Student” table… 😉 This time you (as Bobby) will use a subtler approach.

Here is your mission:

“Your mission Bobby, should you decide to accept it to steal a Very Important Data from the TargetDB database hosted by a SQL Server. You need to get this data and delete it (again).

We have an undercover agent who can assist you in a very minimal level, but his identity cannot be revealed. It is possible to get a Login for you, but unfortunately, as a security protocol, every login on the server has explicit DENIED permissions on the target database and the target data as well. The only information our agent can provide you to plan the action are the ones which were generated for verification by the security protocol during the creation of your login (see the attached files).

As always, should you or any of your I.D.M. Force be caught and/or fired, the Secretary will disavow any knowledge of your actions. This message will NOT self-destruct in five seconds this time 😉

Good luck Bobby!”

Attachments:

Database information from the target server.

Agent Sys Databases

Agent Sys Databases

Agent Sys Databases

Agent Sys Databases

Agent Sys Databases

Server permissions.

Server permissions

Database permissions.

Database permissions

As every decent agent, let’s do the homework and check what you are really dealing with.

Target DB

You cannot just log in and connect to the TargetDB, since every single permission is denied for your login and its mapped user explicitly. You need to get in from another database. Unfortunately, cross-database actions are not easy things to do. Consider it as a closed door with two locks on it. I am not going to drill down into the technical details. You can do that by reading the Extending Database Impersonation by Using EXECUTE AS MSDN article (I highly recommend to do so).

The first lock is “Trusting the authenticator” and the second is “Trusting the database.”

Target DB

 

Let’s start with the first lock. Trusting the authenticator means that for access to another database B, the owner of database A must have been granted (explicitly or implicitly) the AUTHENTICATE permission in database B.

Wait a minute, the authenticator on database level is the owner of the database itself (Don’t mix it up with the db_owner database role!). Check the database owners.

Agent Sys Databases

 

As you can see even though they follow a pretty good practice by using one account per server (more about that later on), which is not SA, this way they just kindly opened the first lock to you.

Target DB

 

Let’s focus on the second lock. So somehow, you need to manage to have a database created on the server with TRUSTWORTHY property ON. Everybody knows that the best practice here is to set the TRUSTWORTHY database property to OFF. Maybe not everybody knows why, but it doesn’t matter right now since such a request would be very suspicious.

This is the time when we should say: “that is a really an Impossible Mission”…or not? What if you already have such a database?

Agent Sys Databases

 

It is the MSDB database.

Target DB

 

The second lock is done. You did not even need to “break” any of the locks.

Right now, you have to deal with one challenge: somehow you need to get into the MSDB database with db_owner database role.

Why do you need the db_owner database role? Because it has implicit, impersonate permission, but obviously, you cannot ask the database administrators to give you access to the MSDB with explicit impersonate permission. That would be also very suspicious.

Target DB

 

Since MSDB is not usually in the database administrators’ focus (more about that later on) I think this mission is not impossible anymore. I don’t want to give any tips and tricks how you can manage to have this access, so I leave it to your imagination, but let’s assume that somehow you got it.

Let’s see what Bobby is capable of just because he got a user with the db_owner database role in the MSDB database:

Connect to Database Engine

 

Try to connect to the TargetDB:

Connect to the TargetDB

 

This is an expected error. Remember the security protocol applied on the provided login.

Let’s start it…

Connect to the TargetDB

 

Try to connect to the TargetDB and to select the target data:

Connect to the TargetDB

 

It works! Let’s modify it and after that verify the action.

Connect to the TargetDB

Connect to the TargetDB

 

That’s all, it is done.

Connect to the TargetDB

 

Like a piece of cake. MISSION ACCOMPLISHED!

As you saw, in this article I focused on a particular security database configuration combination. Those were the owner of the database and the TRUSTWORTHY database option paying particular attention to MSDB. The presented scenario was just one in which sensitive data can be compromised in the same way.

This article is the first in a series of three that are related to a particular security database configuration combination. This was just an introduction. I hope you have enjoyed it. In the next articles I will explain the reason why I thought that this article (the combination of the options introduced), this topic is important.

Robert Virag

Robert Virag

SQL Server Service Engineer at IT Services Hungary, Member of T-Systems; MCITP: Database Administrator 2008; former MCT
Robert Virag

Latest posts by Robert Virag (see all)

Robert Virag

SQL Server Service Engineer at IT Services Hungary, Member of T-Systems; MCITP: Database Administrator 2008; former MCT