May 28, 2023 at 12:43 pm
I have established a Service Broker configuration based on Eitan Blumin's excellent example.
https://eitanblumin.com/2018/10/31/advanced-service-broker-sample-asynchronous-triggers/
I have crafted my own Stored Procedure. I can get the example to work when I'm performing activities within the same database. If I try to operate outside the current DB I get errors.
I'm new to Service Brokers and I'm having trouble capturing the error but I suspect this is all part of the same problem.
This works
DELETE FROM CurrentDB.dbo.[gis_pt_im_map_auth_label] WHERE plotnumber IN ('+@ids+')This fails
DELETE FROM OtherDB.OtherSchema.[gis_pt_im_map_auth_label] WHERE plotnumber IN ('+@ids+')Below is the SP in question. I suspect it is some kind of user permission but I have tried a couple of user based changes like making the executing user the DBO of both databases and no result.
For clarity if I execute the SP manually this will run. However Service Brokers change the nature of the execution.
Any thoughts or inputs appreciated.
CREATE PROCEDURE [dbo].[sp_Location_UPDATE_TEST3]
@inserted XML,
@deleted XML = NULL
AS
SET NOCOUNT ON;
BEGIN TRY
CREATE PROCEDURE [dbo].[sp_Location_UPDATE_TEST3]
@inserted XML,
@deleted XML = NULL
AS
SET NOCOUNT ON;
BEGIN TRY
DECLARE @SQL NVARCHAR(max)
DECLARE @ids NVARCHAR(max)
IF EXISTS ( SELECT NULL FROM @inserted.nodes('inserted/row') AS T(X) )
BEGIN
INSERT INTO [dbo].[LocationViewLog]
([lo_location]
,[lo_Location_Code]
)
SELECT inserted.[lo_location]
,inserted.lo_location_Code
FROM
(
SELECT
--X.query('.').value('(row/PurchaseOrderID)[1]', 'int') AS PurchaseOrderID
X.query('.').value('(row/lo_Location)[1]', 'int') AS lo_location
,X.query('.').value('(row/lo_Location_Code)[1]', 'nvarchar(100)') AS lo_location_Code
FROM @inserted.nodes('inserted/row') AS T(X)
) AS inserted
SELECT @ids = String_agg(inserted.lo_location, ',')
FROM
(SELECT
X.query('.').value('(row/lo_Location)[1]', 'int') AS lo_location
FROM @inserted.nodes('inserted/row') AS T(X)
) AS inserted
--The following block is what is problematic.
--if I try to execute this delete in another database the Service Broker breaks as a result of an error.
--If I craft this same command to operate in the current database there's no problem
SET @SQL = 'DELETE FROM Test.dbo.[gis_pt_im_map_auth_label] WHERE plotnumber IN ('+@ids+')';
INSERT INTO [dbo].[LocationViewLog]
(lo_Location_Code) VALUES (@SQL);
EXEC(@SQL)
--END Problem Block
END
END TRY
BEGIN CATCH
-- Since we're in an Asynchronous Trigger, rolling back an update operation
-- is a lot more complicated than in a regular trigger.
-- For now, for this scenario we'll take the risk of having partial data.
EXECUTE [dbo].[uspLogError];
END CATCH;
GO
May 28, 2023 at 6:48 pm
More information is required. What is the error etc. When using Service Broker you will have to monitor it in case it stops throws errors etc. You should get out of the habit of starting procs with sp_.
Your problem here is probably permissions in Test db. You should really sign the procedure and give the certificate user permission in Test.
https://www.sommarskog.se/grantperm.html
https://sqlquantumleap.com/2017/12/30/please-please-please-stop-using-impersonation-execute-as/
May 28, 2023 at 10:40 pm
Thanks Ken.
I'm having trouble capturing the error. If I have a simple error and the operation is able to fall into the CATCH block I get an error I can work with.
In the case where I'm trying to operate in the TEST DB all I end up with in a never ending CONVERSATION and a disabled SB queue.
As mentioned same command works in the local database, causes the above in a second DB.
I'll look into the signing and see if that resolves the issue.
Thanks for the advice.
May 29, 2023 at 12:33 pm
Thanks again Ken.
It appears your good advice put me on the right track. Ironically, it's quite possible my foray into Service Brokers to address my core issue, trying to update a in a second database may have worked with a basic trigger if I'd used Module Signing to allow the the triggers to operate between two databases.
Used the following two links to great effect against my Service Broker question. So now I'm quite expert in SB as well as Module signing.
https://sqlundercover.com/2018/10/30/signing-stored-procedures-that-access-multiple-databases/
Thanks again.
May 30, 2023 at 6:32 am
Ken,
It seems I spoke too soon.
I thought I'd gotten this right but I'm now looking at the server module signing as the prime culprit.
Very basic testing using the links above work just fine as far as module signing and executing SP's based on triggers across databases is concerned. However when I tried running this with SB, things change.
I ran Profiler to see if I could trace the issue.
The update to the table runs as my own user, trigger fires as my own user, Stored proc called by the trigger as my own user but once the SB is asked to fire the user context changes to 'sa'.
All of the components were signed with the same certificate.
I'm now wondering if the SB was the answer to my original question.
In a trace of the original issue which is an update that fires a trigger, the update runs under one account. The trigger directly attempts to update the a table in the second database under the same account. In the absence of module signing this would most likely fail.
I have attached a trace image. I needed to anonymise the data. You will see the table update is and RPC transaction. Between the start and end RPC transaction are the trigger fires and the updates/deletes are attempted.
Should I be looking to try and get the account that is running the Update and Triggers to sign the resources in the second database?
A
The SB activity is all running under 'sa' exclusively.
May 30, 2023 at 8:03 am
You are going to have to read up on this yourself as it is too involved for a post.
Personally I would not have a SP acting across two databases with SB. I would setup queues in both databases and have a dialog between them. (This also has the advantage that the other db can be moved to a different instance.) The trigger can then just insert into your log table (which I am not particularly convinced about as temporal/versioned tables work better about 80% of the time) and then add to the queue.
If you want to understand procedure signing you need to carefully read your way through Erland Sommarskog's excellent paper.
Should I be looking to try and get the account that is running the Update and Triggers to sign the resources in the second database?
Obviously ownership chaining should work in the current database; you need to copy the certificate to the second db, create a certificate user and then assign the permissions. If the procedure is signed then it will use the permissions of the certificate user in the second db - all in Erland Sommarskog's paper.
May 30, 2023 at 8:27 am
I have just found an actual error from profiler that matches what I'm seeing in a Profiler Trace.
'The server principal "sa" is not able to access the database "TEST" under the current security context.'
Now I'm struggling to correct this.
I tried a couple of the standard Alter type command and i'm getting the following
Cannot alter the user 'sa', because it does not exist or you do not have permission.
I should add I'm trying this logged in as SA and as a user who is a member of sysadmin.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply