Wednesday, October 2, 2013

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!!!

MYSQL:::Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables

  Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables Introduction: In the dynamic landscape of database management...