SQLTeam.com | Weblogs | Forums

Permissions for DTS in Agent

sql2014

#1

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


#2

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


#3

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

Greg


#4

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

Greg


#5

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.