October 17, 2008 at 10:05 am
Hi, I'm completely lost here... I have a SQL Server Agent job that execute a SSIS package. The same job works perfectly at on SQL server but errored out at the other with the "system error: Logon failure: unknown user name or bad password". As far as I can tell, both are setup the same. Here is my setup:
1. Create a credential AccountName to use the credential of a domain account (domain\AccountName).
2. Create a proxy AccountName that utilize the credential created from step 1.
3. In the "Run As" of the SQL Server Agent job step, use the proxy created from step 2.
When I executed the SQL Server Agent job, it prompted failed at one server with this unknown user name or bad password message. I know for sure that the user exists and the password is correct. It failed "before" executing the package. It acts like the SQL Server Agent Service account is unable to use the security context of the proxy account. As I said, the same job works fine at another server. The problem is I cannot find why. The account is sysadmin at both SQL Servers.
Could someone shed some light? Thank.s
October 17, 2008 at 5:57 pm
Hey can you post the exact error that you receive while executing the job.
I used to have the same kind of issue .......it used to say ...teh file cannot be found and XML node an dpassword something.........
One thing make sure that on the server that fails, sql agent ( under which the jobs run) have all the rights that it needs to perform in SSIS.
ALos, make sure all the objects that SSIS is using u have on the server, and also enough permissions to do the work for sql agent.
My case was. one of the objcet was missing, and before runnning the job, it was trying to validate things, and thrwoing errors before executing the ssis.
Other time, sqlagebt had no enough permission to delete and archive files........
this was one of my error and if u look at the error it talks all about something............
Executed as user: ADHCSCINT\sqlagt. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:03:20 PM Error: 2008-10-16 16:03:20.68 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-10-16 16:03:28.10 Code: 0xC002F304 Source: Rename and Archive flat file File System Task Description: An error occurred with the following error message: "Access to the path is denied.". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:03:20 PM Finished: 4:03:28 PM Elapsed: 7.547 seconds. The package execution failed. The step failed.
but the actual solution was to give enough rights to sqlagent to move, rename and delete files.
October 19, 2008 at 5:35 am
if running under a sql agent job as sql agent (i.e. a user other than the user who created the project) this is well known. How does the sql agent run the job sucessfully on the other server?
are you using xml config file, server roles\etc, registry or environment variables?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 20, 2008 at 8:05 am
There was not much message to the error. Here it is:
Unable to start execution of step 1 (reason: Error authenticating proxy SDRGE\svcBOKPosPay, system error: Logon failure: unknown user name or bad password.). The step failed.
If I should look at some other places for messages, please let me know...
This proxy account (SDRGE\svcBOKPosPay) is sysadmin on SQL Server database level. It also belongs to administrators group on the server level. So I don't know what else it might need...
The process is setup basically the same. Same SSIS package, same procedure to create all the steps, etc. The proxy account actually has less privilege on the other server that succeeds as it is not a admistrator group member on the server.
I am completely lost... Only thing I can say is that the setup somwhere along the way is different between the two servers, but I cannot figure out where and what...
October 20, 2008 at 8:13 am
Looks like the issue is Proxcy Account.
I had the same issue for sql agent. My DBA was able to figure out the problem. Though they belong to adminstrator group, there might be some explicit kind of some deny permission. One step you can do if possible is Give all th ehighest level of permission, and slowley test each time by removing few permiisons. Or make both the environment exactly the same...........
October 20, 2008 at 8:26 am
Um. I am not sure what "Give all th ehighest level of permission".. What else can I give? Thanks.
November 5, 2008 at 2:38 pm
I am having the same problem.
This syntax runs fine with my other SQL server except for my newly installed SQL server. I check all the permission and they all the same.
EXEC master..xp_cmdshell 'MKDIR Servername\test'
Error:
Logon failure: unknown user name or bad password.
Please help...
January 28, 2009 at 11:46 pm
I am also facing the same issue...but my scenario is little bit funny .....the sql server job is running some few days back perfectly but the same job with the same priveleges without changing any information.........it is not working now ......
am completely lost .......please help me out this problem....
the error am getting is .....
Error authenticating proxy.system error: invalid logon bad username or password
February 11, 2009 at 1:52 am
On the machine where it does not work, you are probably running the SQL Agent as a user which is not the default "Local System Account", and on the machine where it DOES work, you are running Agent as local system, right?
I just fixed this by going back to local system for Agent and running steps with proxies when needed. It seems like the domain service account we used for running Agent is not allowed to impersonate the proxy, but the local system account is.
Probably also solvable by allowing the domain service account to delegate.
May 28, 2009 at 2:46 pm
Hi Friends,
I got same problem today for one of our job after resetting password of a windows account. This job to run SSIS package was running fine till we changed the password. After resetting the password we got following error:
Unable to start execution of step 2 (reason: Error authenticating proxy REDMOND\***ADMIN, system error: Logon failure: unknown user name or bad password.).
I never used Proxy before. After some googling & trial & error, we tried changing the password of respective 'credential' [Security -> Credential] of this proxy. 🙂 It worked for us.
Hope my experience helps someone else !!!
September 22, 2010 at 1:22 pm
My issue was trying to have a DTS package connect to two remote databases and transfer data between the two of them (not on the server running the package). Before setting up a proxy, I would get errors when the job tried to run under the local server system account, which of course, has no privileges on any other server.
I followed all the steps to create a proxy account for a domain user with a password that does not expire. I still got the error: I would get the logon failure message.
After reading the last poster's message, I realized (thank you) that I had failed to go into the credential to change the password to the domain user account.
Resetting the password indeed works.
The SQL for creating a credential is as follows (got this from "How to Schedule and run a SSIS package (DTS) Job in SQL Server 2005." on CodeProject.com by hong wei li):
USE master
GO
CREATE CREDENTIAL [SSISExecCredential] WITH IDENTITY = domain\username', SECRET = 'WindowLoginPassword'
USE msdb
GO
sp_add_proxy @proxy_name = 'SSISExecProxy', @credential_name = 'SSISExecCredential'
GO
sp_grant_login_to_proxy @login_name = 'ssisexec', @proxy_name = 'SSISExecProxy'
GO
sp_grant_proxy_to_subsystem @proxy_name = 'SSISExecProxy', @subsystem_name = 'SSIS'
GO
Note that you can use any proxy name, credential name and user name you want. The choices above were just easy for me to implement and track.
December 21, 2011 at 3:56 pm
This happened to me as well, and it happened because the AD password changed.
By going into Enterprise Manager > Server > Security > Credentials, and opening up the proxy account name and updating the password, these Jobs worked again.
December 6, 2012 at 10:57 am
I know this is an old post but needed to update. i just experienced this same error (only on 2008) and changing to the local system account for the agent service resolved the issue.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 10, 2013 at 1:36 am
I have the same problem -
Unable to start execution of step 1 (reason: Error authenticating proxy Domain\WindowsID, system error: Logon failure: unknown user name or bad password.). The step failed.
> Have tried changing the password, but did not resolve the error
> The indivual package is runing fine using multiple user IDs outside the SQL job
> I am using a proxy with credential of the ID having access and who is able to execute the package outside the Agent Job
> Have provided sa priviledge to the agent service account
Please help.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
July 10, 2013 at 3:12 am
Additionally, changing to local system account is out of scope for me.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply