SSIS : Credential & Proxy

1) Setting up a credential -

USE [master]
GO
CREATE CREDENTIAL [SSIS_Credential] WITH IDENTITY = N'Domain\SQLServiceAgent', SECRET = N'longcomplicatedpassword'
GO

2) Setting up a proxy -

USE [msdb]
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSIS_Proxy',@credential_name=N'SSIS_Credential', 
  @enabled=1

GO

3) Assigning proxy to be available to schedule SSIS jobs -

USE [msdb]
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'SSIS_Proxy', @subsystem_id=11
GO

NB : A subsystem_id of 11 is for SSIS package execution.


Database Journal : Proxy Accounts in SQL Server


Or all steps in one go -

USE [master]
GO

CREATE CREDENTIAL [SSIS_Credential] WITH IDENTITY = N'Domain\SQLServiceAgent', SECRET = N'longcomplicatedpassword'
GO

USE [msdb]
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSIS_Proxy',@credential_name=N'SSIS_Credential', 
@enabled=1
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'SSIS_Proxy', @subsystem_id=11
GO