cross database trigger: server principal is not able to access the database under the current security context

Question

I have a two databases Source and Target hosted in the same SQL Server 2008 R2 instance. I have a Login which is mapped to Users in both databases. Each mapped User has a Role in its respective database which grants access to all objects in that database.

I have a cross-database trigger which propagates updates from a table in Source to a table in Target.

When I connect to the server in SQL Server Managment Studio with the Login and run an update query on Source, the cross-database update succeeds.

But when an equivalent update is initiated using the same Login from within an application, the trigger fails with error:

The server principal 'name' is not able to access the database 'Target' under the current security context.

By running a trace with SQL Server Profiler, I can verify that the update is being run under the same LoginName in both cases.

Questions:

1. What is special about SSMS that allows the cross-database trigger to work?

2. What does "current security context" mean?

3. What do I need to modify to allow the update+trigger to work in the Application's context?

I've read about object ownership chaining, and I've not fully investigated that yet. But since it works in SSMS, I'm tempted to believe that a broken ownership chain is not the issue. But I could be wrong about that! I'd be really grateful for any diagnosis suggestions.

---

Update: I'm attempting to implement srutzky's suggestion. This is what I have run so far.

use [SourceDB];

create certificate [Access_TargetDB]  
   encryption by password = 'password123'  
      with subject = 'Cross-DB Access to TargetDB',   
      expiry_date = '2099-12-31';

add signature to [MyTrigger]
    by certificate [Access_TargetDB]
    with password = 'password123';

backup certificate [Access_TargetDB]
    to file = 'C:\Access_TargetDB.cert';

use [TargetDB];

create certificate [SourceDB]
    from file = 'C:\Access_TargetDB.cert';

create user [SourceDBUser] for certificate [SourceDB];

exec sp_addrolemember 'StandardUserRole', 'SourceDBUser';

I am still getting the same error when attempting to update the table in SourceDB with the Application Role context.

---

I'm attempting to implement srutzky's 2nd suggestion. In addition to the above, I have also run the following. But the suggestion assumes a target stored procedure which I do not have, so maybe this isn't actually a valid test.

use master;

create certificate [SourceDB]
    from file = 'C:\SourceDB.cert';

create login [SourceDBLogin] from certificate [SourceDB];

grant authenticate server to [SourceDBLogin];

Still no change in the permissions available in the Application Role context.

Answer 1

This should be fairly simple to accomplish via module signing. Look at my other answers here on DBA.SE:

• Permissions not allowing Update or Select on a trigger executed function

• Permissions in triggers when using cross database certificates

The basic concept is as follows:

1. Create a Certificate in the Source DB (i.e. where the Trigger exists).

2. Sign the Trigger using ADD SIGNATURE.

3. Create that same Certificate in the Target DB.

4. Create a User in the Target DB from that Certificate.

5. Grant that Certificate-based User INSERT, UPDATE permission on the table in the Target DB.

Answer 2

I discovered that the problem with update+trigger initiated from within the Application is because the Application is using an Application Role to supersede the Login's security context.

More information can be found in this blog post by Brian Kelley: SQL Server Security: Pros and Cons of Application Roles. In particular:

When an application role takes effect, it overrides any database and login credentials for the connection. That means if I try to access another database using the three-part naming convention like Northwind.dbo.Customers, I won't map into the database based on my original login. The only user I can use is guest.

So answers to the specific questions:

1. SSMS is not special; it is the Application that blocks the cross-database access.

2. The current security context is the list of login tokens and user tokens through which permissions are granted/denied. For example, the following query returns two rows when run in SSMS

   SELECT name, type, usage FROM sys.login_token;
   
   name         type           usage
   ------------ -------------- ---------------
   mylogin      SQL LOGIN      GRANT OR DENY
   public       SERVER ROLE    GRANT OR DENY
   

   When the same query is run in the Application Role's context, only the public login token remains and the usage for that token becomes "DENY ONLY".

   More information on TechNet: Understanding Execution Context

3. I'm still looking for a solution! The Application developer will not abandon the recently added Application Role. And it would be exremely unwise to grant the necessary permissions to the public role in the Target database.