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]
GO

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

USE [msdb]
GO

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

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

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

USE [msdb]
GO

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





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.

2 comments:

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