Clicky

I created a SSIS package in BIDS2008, created a job in sql server agent 2008 that calls the Package.
However I'm getting this error.. I also cretated credentials and a proxy.

Unable to start execution of step 1 (reason: Error authenticating proxy domain\svc_SqlDev, system error: Logon failure: unknown user name or bad password.).  The step failed.

Any ideas what this could be?
Thanks,

asked 05/12/2011 02:45

TSG954's gravatar image

TSG954 ♦♦


14 Answers:
Is the Package stored as a File or inside SQL Server?

As the package is executed by the SQL Agent asunder the proxy account, does that  user  "domain\svc_SqlDev" have all requred DB rights in SQL?
link

answered

lcohan's gravatar image

lcohan

The package is stored as a file.
What rights would be required for the domain\svc_SqlDev?
Thanks,
link

answered 2011-05-12 at 11:01:56

TSG954's gravatar image

TSG954

http://msdn.microsoft.com/en-us/library/ms189064.aspx

"Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context."
link

answered 2011-05-12 at 11:04:19

lcohan's gravatar image

lcohan

Right, I created a credential for it.
link

answered 2011-05-12 at 11:08:36

TSG954's gravatar image

TSG954

therefor you would need to grant sufficient rights on that account to perform whatever the package steps need to do.
link

answered 2011-05-12 at 11:09:26

lcohan's gravatar image

lcohan

This is what I had done. I created a user domain\svc_SqlDev in Active Directory.
Then, I created a Credential called SSISProxyCredentials and in the Identity put "domain\svc_SqlDev", and the password the same
I had given to that account.
Where am I going wrong?
link

answered 2011-05-12 at 11:09:52

TSG954's gravatar image

TSG954

You need to open SSMS and check that the account exists in SQL server under the Security - Logins and that it has sufficient rights against whatever DB objects the package steps need to perform.
link

answered 2011-05-12 at 11:14:19

lcohan's gravatar image

lcohan

I checked the Logins in Security and there is an account. My package dosnt connect to any db right now since i'm just in testing mode.
link

answered 2011-05-12 at 11:42:52

TSG954's gravatar image

TSG954

OK, do you have a password for the account?
If not you need to set a password for the Credential that is associated with the proxy. To do this, right click on the credential and select properties. Enter and confirm the password for the account.
link

answered 2011-05-12 at 12:00:21

lcohan's gravatar image

lcohan

Yes, I have done this.
link

answered 2011-05-12 at 12:05:44

TSG954's gravatar image

TSG954

Hm......
Because the package is stored as a file, can you navigate to that folder, double click the DTSX package and try to Execute it?
link

answered 2011-05-12 at 12:06:29

lcohan's gravatar image

lcohan

So the proxy is "Active to the following subsystems:" - "SQL Server Integration Server Packages"
AND the job step where you execute the DTSX file is set to "Run as" the proxy account right?
link

answered 2011-05-12 at 12:37:28

lcohan's gravatar image

lcohan

The proxy is set to "Active to the following subsystems:" AND the job step is set to "Run as" the proxy account right, correct.
As it turns out, I didnt need the proxy after all. I'm now running it as  SQL server Agent service account and its working.
I went the proxy route because I was getting an XML error before, which turned out to be that I was running the package from a different version,
i needed to place it in C:\Program Files (x86)\Microsoft SQL Server\100\DTS as as opposse to C:\Program Files\Microsoft SQL Server\100\DTS.

Thanks,
link

answered 2011-05-12 at 12:46:14

TSG954's gravatar image

TSG954

Switched methods and worked fine

link

answered 2011-05-12 at 12:49:59

TSG954's gravatar image

TSG954

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×3
×6
×1
×16

Asked: 05/12/2011 02:45

Seen: 325 times

Last updated: 12/17/2011 05:20