Recently I have come across to an old topic relating to SQL Server security, more precisely to database ownership. I think it is worth revisiting this topic to highlight how an elevated privileges account combined to a trust relationship for a database can compromise the security of the whole instance.
The Microsoft SQL Server 2012 Security Best Practices White Paper (Available for downloading here) warns people of the possibility of opening an “Elevation of Privilege path from DBO to sysadmin” when establishing a “trust relationship” with a database. At the same time, although the white paper always recommends choosing accounts following the least privileges principle, some people recommend as “security best practice” to make [sa] owner of every database within an instance. They fail to mention the risks of combining this ownership with a trust relationship on a database.
That can be acceptable for consistency in some environments or to make your life easier in case you want to delete the user who owns the database, but if we are strictly speaking of security best practices, it is just not true.
TRUSTWORTHY
The TRUSTWORTHY database property was first introduced in SQL Server 2005 as an additional security measurement for some new features which were also released back then: EXECUTE AS and CLR assemblies.
As per BOL, “The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within”
Also in BOL we can read that when TRUSTWORTHY is ON for a database, “Database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database”. I would say not only modules, any script using impersonation can take advantage of this trust relationship and make SQL Server believe that the impersonated user is legitimate, therefore it would pass any security check carried out by the instance the same way the original user would.
In other words, when TRUSTWORTHY is OFF (which is the value all databases are created and cannot be changed for [model] database), impersonated users (using EXECUTE AS) will be limited to their database-scope, and server-scoped permissions will be denied, but once TRUSTWORTHY is ON, this limitation is removed and impersonated users from the trusted database will be allowed to perform actions outside of their database, where obviously they must comply to the target scope’s permissions (other database or server-scope).
This is not a risk by itself, it’s just the mechanism we have to allow impersonated users to access resources outside of their natural security context, but it’s the combination of this trust with a high privilege database ownership what can put your instance in serious danger.
Members of the [db_owner] fixed database role have implied permission to IMPERSONATE any user within the database, hence they can also impersonate the owner of the database by impersonating [dbo].
You can imagine how critical this is if we have made [sa] owner of the database, because even if we have disabled it, as the attack comes from inside the instance, that will not stop it (If [sa] is disabled, we will not be able to connect to our instance using it, but our malicious connection is made by another [legitimate] user).
I have prepared a very simple lab to actually see in action what most DBAs have read many times, but maybe some have not had the chance to put it in practice.
Disclaimer: Like any other lab, this is for training purposes, NOT to be run in PRODUCTION and comes with no guarantee or legal liability.
Create the environment
In my case I have a freshly installed new instance named “localhost\MSSQL2014”, where I’m member of the [sysadmin] fixed server role, and I restored copies of the sample databases [AdventureWorks2014] and [AdventureWorksDW2014]. The sample Databases can be downloaded from here.
To restore the databases into your instance you can use this code, backup files are in the default Backup folder and data and log files would go to the default DATA folder (please customize these paths to match your current configuration)
By default we are owners of these databases once they are restored, we can check it by querying
Now we’ll follow the common advice of making [sa] the owner of our databases. We can also disable [sa] before making the change.
So far so good, we can check how the settings we care about look like by running the following query
Which should return something like
database_name | database_owner | is_trustworthy_on |
master | sa | 0 |
tempdb | sa | 0 |
model | sa | 0 |
msdb | sa | 1 |
AdventureWorks2014 | sa | 0 |
AdventureWorksDW2014 | sa | 0 |
Create a new login and database user
In this example, let’s say that [AdventureWorks2014] database is to be used by a user called [adw_dbo] which should be able to perform all tasks within the database. To do this we create the login and database user and make it a member of the [db_owner] fixed database role
First privilege escalation attempt
To attempt to get elevated privileges, our user [adw_dbo] will impersonate the owner of the database ([sa]), and then try to perform actions that [sa] would be able to do outside of the database-scope.
We will see that although the impersonation is successful, no privilege escalation can be made as impersonated users from our database (that includes [sa]) are not trusted outside of their security context. So, even something as innocent as a SELECT statement, will be stopped by the instance if [sa] (impersonated by [adw_dbo]) wants to reach anything outside its database
To verify it, open a new connection to your instance using the login [adw_dbo], create a new query window and run these queries.
When trying to select from [AdventureWorksDW2014] we should be getting an error
Let’s remove the impersonation
In this case, we did not have privilege escalation as impersonated users from [AdventureWorks2014] database are not trusted out of its natural security context (database), so nobody else out there actually believes we are [sa]. What a pity.
The problem arises if we need, for any reason, to change the trust relationship for our database. That can be due to that new stored procedure which access data from a different database and is created using WITH EXECUTE AS clause or we want to test/deploy some CLR assemblies without using a certificate ignoring best practices or just “an accident”
Second privilege escalation attempt
In our script window running under the [sysadmin] account, execute
Now return to the query window open for [adw_dbo] and re-execute the query below:
This time we should be allowed to access [AdventureWorksDW2014] as the instance trusts us and truly believes we are [sa]. So from now on we can do exactly whatever [sa] can ... for example making ourselves member of the [sysadmin] fixed server role!
And we can check that the change was effective
That was really nice, wasn’t it?
Conclusion
TRUSTWORTHY is a powerful database feature documented by Microsoft and exists for a reason (if you need to access resources outside your database using impersonation or you want to avoid signing UNSAFE or EXTERNAL CLR assemblies, although this is not a best practice), but DBAs should be aware of the risks their databases can be exposed to when it is combined to a high privilege database ownership, something very common as [sa] has been recommended to be the owner of all databases along the years.
You have also seen how disabling [sa] account does not help as the attack comes from inside the instance.
When people give away recommendations like ‘[sa] should be the owner of all databases in your instance’, these should also come with the ‘side effects’ list, so database professionals have all the information before taking a decision they can regret in the long (or maybe short) run
For the question "Who should be the owner of my databases?" The answer is, as usual, ‘it depends’, but it helps to be aware of the implications of setting [sa] as owner of all databases and not taking it as a best practice. When speaking of security, I would recommend following the principle of least privilege instead.
Andreas Wolter’s article gives away some very good recommendations for database ownership depending on different environments, it is worth the reading.
Hope you enjoyed this (my first) article and helps you maybe to make databases a bit more secure.
Special thanks to my technical reviewer Jon Fallows (@jonwolds)
References
- SQL Server 2012 Security Best Practices White paper (Microsoft) - http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx
- SQL Server Database Ownership: survey results & recommendations (Andreas Wolter) - http://www.insidesql.org/blogs/andreaswolter/2014/06/sql-server-database-ownership-survey-results-recommendations
- ALTER DATABASE SET Options (Books Online) - http://msdn.microsoft.com/en-us/library/bb522682.aspx
- TRUSTWORTHY Database Property (Books Online) - http://msdn.microsoft.com/en-us/library/ms187861.aspx
- Guidelines for using the TRUSTWORTHY database setting in SQL Server (Microsoft KB) - https://support.microsoft.com/kb/2183687
- The TRUSTWORHY bit database property in SQL Server 2005 (MSDN Blogs) - http://blogs.msdn.com/b/sqlsecurity/archive/2007/12/03/the-trustworhy-bit-database-property-in-sql-server-2005.aspx
- GRANT Database Principal Permissions (Books Online) - http://msdn.microsoft.com/en-GB/library/ms173848.aspx
- EXECUTE AS (Books Online) - http://msdn.microsoft.com/en-us/library/ms181362.aspx
- Extending Database Impersonation by Using EXECUTE AS (Books Online) - http://msdn.microsoft.com/library/ms188304(SQL.105).aspx
- Creating an Assembly (Books Online) - http://msdn.microsoft.com/en-us/library/ms345106.aspx