This article is the second one of the three articles devoted to a particular security configuration combination of database security.
In my previous article, I presented a scenario in which we were able to compromise data in a SQL Server database.
I would like to note that the knowledge of this configuration combination is critical. In this article, I am going to provide further information and reasons for the importance of this issue.
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 the following answers:
“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.”
“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.”
I found a blog, which even states that it is the best practice (I intentionally crossed out the parts I don’t agree with):
“According to the SQL Server security best practice document, we should grant login details with the least amount of privileges. Therefore, it is recommended to set a built-in sa account as an owner of all databases on the SQL Server instance. This will make database and server more secure.”
This blog post is interesting because I have checked the “SQL Server security best practice document” and it is saying:
“Best practices for database ownership and trust
- If you are an ISP, then you have distinct owners for databases; not all databases should be owned by sa.”
Well, the documentation explicitly refers to ISP scenarios, I admit it, but still, I have not found any statement in the documentation, that picking SA for being a database owner would be the best practice or even fair one.
The widely known free script sp_Blitz also puts every database in its result set with priority 200, where SA is not the owner (Database Owner <> SA) and by doing this I have a feeling that the script encourages people with less technical skills, such as accidental DBAs to stick to this practice. If you check the related finding link, it says:
Most shops end up using SA as the owner because it’s the easiest way to avoid ownership problems when the owner name no longer exists. This is not best practice – but there’s no short answer for best practice.
Ok so, it is for sure that this is NOT the best practice! I would like to join Andreas Wolter (sp_Blitz page also refers to this page in the ‘To Fix the Problem Long Term’ section) and emphasizes that using the SA account as a database owner is the WORST practice actually and personally I think this should be highlighted on every blog and in every documentation, related to this topic.
I know that if the users would be created with only valid privileges that would be enough, but unfortunately, this is not how things usually work and in the case of security questions there are no ‘IFs’. I mean in a good way of thinking. You need to be prepared for the ‘possible worst’ scenarios.
Just think, what we could do in our example if the default database owner were the SA!
Let’s go on with the second option, the TRUSTWORTHY database option. Fortunately, the situation is a bit better in the case of this one, but still, has a common problem with its handling.
Everybody knows that the best practice here is as follows: Set the ‘Trustworthy’ Database Property to Off.
We have just seen why this option is “bad”, but this is not everything. Here is why I am still concerned about this option.
If you try to find some scripts, which 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'
sp_Blitz has a check as well, which checks the default settings of the databases (including TRUSTWORTHY as a default value of 0) and reports every database, which has non-default settings, but the script skips the system databases.
Furthermore, there is a MS KB article, which focuses on this topic.
Refer to these guidelines for using the TRUSTWORTHY database settings in SQL Server: https://support.microsoft.com/en-us/kb/2183687
There is a code sample in the article, which lists the databases that have the TRUSTWORTHY bit ON and whose database owner belongs 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, and 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 article 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 due to the fact that the TRUSTWORTHY setting is set to ON for the MSDB database by default.
That’s all for now. We went through and checked two important database options, the owner of the database and the TRUSTWORTHY database setting. I just wanted to put a spotlight on these options since – as you saw – they are very critical options, especially when we talk about the combination of these two options.
I hope you have enjoyed the article. In my further publications, I will provide comprehensive scripts that help you to identify the possible risks in your SQL Servers: users that could take advantages of these options, and databases that could be a target or a source of such an attack.
Latest posts by Robert Virag (see all)
- SQL Server Security Ponderings – Part 2 | Database owner + TRUSTWORTHY - May 5, 2017
- SQL Server Security Ponderings – Part 1 | DMI – Bobby Returns - December 5, 2016