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

permissionssql serverssmstrigger

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.

Best Answer

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

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.

Related Question