Activation/Security/Dynamic SQL Question

I will have a variety of different types of work that will come into my Service Broker queue and I'll likely have a stored procedure or two for each of the different types of work (ie. move order header, move items, move payment, etc.) What is required to be done in each of these steps may vary by the subsidiary and type of order coming in. My plan is to use exclusively stored procedures but to execute them dynamically using sp_executesql. I think I should use sp_executesql because that way I can have a config file (in xml) that I can store what stored procedures need to be called for which unit of work/order type/subsidiary. If I do this I should be able to easily configure each type of work to be done in a config file and let Service Broker handle the execution dynamically. As long as I keep the parameters the same for each of the stored procedures (I'm thinking maybe 4 or 5 parameters) and passing them to each of the stored procedures, this approach will allow me to dynamically configure Service Broker to do what it is supposed to do. I can pull what needs to be done out of the message that comes in with an XQuery expression on the config file. I know that I will have to configure my user (activation user) to be able to run sp_executesql and the security may be complex (especially since I'm using certificates). I can not use trusted databases. Are there any other considerations I should think about?

Gary

You should be able to execute dynamic SQL as well as impersonate database principals (using the 'EXECUTE AS' statement or clause) to call message type handlers from the activation stored procedure. If you stay within the database, your database does not have to be trustworthy.

Hope that helps,
Rushi

Activation proc failed

Good day,

I have send a msg successful,but the proc failed. I altered the proc to correct the problem in the proc. The msg are still in the queue. Is there a manual way to excute the proc again to process the msg in the q? or do I wait for service broker to do it after a retry time (self recover)?

thank you in advance.

If the proc fails (i.e. throws an exception), Service Broker will try to restart the proc every 5 seconds. However, if your proc actually did a RECEIVE which was rolled back, then the rollback handler will detect this as a poisoned message and disable the queue. You can check this by doing:

select is_receive_enabled from sys.service_queues where name='YourQueue';

You can enable the queue by:

alter queue [YourQueue] with status = on;

|||

Rudhi

what I did :

receive top(1) @.receivedMsg = message_body,

@.h = conversation_handle,

@.msgType = message_type_name

from q111 -- wrong queue ,finger trouble.

In theLog file viewer :
The activated proc [dbo].[HandleRecieveMsg] running on queue EOGDB.dbo.q2eog output the following: 'Invalid object name 'q111'.'

So I corrected the proc to point to the correct queue.

I did as you said: "select is_receive_enabled from sys.service_queues where name='q2eog'; " q return a value of 1.

The message still is in the correct q after 5 seconds and longer.

Is there something else I can look or do?

|||

Okay I followed the steps in the help:

"Symptom: Activation Stored Procedures Do Not Run". And discovered that the proc I execute inside the RecieveProc fails.

(the proc I exec inside the recieving proc is proc reference a external assmbly)

Now my q is if a proc fails and the msg is allready retrieved from the queue, what happens to the msg? where does it go or the error msg?

|||

You can look at the state of the queue monitor:

select * from sys.dm_broker_queue_monitors;

If it is in 'NOTIFIED' state (which means, waiting for someone to do a RECEIVE), you can either reset the state by doing a RECEIVE or an ALTER QUEUE:

ALTER QUEUE [YourQueue] WITH STATUS = ON;

Hope that helps.

|||If you are doing that within a transaction scope, the transaction should rollback and the message should be put back into the queue.

Activation not working

Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

?

Thanx

Hi there.

It seems the statement in my sp that has a permission problem is the get conversation group sattement.

So my activation on the queue does not work when i stipulate execute as 'dbo' or OWNER. I cannot assign execute as 'sa' i get the following error "The server principal "sa" is not able to access the database "OLTP1PRO" under the current security context". I have tried not to stipulate the execute as so taht it defaults to a sysadmin.

But i always get this error:

The user does not have permission to perform this action.

So i tried the following.

CREATE LOGIN QueueReader with password = 'T#ST123'

CREATE USER QueueReader for login QueueReader

grant receive on [ewx.co.za/Queue/ewx_sb_central_queue] to QueueReader

grant execute on dbo.CENTRAL_Queue_Processor to QueueReader

And chanegd the execute as to QueueReader, but is till get the same error.

I dunno what else to try, no matter hwat i do i cannot execute the get conversation group statement ?

Please help ?

|||anyone?|||

What error do you get if you manually invoke the procedure, from Management Studio, under the QueueReader user context?

executeasuser'QueueReader';

go

exec dbo.CENTRAL_Queue_Processor;

go

revert;

go

|||

Hi Remus

I get the same error as before. The user does not have permission to perform this action. Error number 297.

I have done more investigation, judging by the erro line number line it seems that the QueueReader user cannot read from

sys.dm_broker_activated_tasks.

However i am expecting these errors, the activated stored procedure has many inserts etc. SO basically i want the activated sp to run as dbo, i do not want to have to setup alot of permission for the user.

My biggest question is why does EXECUTE as 'dbo' or OWNER not work ? How can i get permission errors when it executes as dbo ?

So i make QueueReader dbo of the database and i still get the user doesnot have permission error, so it seems only sysadmin can select from the sys. views, however i am still stumped , since the dbo of the database is a sysadmin. If i login as the user who is the dbo i can execute it. But if i say execute as dbo i get the permission error, i am totally stumped ?

Thanx

|||

Ok so it seems that if i reference sis.dm views in my activated sp i have to make the execute as user a sysadmin, or grant that user select on the sys.dm views , is this correct?

However i am still interested to know why execute as dbo does nto work when the dbo is also a sysadmin?

Thanx Remus you rfeedabck will be greatly appreciated.

|||

Hi Remus

Lastly, it also mentions in Roger Wolters book that if you do not stipulate execute as for your activated sp it will execute in the backgrouond as a sysadmin. However if i do not stipulate execute as i still get the permission error ? So not sure what is happening there?

Thanx

|||

Execute as user is restricted to database context. sysadmin is a server context concept. An untrusted database cannot extend the context to the server. This topic is covered in my blog in these posts: http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx, http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx and http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The simplest solution is to mark the database as trustworthy.

HTH,
~ Remus

|||

Thanx Remus

The links were worth reading and the trustworthy worked.

Just as a matter of interrest , what is the best practice in a prouction environment when the activation proc requires sys.dm views. Do you make the queue reader user a sysadmin (must be a bad idea), or make the dbo of the database a sysadmin, or simply grant the user permission to the sys.dm views (probably best) ? In gerneral when the actvation proc must use these views what would you reccomend ?

|||

The safest option is code signing (as in the exaple in my link). Getting code siging righ is really hard, so if you can get away with trustworthy bit, is much more easier. sysadmin is not a requirement, the appropiate priviledges needed to be granted are AUTHENTICATE SERVER to the database dbo and VIEW SERVER STATE to the execute as user.

HTH,
~ Remus

|||

HI Remus

Great thanx , i thnk i will stick to trustworthy and the permission you specified, thanx very much

Activation not working

Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

?

Thanx

Hi there.

It seems the statement in my sp that has a permission problem is the get conversation group sattement.

So my activation on the queue does not work when i stipulate execute as 'dbo' or OWNER. I cannot assign execute as 'sa' i get the following error "The server principal "sa" is not able to access the database "OLTP1PRO" under the current security context". I have tried not to stipulate the execute as so taht it defaults to a sysadmin.

But i always get this error:

The user does not have permission to perform this action.

So i tried the following.

CREATE LOGIN QueueReader with password = 'T#ST123'

CREATE USER QueueReader for login QueueReader

grant receive on [ewx.co.za/Queue/ewx_sb_central_queue] to QueueReader

grant execute on dbo.CENTRAL_Queue_Processor to QueueReader

And chanegd the execute as to QueueReader, but is till get the same error.

I dunno what else to try, no matter hwat i do i cannot execute the get conversation group statement ?

Please help ?

|||anyone?|||

What error do you get if you manually invoke the procedure, from Management Studio, under the QueueReader user context?

execute as user 'QueueReader';

go

exec dbo.CENTRAL_Queue_Processor;

go

revert;

go

|||

Hi Remus

I get the same error as before. The user does not have permission to perform this action. Error number 297.

I have done more investigation, judging by the erro line number line it seems that the QueueReader user cannot read from

sys.dm_broker_activated_tasks.

However i am expecting these errors, the activated stored procedure has many inserts etc. SO basically i want the activated sp to run as dbo, i do not want to have to setup alot of permission for the user.

My biggest question is why does EXECUTE as 'dbo' or OWNER not work ? How can i get permission errors when it executes as dbo ?

So i make QueueReader dbo of the database and i still get the user doesnot have permission error, so it seems only sysadmin can select from the sys. views, however i am still stumped , since the dbo of the database is a sysadmin. If i login as the user who is the dbo i can execute it. But if i say execute as dbo i get the permission error, i am totally stumped ?

Thanx

|||

Ok so it seems that if i reference sis.dm views in my activated sp i have to make the execute as user a sysadmin, or grant that user select on the sys.dm views , is this correct?

However i am still interested to know why execute as dbo does nto work when the dbo is also a sysadmin?

Thanx Remus you rfeedabck will be greatly appreciated.

|||

Hi Remus

Lastly, it also mentions in Roger Wolters book that if you do not stipulate execute as for your activated sp it will execute in the backgrouond as a sysadmin. However if i do not stipulate execute as i still get the permission error ? So not sure what is happening there?

Thanx

|||

Execute as user is restricted to database context. sysadmin is a server context concept. An untrusted database cannot extend the context to the server. This topic is covered in my blog in these posts: http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx, http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx and http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The simplest solution is to mark the database as trustworthy.

HTH,
~ Remus

|||

Thanx Remus

The links were worth reading and the trustworthy worked.

Just as a matter of interrest , what is the best practice in a prouction environment when the activation proc requires sys.dm views. Do you make the queue reader user a sysadmin (must be a bad idea), or make the dbo of the database a sysadmin, or simply grant the user permission to the sys.dm views (probably best) ? In gerneral when the actvation proc must use these views what would you reccomend ?

|||

The safest option is code signing (as in the exaple in my link). Getting code siging righ is really hard, so if you can get away with trustworthy bit, is much more easier. sysadmin is not a requirement, the appropiate priviledges needed to be granted are AUTHENTICATE SERVER to the database dbo and VIEW SERVER STATE to the execute as user.

HTH,
~ Remus

|||

HI Remus

Great thanx , i thnk i will stick to trustworthy and the permission you specified, thanx very much

Activating the Queue

Hi,

I have created Queue with the following syntax. But it is not getting activated itself. What I have to do to get it activated itself, and what could be the frequency by default.

CREATE QUEUE NewCustomerQueue
WITH ACTIVATION
(PROCEDURE_NAME = prProcessNewCustomers,
STATUS = ON,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF)
GO

If I execute the prProcessNewCustomers procedure manually it is showing that the Queue has been activated. What change I have to make in the syntax to get it activated itself.

Actually I have two scenarios in my requirement,

1. One Queue processing immediately when it receives data (Order Processing)

2. Another Queue, Process when the server is idle i.e., off-peak time (for mailing)

What syntax I have to use for these.

Please help.

Thanks in advance

Babu

Activation can be turned on and off using ALTER QUEUE

ALTER QUEUE NewCustomerQueue WITH ACTIVATION (STATUS = ON|OFF);

So in your first scenario you can keep activation on always. In your second scenario, you can keep it off by default and turn it on whenever desired.

Activation launches the specified stored procedure in the background upon receipt of incoming messages. If the procedure cannot keep with the rate of incoming messages, it can launch concurrent instances of the stored procedure (you need to set max_queue_readers > 1 for that).

Hope that helps,

Rushi

|||

Hi Rushi,

Thanks for your reply.

I have created the Queue with the ACTIVATION (STATUS = ON) only.

Currently I'm trying with a typical example what I got from some books. When a new customer is added, the trigger in the Customer table will "BEGIN DIALOG CONVERSATION" and send a message. I think when a message is sent, the Queue should get activated. It is not happening in this case. What could be the reason.

Please help.

Regards

Babu

|||

Hi Babu:

First make sure the trigger runs by checking in query analyser. Check the sql profiler to see whats the errror your getting.

Pramod

|||

Do you see the message in the target queue?

select * from [target queue]

(where [target queue] is the name of your target queue)

Rushi

|||

Hi Rushi, Pramod,

Thank you both for the responses.

The problem was occured because the 'Idle CPU Condition' was not defined.

I'll come back with new doubts . Thank you once again.

Regards

Babu

Activated Proc not firing.

Hi There

I just had the following scenario.

I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.

I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.

So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.

For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.

Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.

However the activated proc still did not fire.

I had to disable and re-enable activation for it to work.

What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?

And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?

Thanx

I had a similar issue...and I am trying to get a resolution on this. In my scenario, it was a new service broker startup (complete rebuild of queues from build scripts that were working in our development database environment)...We have about 30 queues in which all but 1 worked fine. When I went to look to see what happened, the queue was up and activation was on and the messages were sitting in the target queue. When I manually executed the activation stored procedure everything was successful and processed fine (no errors). This is not the first time this has happened, and after it is manually executed the activation seems to occur without issue from that point forward. I am just kind of concerned that when this system is in production and they have to take it down to perform maintenance, this will happen again and since nothing is really "wrong" with the queue, monitoring this is fairly difficult.

Any ideas or suggestions?

|||

When you run into this problem, look at sys.dm_broker_queue_monitors, sys.dm_broker_activated_tasks and sys.service_queues views. We need the content of these views to diagnose the issue.

HTH,
~ Remus

|||

Hi Remus

Ok next time i will get that information.

Thanx

Activated Proc not firing.

Hi There

I just had the following scenario.

I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.

I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.

So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.

For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.

Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.

However the activated proc still did not fire.

I had to disable and re-enable activation for it to work.

What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?

And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?

Thanx

I had a similar issue...and I am trying to get a resolution on this. In my scenario, it was a new service broker startup (complete rebuild of queues from build scripts that were working in our development database environment)...We have about 30 queues in which all but 1 worked fine. When I went to look to see what happened, the queue was up and activation was on and the messages were sitting in the target queue. When I manually executed the activation stored procedure everything was successful and processed fine (no errors). This is not the first time this has happened, and after it is manually executed the activation seems to occur without issue from that point forward. I am just kind of concerned that when this system is in production and they have to take it down to perform maintenance, this will happen again and since nothing is really "wrong" with the queue, monitoring this is fairly difficult.

Any ideas or suggestions?

|||

When you run into this problem, look at sys.dm_broker_queue_monitors, sys.dm_broker_activated_tasks and sys.service_queues views. We need the content of these views to diagnose the issue.

HTH,
~ Remus

|||

Hi Remus

Ok next time i will get that information.

Thanx