SQLTeam.com | Weblogs | Forums

Permissions for DTS in Agent

I created a package for exporting data from one instance of SQL Server and putting it in a table in the local database. The package runs fine in the Export Wizard. I have tried saving it both to the file system and in SQL server. When I run it from an agent step it fails ever time.

In the SQL Server log the error is: Login failed for user 'myusername'. Reason: Password did not match that for the login provided.

When I save the DTS package I "encrypt sensitive data with a password" and I supply that password in the Agent Step Dialog.

In the Windows NT log I get:
Date 2/25/2016 3:59:55 PM
Log Windows NT (System)

Source Microsoft-Windows-DistributedCOM
Category (0)
Event 10016
User NT SERVICE\SQLSERVERAGENT
Computer BIserv01

Message
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{806835AE-FD04-4870-A1E8-D65535358293}
and APPID
{EE4171E6-C37E-4D04-AF4C-8617BC7D4914}
to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

I looked up the CLSID in the registry and found it was Microsoft.SqlServer.Dts.Server.DtsServer. I read that I needed to add permissions in the DCOM Config. In there I find DTS Package Host and DTS Task Host. I'm logging in to SQL server with an SA account and not Windows Authentication. So I'm not sure who I should be adding permissions for. I tried my Windows login, with no luck. I try adding NT SERVICE\SQLSERVERAGENT, but it is not found when I click on Check names.

I'm not really sure what is going on here. Any help is appreciated. I've recreated this a half dozen times. There is no way I am typing my password wrong ever time.

Greg

IN the job, what have you set for "Run Job As"?

The only option for Run As is SQL Server Agent Account and that is selected.

Greg

I changed it to Windows Authentication for the local db part and that fixed it.

Greg

For anyone finding this post by Googling what DCOM the CLSID or APPID corresponds to; try the 'Microsoft SQL Server Integration Services 1x.0' DCOM which uses the {EE4171E6-C37E-4D04-AF4C-8617BC7D4914} Application ID.