August 31, 2012 at 10:30 am
Hi ,
I have a package deployed on SSISDB (the new concept in MS SQL 2012, SSIS catalogs). I have t-sql code in which i will be able to execute SSIS package in SSISDB with no problems. But if i place the same t-sql code inside of a procedure which will be called by a service broker , the code is not executing.
I am using the following code to execute a package in the SSISDB catalog
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'LoadToABC.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'ABC', @project_name=N'LoadToABC',
@use32bitruntime=False, @reference_id=Null
DECLARE @var0 NVARCHAR(200) = N'D:\MyData\SampleText20120830100001.txt'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N'strFileName', @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
This code executes if run it alone or placed in a regular stored procedure , but not executes if i palce this same code inside of a procedure which is being called/executed by a service broker call like this:
CREATE QUEUE dbo.SampleQueue WITH STATUS=ON, ACTIVATION
(STATUS = ON, MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = spMessageProcSample, EXECUTE AS OWNER);
The problem occurs if we call the SSIS catalogs inside a proc which will be calling through a service broker queue.
I am running all these steps on my local instance of SQL SERVER 2012 in which i am the administrator.
Please advice where i am doing wrong ?
Thanks,
Kumar
August 31, 2012 at 11:18 am
The service-broker procedure or executor needs to be authorized to execute these commands.
And if it is running from a database other than your [SSISDB], then it will need either Cross-Db chaining or Certificate authorization to span both databases, and then that other database will need to be set to TRUSTWORTHY to make either of those work as well.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2012 at 11:45 am
Hi RBarryYoung,
Thank you for replying. Yes the PROC executing/calling by Service broker is on different database (FileTableDB).
While i see the server log it is:
The activated proc '[dbo].[spMessageProcSample]' running on queue 'FileTableDB.dbo.SampleQueue' output the following: 'The server principal "fl\user2012" is not able to access the database "SSISDB" under the current security context.'
Do i need to use the password(/key) that i used when i setting up the SSISDB catalog/database to connect to FileTableDB database ?
Can you please explain a little more.
Thanks,
Kumar
August 31, 2012 at 12:16 pm
kumar-378458 (8/31/2012)
...While i see the server log it is:
The activated proc '[dbo].[spMessageProcSample]' running on queue 'FileTableDB.dbo.SampleQueue' output the following: 'The server principal "fl\user2012" is not able to access the database "SSISDB" under the current security context.'
Do i need to use the password(/key) that i used when i setting up the SSISDB catalog/database to connect to FileTableDB database ?
No, the problem is exactly what I said above. You need to set it up to use either Cross-DB chaining or Certificate authorization so that it can get into [SSISDB] when running as an activated procedure. Both of those option also require that the source database ([FileTableDB]) be set to TRUSTWORTHY.
As the setup for either of those is involved and as I have to leave for an airplane in about 10 minutes, I am going to have to let someone else explain the details.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 4, 2012 at 10:04 am
Hi RBarryYoung,
As you suggested I tried with Cross-db changing setup. But no luck. I got the same error. Can you please explain step by step code to do this ? I think I am doing somewhere wrong.
Thanks,
kumar.
September 4, 2012 at 12:58 pm
kumar-378458 (9/4/2012)
Hi RBarryYoung,As you suggested I tried with Cross-db changing setup. But no luck. I got the same error. Can you please explain step by step code to do this ? I think I am doing somewhere wrong.
Thanks,
kumar.
Do both databases have the same owner? I forgot to mention it, but practically-speaking, this is a requirement also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 4, 2012 at 2:00 pm
Yes RBarryYoung. I only (with owner "fl\user2012" as windows authenticated , admin access ) created/setup everything and i am the owner of all the objects.
Thanks,
kumar
September 4, 2012 at 6:20 pm
Is CrossDB chaining set on both databases? Or is it set for the whole Sever?
And are you still getting the same error?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2012 at 7:20 pm
kumar-378458 (9/4/2012)
Yes RBarryYoung. I only (with owner "fl\user2012" as windows authenticated , admin access ) created/setup everything and i am the owner of all the objects.
Could you define "admin access"? Do you mean sysadmin on the server or ssis_admin in the SSIS Catalog? Either of these should be sufficient permissions to execute the SSIS stored procedures.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply