I'm trying to run a SSIS package (dtsx) from inside an sql job (SQL Server agent). This works fine if the user running (run as) the step is a local admin on the server. If it's not, I get the error message "The package could not be loaded. The step failed". This happens even if the user has all possible serverroles such as "sysadmin" etc in SQL.
So, my question is, is there any way to load an SSIS package without being local admin on the machine? In case it is, what is needed?
regards Andreas
I'm not sure that this will solve the problem, but have a look at PROXYand CREDENTIALS - I was able to invoke a sample package using them,
using a user that was only a member of the USERS local group.|||
GethWho wrote:
I'm not sure that this will solve the problem, but have a look at PROXY and CREDENTIALS - I was able to invoke a sample package using them,
using a user that was only a member of the USERS local group.
here's a sample of what I did:
--code
--###################################################### describe
script
############################################################################
PRINT '>>> This script creates the LOGIN,
USER , CREDENTIALS and PROXY for INTEGRATION SERVICES SSIS PACKAGES
<<<'
PRINT '>>> This execution on server:
['+@.@.SERVERNAME+'] started at:
['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'
PRINT ''
PRINT '>>> Create Objects <<<'
PRINT ''
--################################################## Check and Drop
Existing
########################################################################
--################################################## CREATE Login
###################################################################################
SET NOCOUNT ON
USE [master]
IF NOT EXISTS (select 1 from sys.syslogins WHERE [Name] = 'LocalMachine\TestSSISUser')
CREATE LOGIN [LocalMachine\TESTSSISUser] FROM WINDOWS WITH DEFAULT_DATABASE = [MyDatabase]
--################################################## CREATE User and
Grant Rights on DBS
############################################################
USE [MyDatabase]
IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'LocalMachine\TestSSISUser')
CREATE USER [LocalMachine\TESTSSISUser] FROM LOGIN [LocalMachine\TESTSSISUser]
EXEC SP_ADDROLEMEMBER [DB_DataReader], [LocalMachine\TESTSSISUser]
EXEC SP_ADDROLEMEMBER [DB_DataWriter], [LocalMachine\TESTSSISUser]
EXEC SP_ADDROLEMEMBER [DB_DDLAdmin], [LocalMachine\TESTSSISUser]
GRANT EXECUTE ON [sp_dts_addlogentry] to [LocalMachine\TESTSSISUser]
USE [msdb]
IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'LocalMachine\TestSSISUser')
CREATE USER [LocalMachine\TESTSSISUser] FROM LOGIN [LocalMachine\TESTSSISUser]
EXEC SP_ADDROLEMEMBER [db_dtsadmin], [LocalMachine\TESTSSISUser]
EXEC SP_ADDROLEMEMBER [db_dtsltduser], [LocalMachine\TESTSSISUser]
EXEC SP_ADDROLEMEMBER [db_dtsoperator], [LocalMachine\TESTSSISUser]
EXEC SP_ADDROLEMEMBER [SQLAgentOperatorRole], [LocalMachine\TESTSSISUser]
EXEC SP_ADDROLEMEMBER [SQLAgentReaderRole], [LocalMachine\TESTSSISUser]
EXEC SP_ADDROLEMEMBER [SQLAgentUserRole], [LocalMachine\TESTSSISUser]
--################################################## CREATE Credential
##############################################################################
USE [master]
IF NOT EXISTS (select 1 from sys.credentials WHERE [Name] = 'TestProxy')
CREATE CREDENTIAL [TestProxy] WITH IDENTITY = 'LocalMachine\TestSSISUser', secret = 't3st'
--################################################## CREATE Proxy
###################################################################################
USE [msdb]
DECLARE
@.proxy_name SYSNAME,
@.subsystem_name SYSNAME,
@.UserName SYSNAME
SET @.proxy_name = 'TestSSISUser'
SET @.subsystem_name = 'CmdExec'
SET @.UserName = 'LocalMachine\TestSSISUser'
EXEC sp_enum_proxy_for_subsystem @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name
IF @.@.ROWCOUNT = 0
EXEC SP_ADD_PROXY
@.proxy_name=@.proxy_name, @.credential_name='TestProxy'
EXEC sp_enum_login_for_proxy @.proxy_name=@.proxy_name, @.name=@.UserName
IF @.@.ROWCOUNT = 0
EXEC SP_GRANT_LOGIN_TO_PROXY
@.login_name=@.UserName,
@.proxy_name=@.proxy_name
EXEC SP_REVOKE_PROXY_FROM_SUBSYSTEM @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name
EXEC SP_GRANT_PROXY_TO_SUBSYSTEM @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name
--###################################################### end script
##################################################################################
PRINT '>>> This execution on server:
['+@.@.SERVERNAME+'] ended at:
['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'
--code
.. it isn't perfect - I couldn't find a way to do an existance check
for the PROXY to run the SP_REVOKE_PROXY_FROM_SUBSYSTEM only if it
PROXY had been gtranted.|||
There will be a reason for the package failing to load, and this is what you need to find out. Use the CmdExec step type.
Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)
