Monday, February 27, 2006

Fun with jobs and the public role and proxies

My colleague came across some interesting 'features' with SSIS in jobs.

  • When you execute a job and change the data sources the information is NOT stored and the next execution resets the connection information. Using a config file is definitely the way to go :-)
  • Proxy accounts with the public role assigned to it have a problem removing this role.

USE [master]

CREATE CREDENTIAL [myCredentials] WITH IDENTITY = N'svcaccount',
SECRET = N'xxxxxxxxx'

USE [msdb]

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'myProxy',
@credential_name=N'myCredentials', @enabled=1

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'myProxy',

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'myProxy',

USE [msdb]

EXEC msdb.dbo.sp_revoke_login_from_proxy @proxy_name=N'myProxy',

This results in the following error message: "Msg 14523, Level 16,
State 1, Procedure sp_revoke_login_from_proxy, Line 63 "public" has not been granted permission to use proxy "myProxy"."

Also notice the @enabled parameter in the sp_add_proxy call. We can't find this in the interface but when this parameter is set to 0 the proxy is disabled. The problem is that you CAN select
disabled proxies as the account to run the job with but the job fails on execution.


tv said...

Blood, sweat and tears just to bypass an earlier problem we've encountered with SSIS. As I was running through your "long-term memory", I noticed that you've never described that problem. It's not copyright protected material you know ;-)
But, no matter what they say, SSIS rocks!

WesleyB said...

I apoligize for not blogging the other problem :-) Maybe you can send me the details again so I can be your gateway to the world :-D