November 18, 2010 at 4:04 pm
Hi there,
I am trying to execute SSIS package through a stored proc. Stored proc is being called from a .NET app and is executed by SQL login. But in my stored proc, I am impersonating that user with Execute AS service account. Store proc is below. But I am getting the following error message when tried to exeucute it.
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1326'
Can somebody please help?
Proxy account has been setup as well and it works if I hardcode the invoice number in the SSIS pacakge and run it from there.
CREATE PROCEDURE [dbo].[proc_A] (@InvoiceNumber nvarchar(100))
WITH EXECUTE AS 'QUERT\svcicsadmsqlprod'
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SSISCommand nvarchar(1000)
SET @SSISCommand = N'DTEXEC /SQL "\SSIS_Export_Package" /SERVER "(local)" ' +
N'/SET "\Package.Variables[User::InvoiceNumber].Properties[Value]";"' + @InvoiceNumber + N'"'
EXEC master.dbo.xp_cmdshell @SSISCommand
November 19, 2010 at 2:26 am
What is the exact error that you get?
It may be a long shot, but when I execute the code in your sp with an invoicenumber of 1000 I get the following command:
DTEXEC /SQL "\SSIS_Export_Package" /SERVER "(local)" /SET "\Package.Variables[User::InvoiceNumber].Properties[Value]";"1000"
I'm not sure if it would help, but those "" around the package variable aren't necessary.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 19, 2010 at 6:08 am
The exact error is as follows:
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1326'
I don't think its a syntax issue there. Sounds like some kind of permission issue. App is accessing the SQL server through a SQL login (which doesn't have admin rights to execute xp_cmdshell) but in the sp I am using EXECUTE AS service account.
Any help is appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply