Service broker - Cross-database SELECTs to insert into local DB?

I'm using Remus' article http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/ as a guide.

What I'm trying to do:

I have a Activated Stored Procedure that (within the Activated SP) calls 2 different stored procedures. One of those stored procedures needs to access tables in another database to lookup some data, after which it writes the record locally with the extra info. Since I'm crossing databases, my options are

"trustworthy" (pass, thanks)
"replicate lookup tables to the same database service broker is in"
"get certificates working" (this approach)

So I did the following, but it's still failing with "lock time out exceeded" and "not able to access database... under the current security context.

In the database service broker is running in:

USE database_with_service_broker_and_queue
GO
CREATE CERTIFICATE mysp_Auditing ENCRYPTION 
    BY PASSWORD = '123457' 
    with subject = 'god_i_hope_this_works'
ADD SIGNATURE TO OBJECT::myschema.mystoredprocedure 
    BY CERTIFICATE mysp_Auditing 
    WITH PASSWORD = '123457'
ALTER CERTIFICATE mysp_Auditing REMOVE PRIVATE KEY
BACKUP CERTIFICATE mysp_Auditing to file = 'c:\mysp_auditing.CER'

Now in the database with the additional tables:

USE db_with_tables_I_need
GO
CREATE CERTIFICATE mysp_Auditing from file = 'c:\mysp_auditing.CER'
CREATE USER mysp_Auditing FROM CERTIFICATE mysp_Auditing
GRANT AUTHENTICATE to mysp_Auditing
GRANT SELECT ON TABLE1 to mysp_Auditing
GRANT SELECT on TABLE2 to mysp_Auditing

I then ENABLE the queue and watch alllll the error messages scroll by. I even tried changing the stored procedure to add EXECUTE AS OWNER, but still doesn't work.

Any help appreciated.

Solution

too long to post as comment

not able to access database... under the current security context.

This means the certificate business is not set up correct (I don't blame you, is ungodly hard to pull it off on first try). I would look at these:

make sure 'dbo' is a valid user. Try running EXECUTE AS USER='dbo'; on both DBs involved. If one fails (it means the DB was created by a Windows SID that is no longer valid) run ALTER AUTHORIZATION ON DATABASE:<dbname> TO sa.
make sure you do not change the procedure after you sign it. Any ALTER will silently drop the signature and invalidate your trust chain.

add EXECUTE AS OWNER

The procedure must have an EXECUTE AS clause, but it should not matter much what it is, as long as there is one. You are granting the SELECT permission to the certificate (hence to the signature) so it should work no matter who the procedure impersonates.

For debugging, I recommend you do it by simply running the stored proc manually, from SSMS query window:

 USE myactivateddb;
 GO

 EXECUTE AS USER='dbo'; -- does not matter who, is important to be AS USER
 GO

 EXEC myotherdb.dbo.myotherproc ...;
 GO

 REVERT;
 GO

This is easier to debug that trying to do it from activated procedure. If this works, then try the same but instead of executing the other DB stored proc, execute the activated procedure from the SSMS query window (w/o truning on activation on the queue).