Showing posts from October, 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_Re…