Labels

Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.




Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account.  The step failed.


  1. Change Job_Owner to Sa, If it's not possible then you have to try 2nd option Create a proxy account to execute SSIS package
  2.  Create a Proxy account for SSIS package and assign same while executing a job.



USE MASTER
GO
CREATE CREDENTIAL [SSISProxyCredentials]
WITH IDENTITY = N'Any Sysadmin Domain Account',
SECRET = N'Password'
GO
-----------------------------------------------------

USE msdb
GO
--Drop the proxy if it is already existing
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'SSISProxy')
BEGIN
EXEC dbo.sp_delete_proxy
@proxy_name = N'SSISProxy'
END
GO
--Create a proxy and use the same credential as created above
EXEC msdb.dbo.sp_add_proxy
@proxy_name = N'SSISProxy',
@credential_name=N'SSISProxyCredentials',
@enabled=1
GO
--To enable or disable you can use this command
EXEC msdb.dbo.sp_update_proxy
@proxy_name = N'SSISProxy',
@enabled = 1 --@enabled = 0
GO
---------------------------------------------------------
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
@proxy_name=N'SSISProxy',
@subsystem_id=11 --subsystem 11 is for SSIS as you can see in the above image
GO
--View all the proxies granted to all the subsystems
EXEC dbo.sp_enum_proxy_for_subsystem

------------------------------------------------------
USE msdb
GO
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are not allowed to use any proxy
EXEC msdb.dbo.sp_grant_login_to_proxy
@proxy_name=N'SSISProxy'
,@login_name=N'Any nonsysadmin account'
GO
--View logins provided access to proxies
EXEC dbo.sp_enum_login_for_proxy
GO
------------------------------------------------------
--EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SSISPackageCall',
--@step_id=1,
--@cmdexec_success_code=0,
--@on_success_action=1,
--@on_success_step_id=0,
--@on_fail_action=2,
--@on_fail_step_id=0,
--@retry_attempts=0,
--@retry_interval=0,
--@os_run_priority=0, @subsystem=N'SSIS',
--@command=N'/FILE "C:\Package.dtsx" /CHECKPOINTING OFF /REPORTING E',
--@database_name=N'master',
--@flags=0,
--@proxy_name = N'SSISProxyDemo';

2 comments:
Write comments
  1. Thanks for the article, but regarding the 'Any Sysadmin Domain Account' in the CREATE CREDENTIAL statement:

    This must not be a SysAdmin Account (this would make the whole concept unsecure again, since the SSIS packets would run with SysAdmin permissions again).

    You can create a "normal" (windows) login, add it to the ssis_admin role in the SSIS DB and GRANT him the necessary permissions on all databases that are accessed (read or written) in the SSIS package.

    And of course you could create multiple proxy accounts that are used to execute different packages (which access different databases / need other permissions)

    Example:
    USE master
    CREATE LOGIN [MyDomain\SSISProxy] FROM WINDOWS
    -----------------
    USE SSISDB;
    GO
    CREATE USER [MyDomain\SSISProxy] FROM LOGIN [MyDomain\SSISProxy]
    go
    ALTER ROLE ssis_admin ADD MEMBER [MyDomain\SSISProxy]
    GO
    -----------------
    USE myDB
    GO
    CREATE USER [MyDomain\SSISProxy] FROM LOGIN [MyDomain\SSISProxy]
    go
    GRANT SELECT ON dbo.SourceTbl TO [MyDomain\SSISProxy]
    GRANT INSERT ON dbo.TargetTbl TO [MyDomain\SSISProxy]
    go
    ----------
    USE MASTER
    GO
    CREATE CREDENTIAL [SSISProxyCredentials]
    WITH IDENTITY = N'MyDomain\SSISProxy',
    SECRET = N'the password for the windows account MyDomain\SSISProxy'
    GO
    -- the rest of the script above

    ReplyDelete

Please do not enter spam links

Services

More Services