Credentials and Proxy creation Script in SQL Server 2008R2 or How to access subsystems by SQl Server ?

#How to create credentials and proxy account by script in SQL2008R2#

Script Uses: In SSIS package Migration and SQL server agent job to access outside file. 

Credentials are used for to allow sql server authenticated logins to access file source or any action outside sql server.

#Scripts for Credentials creation#

USE [master]
GO
-- Update the SECRET (password) in CREATE CREDENTIAL query before running script

IF NOT EXISTS (SELECT * FROM sys.credentials WHERE name = 'DBA_Report')
BEGIN
CREATE CREDENTIAL [DBA_Report] WITH IDENTITY = N'prod\$sa-ddh', SECRET ='sssss'
END
GO

-->Windows login name:prod\$sa-ddh
-->Secret: specify the password

#Create a proxy account named ‘DBA_Report’to map the credentials ‘DBA_Report’#

-- Drop the proxy if it already exists

IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = 'DBA_Report')
BEGIN
  EXEC msdb.dbo.sp_delete_proxy @proxy_name = N'DBA_Report'
END
GO

-- Add the proxy
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'DBA_Report',@credential_name=N'DBA_Report',
        @enabled=1
GO




-- Grant proxy account to SQL Server Agent Sub-systems(subsystem_id=11) into SSIS PAckage Execution folder in Proxies.

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'DBA_Report', @subsystem_id=11
  
Hope you are happy in accessing sub systems like SSIS, Command shell and active x script!!!

Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)