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.

• Solomon Rutzky: @dlh I forgot to ask before, what is the permission for the public role when selecting from sys.login_token;? Step 2 in your answer doesn't show that field. I also just noticed that you have updated the question with more details. I noticed that you didn't backup/restore the private key..not sure yet if that has any impact here, but I have never tried to omit that part. Nor have I tried adding the User to a Role instead of granting direct permissions. Also, look at this answer dba.stackexchange.com/a/166280/30859 as it required creating a login and granting it AUTHENTICATE SERVER.
• dlh: Thanks, again. I added the usage column to Step 2 in my answer. Its result is different in SSMS vs. Application Role context.
• Solomon Rutzky: @dlh I was afraid of that PUBLIC would be DENY ONLY. That'is a problem, one that is preventing my initial response, in that answer linked in my previous comment, from working there as well. It's still worth trying the Login, granted with AUTHENTICATE SERVER first just to be sure. But if that doesn't work, then it might require enabling the guest User and then creating a stored procedure in the Target DB that guest can execute and it does the INSERT or UPDATE, and it only runs if the Login is name and is executed by the Trigger. I will try to prove it out in that linked answer.
• dlh: In case it matters, the reason I didn't backup/restore the private key is the TargetDB has no master key (and I am not yet sure what the side effects of creating one will be).
• Solomon Rutzky: @dlh You aren't using the DMK (DB Master Key) since you used ENCRYPTION BY PASSWORD. :-) DMK is for when you aren't specifying a password. So you can still backup and restore the private key here. Just use the same password.

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.