Monday, July 18, 2011

How to configure Service Acount to run Stored Procedure that run SSIS Package without sysAdmin


You only need to follow these step then you can execute a SSIS Package from Stored procedure.

Enable xp_cmdshell option in the DB (assume until you and your system Admin comfortable with this)

        EXEC sp_configure 'xp_cmdshell', 1
       
RECONFIGURE

 Grant Access Permission To Service Account.
  GRANT exec ON xp_cmdshell TO [MyDomain\ServiceAccount]

 Create create a proxy credential for a Windows account
  
EXEC sp_xp_cmdshell_proxy_account ' [MyDomain\ServiceAccount]', 'MyPassword';
GO


 Create Credential

create credential ##xp_cmdshell_proxy_account## with identity = 'MyDomain\ServiceAccount', secret = 'password' 


you might get this error when you call the stored procedure.

"error An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'.

you can fix this error by following this link  http://grumpydba.blogspot.com/2010/07/xpcmdshell-raises-error-call-to.html 

 

No comments:

Post a Comment