Cross-database access. Part 1 – the worst nightmare or why applications should not use sa login

In this series I am going to show you three different ways of enabling cross-database access, starting with the worst, but for whatever reason, still common “solution”. The second one will be better, but still not very secure. Finally, we will see how cryptography can ultimately solve this problem.

 

Remember, login sa is mapped to dbo account in each and every database

To connect to a SQL Server database, we need a login (at a server level) and a user account (at a database level), that is associated with this login. This means that we have two different types of security tokens: login tokens and user tokens.

Since SQL Server 2000 Service Pack 3, databases are isolated from each other in a way which means that an object in one database cannot access objects that live in a second db. However, sa login is a notable exception. To see this, let me switch to sa login context, create two databases and check what user account is associated with this login in a master database and in a user one:

EXECUTE AS LOGIN = 'sa';
GO

SELECT USER_NAME();
GO

CREATE DATABASE DB1;
CREATE DATABASE DB2;
GO

USE DB1
SELECT USER_NAME();
GO
------
dbo
dbo

 

Let’s say that DB1 is a client’s database that contains some valuable information, and DB2 is a competitor’s database. So, in a DB1 there will be a table:

CREATE TABLE T (K INT);
GO

 

USE DB2;
GO
CREATE PROC CrossDBProc
AS 
SELECT * FROM sys.login_token;
SELECT * FROM sys.user_token;
SELECT * FROM DB1..T
GO

 

Now, if we try to execute this procedure, it will work just fine:

EXEC CrossDBProc;
GO
------
principal_id    sid        name       type        usage
1                0x01    sa        SQL LOGIN      GRANT OR DENY
2                0x02    public    SERVER ROLE    GRANT OR DENY
3                0x03    sysadmin  SERVER ROLE    GRANT OR DENY

principal_id    sid       name        type        usage
1                0x01    dbo        SQL USER    GRANT OR DENY

 

No error was returned, which means that this SELECT * FROM DB1..T statement succeeded. The reason is simple: sa login belongs to sysadmin server role, this role is mapped to dbo user in every database and a dbo can do anything within the database.

Take notice that those databases are not trustworthy neither cross-db chaining enabled:

SELECT name, owner_sid, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE name LIKE 'DB%';
GO
------
name    owner_sid    is_trustworthy_on    is_db_chaining_on
DB1    0x01            0                    0
DB2    0x01            0                    0

 

And even if those database had different owners, the situation would not change:

CREATE LOGIN User1
WITH PASSWORD = 'Pa$sw0rd';

CREATE LOGIN User2
WITH PASSWORD = 'P@ssw0rd';
GO

ALTER AUTHORIZATION ON DATABASE::DB1 TO User1
ALTER AUTHORIZATION ON DATABASE::DB2 TO User2
GO

EXEC CrossDBProc;
GO

 

Lesson learned — sa is a server admin and if an application connects as sa, it will be able to do everything not only inside its database, but with others databases as well. And there is absolutely nothing that you, as a DBA, can do about it. The only solution is to persuade the application vendor to not using this login.

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in Security, SQL Server and tagged by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Leave a Reply

Connect with:
  • This field its required.
  • This field its required.
    • Message is required