DBA is the guardian of the data, and there are two aspects of being a guardian. The first one is integrity. It includes tasks like checking database consistency, creating backups, and, should any problems appear, having a well-designed, comprehensive database recovery plan.
The second aspect is security. It suggests making sure that only authorized users have access to the data, and only to the data that they need.
You can find numerous resources dedicated to security on the Web. Though, I think that some important things lack appropriate attention. In this article, I would like to focus on these options and demonstrate why it is important to be aware of them and to handle them correctly.
A Mission to Compromise an SQL Server
Let’s have a Role-Playing Game in which you are a Secret Agent, and your mission, should you accept it, is to steal Very Important Data from the TargetDB database hosted by a SQL Server. You need to get this data and delete it.
It is possible to get a Login for you, but every login on the server has explicit DENIED permissions on the target database and data. The only information that our agent can provide you with is that one generated for verification by the security protocol during the creation of your login.
Database information from the target server.
Server permissions:
Database permissions:
As every decent agent, let’s do the homework and check what you are dealing with.
You cannot just log in and connect to TargetDB, since every single permission is denied for your login, and its mapped user explicitly. You need to get in from another database.
A Locked Door
Cross-database actions are not easy things to do. Consider it as a closed door with two locks on it. We won’t focus on the technical details here, but you can refer to Extending Database Impersonation by Using EXECUTE AS MSDN article (I highly recommend doing so).
The first lock is Trusting the authenticator, and the second is Trusting the database.
Let’s start with the first lock. Trusting the authenticator means that for access to another database B, the owner of database A must be granted (explicitly or implicitly) the AUTHENTICATE permission in database B.
Wait a minute! The authenticator on the database level is the owner of the database itself (Don’t mix it up with the db_owner database role!).
Check the database owners:
Even though they follow pretty good practice by using one account per server, which is not SA, this way they have kindly opened the first lock for you.
Let’s focus on the second lock.
Somehow, you need to have a database created on the server with the TRUSTWORTHY property ON. The best practice here is to set the TRUSTWORTHY database property to OFF.
This is the time when we should say: what if you already have such a database?
It is the MSDB database.
The second lock is done. You did not even need to break any of the locks.
The importance of the db_owner role
Right now, you have to deal with one challenge. Somehow, you need to get into the MSDB database with the db_owner database role because it has implicit, impersonate permission.
Since MSDB is not usually in the database administrators’ focus, this mission is not impossible anymore. Let’s see what you can do just because you got a user with the db_owner database role in the MSDB database:
Try to connect to the TargetDB:
This is an expected error. Remember the security protocol applied on the provided login. Let’s start it.
Try to connect to the TargetDB and to select the target data:
It works! Let’s modify it and after that verify the action.
That’s all.
Mission accomplished.
As you saw, 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. Let’s review another possible scenario now.
Database owner + TRUSTWORTHY
Let’s check the background details starting with the well-known problem: database ownership. What login details should the owner of your database(s) use? A lot of people say that SA is an appropriate choice.
I did a quick Google search and found answers like the following ones:
“I don’t remember this being a concern for me ever. Other than looking annoying in reports, or being unable to remove the user if they own a database, but I don’t think it affects server operations. You can just pick sa for consistency.”
Or
“I don’t think to own a database by SA or any other user should be of any concern. What matters is who is performing ‘what’ in your database. So, it is a good idea to create users with valid privileges. For simplicity, you can specify the owner as SA.”
The current situation is that using the SA account as a database owner is the WORST practice. I personally think this should be highlighted on every blog and in every documentation, related to this topic.
If we create users with only valid privileges, that would be enough, but unfortunately, this is not how things usually work. You need to be prepared for the ‘possible worst’ scenarios. Just think, what we could do in our earlier examples if the default database owner was SA!
Let’s go on with the second option, the TRUSTWORTHY database option. The situation is a bit better but it still has a common problem. As it is commonly considered, the best practice here is to Set the ‘Trustworthy’ Database Property to Off. We have just seen why this option is bad.
But this is not everything. If you try to find some scripts to check this property, you will probably find a script similar to this one:
SELECT name FROM sys.databases WHERE is_trustworthy_on = 1 AND name != 'msdb'The sp_Blitz script that checks the SQL Server health also checks the default settings of the databases (including TRUSTWORTHY as a default value of 0) and reports every database which has non-default settings. However, the script skips the system databases.
Furthermore, there is an MS KB article, which focuses on this topic. Refer to the guidelines for using the TRUSTWORTHY database settings in SQL Server:
There is a code sample in the article, which lists the databases that have the TRUSTWORTHY bit ON, and whose database owners belong to the sysadmin server role:
SELECT SUSER_SNAME(owner_sid) AS DBOWNER, d.name AS DATABASENAME
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON
p.principal_id = m.member_principal_id
inner join sys.databases d on suser_sname(d.owner_sid) = p.name
WHERE is_trustworthy_on = 1 AND d.name NOT IN ('MSDB') and r.type = 'R' and r.name = N'sysadmin'
What is common in these scripts? Each script excludes the MSDB, but as the MS KB article notes, you have just seen it in our “mission”:
Note: By default, the TRUSTWORTHY setting is set to ON for the MSDB database. Altering this setting from its default value can result in unexpected behavior by SQL Server components that use the MSDB database.
I would like to emphasize that the main focus of this section is neither the TRUSTWORTHY database option nor the database owner property itself, but the combination of these two options. I have mostly concentrated on MSDB because the TRUSTWORTHY setting is set to ON for the MSDB database by default.
Conclusion
That’s all for now. We went through and checked the practical scenarios that relate to SQL Server security. We also reviewed such important database options, as the owner of the database and the TRUSTWORTHY database setting.
I just wanted to put a spotlight on these options since – as they are critical, especially when we talk about them in combination.
Tags: database administration, database security, sql server Last modified: October 13, 2021
