package – The Data Queen

There have been a number of times over the years when I have had to create a Proxy user in SQL Server in order to provide needed access to connections and locations being used in an SSIS package. Sometimes the SQL Server Agent login account simply doesn’t and shouldn’t have the required permissions.

Before using a Proxy account, do check to see if the permissions issue isn’t just that the SQL Server Agent login account has been set up as ‘Local System’. If that is the case, see if you can change it to a domain account specifically created for SQL Server Agent purposes. Check Administrative Tools -> Services on the server where the SSIS SQL Server resides to see what login account the SQL Server Agent is mapped to.

The user mapped to the SQL Server Agent Service Account will need read/write permissions. If you do need to create a new domain login for the SQL Server agent, in SSMS go to Server-> Security (not database security) -> Logins -> left click New Login -> Search -> Locations button -> Entire Directory -> select main domain ->OK -> Sql Agent username-> Check Names button-> OK-> Server Roles-> sysadmin-> OK..

If it turns out that you need to create an SSIS proxy user, edit this script to use the correct username and password and run it to create the proxy user.

USE master 
GO
-- Create a proxy credential for xp_cmdshell.
EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\username', 'password';--SELECT  * FROM [master].[sys].[credentials]
-- Grant execute permission on xp_cmdshell to the SQL Server login account. 
GRANT exec ON sys.xp_cmdshell TO [DOMAIN\username] 
GO

-- Create a credential containing the domain account PowerDomain\PowerUser and its password
CREATE CREDENTIAL MyCredential WITH IDENTITY = N'DOMAIN\username', SECRET = N'password'
GO
USE [msdb]
GO
-- Create a new proxy called SSISProxy and assign the PowerUser credentail to it
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'MyProxy',@credential_name=N'MyCredential',@enabled=1
-- Grant SSISProxy access to the "SSIS package execution" subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'MyProxy', @subsystem_id=11
-- Grant the login testUser the permissions to use SSISProxy
EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'DOMAIN\username', @proxy_name=N'MyProxy'
GO

You will be able to see the proxy user in SSMS under SQL Server Agent. Is is in the SSIS PAckage Execution section because we added it to the SSIS subsystem in our code.

Now when you create your SQL Server Agent Job you can choose to run the SSIS package as your proxy user with the required permissions,rather than the SQL Server Agent account.

At times I like to capture certain errors and events that occur in a Script Task in an SSIS package, and include them in the package logging. In order to make this happen simply include the appropriate statements in your Script Task and turn on some custom logging within the package logging configuration.

Add a Dts.Log statments to your vb Script Task. For example:

            Dim dataBytes(0) As Byte
            Dts.Log("Did not find expected database", 0, dataBytes)

In order for this message to be included in the [sysssislog] table simply right click on the package Control Flow surface, and select Logging. Within the Containers window, drill down to your Script Task.

Check the box beside the Script Task until it has a black check mark, instead of a greyed out check mark. In the Providers and Logs tab select the log you want to write to.

On the Details tab select the Events you wish to log, and be sure to select the ScriptTaskLogEntry.

Click OK and you’re done. Your custom messages will be included in the package logs.

Tag: package

Creating a Proxy User to run an SSIS package in SQL Server Agent

How to Enable Custom Logging for an SSIS Script Task