May 21, 2008 at 10:31 pm
I have migrated a SQL Server 200 DTS package into a SQL Server 2005 environment using the Data Transformation Services "Migration Wizard" in SQL Server Management Studio. The log from the migration wizard gives no errors, yet when I try to run the job which executes the Package in SQL 2005 I get the following error:
Non-SysAdmins have been denied permission to run CmdExec job steps without a proxy account. The step failed.
The original job was set up to run in MS SQL 2000 using a non sysadmin user, so the first error is understandable. However, I changed the owner of the original package firstly to 'sa' then to a windows user with 'sysadmin' rights and reimported the package.
Subsequent attempts to run the job still produce the same message.
Can anyone give me any clues as to why, OR alternatively, give me some help as to how to change the package within MSSQL2005 rather than changing the original package and re-importing, which doesn't seem to be working.
Thanks in advance
May 22, 2008 at 9:46 am
I think the problem is not so much who owns the package as it is who is running the job. If you want a login other than the one the runs SQL Agent to run the job, you have to use a SQL Agent Proxy. I got this to work by creating a security credential and a proxy that uses the credential for executing SSIS packages in job steps.
As an aside, once you've converted the DTS package to SSIS, you can use Business Intelligence Developer Studio to modify the package.
Greg
May 22, 2008 at 6:07 pm
Greg, thanks for the reply. The account that runs SQL Server and SQL Server Agent is a Domain account. I gave this account 'sysadmin' within SQL Server Security but the problem remains. Am I missing something from your reply? Is a proxy necessary if the Account that runs SQL Server Agent also has 'sysadmin'?
Cheers, Neil
May 22, 2008 at 6:31 pm
u can open the package from the Visual studio that installed with SQL 2005, and open project, then select the backage.
u can do what ever u want there..
..>>..
MobashA
May 23, 2008 at 10:06 am
Neil,
No, you wouldn't need a proxy in that case. When you look at the job history, does it say that the job step is executed as the login that runs SQL Agent? Maybe you could post the entire message you get when the job fails?
Greg
May 24, 2008 at 4:20 am
can u copy the step code and execute it manauly, so u can make sure about the problem in the step code or in the agent.
..>>..
MobashA
May 26, 2008 at 12:18 am
Greg, I solved it. The problem (at least in my environment) appears to be that BOTH the owner and the SQL Agent account must have admin rights. When I gave SYSADMIN to the account running SQL Server Agent, the owner of the job was a plain-user with database rights. This worked OK in SQL 2000, but doesn't seem to here. Simple solution, change the owner of the job to the same account as runs the SQL Serevr Agent. The package now executes OK. - Now I have to learn a bit about BI so that I can edit the package. The drive spec for the source text file in the package is different for my original server to the new server, so the package can't find the file.
Thanks for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply