7

I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages.

These packages have been running for years and the associated steps run via a Proxy Account (PackageExecutor). PackageExecuter is associated with a SQL Credential that was a former domain admin account.

Soon, the domain associated with this admin account is going to be shutdown. I have to use a new account, on a new domain, as the admin account associated with my roxy, PackageExecutor. When I created a new Credential for the new Admin account and associated it with PackageExecutor, I started to get the following error when I tried to run one of my SQL jobs as a test:

Unable to start execution of step 1 (reason: Error authenticating
proxy *Domain\Admin_Account*@*fully.qualified.domain.com*, system
error: Logon failure: unknown user name or bad password.).  The step
failed.

If I'm understanding this reasonably explicit error, what it's telling me is that the Credential accounts, associated with my proxy is in correct. How do I validate this?

I know that this account is legitimate-- I've already associated it with every associated server group, I've made it a sysadmin user on the server.

What could be causing this problem?

To be clear, I haven't mis-typed the account name or the password associated with the Proxy Credential. However, when I entered the account name Domain\Admin_Account and clicked the Check Names button, SQL Server automatically transformed the User ID to the fully-qualified version. I'm not sure if this has anything to do with this problem.

I'm at a bit of a loss. I've given my credential account full access to everything that I can think of. What might I need to do to get this to work?

UPDATE

Sorry, one more quick mention. I've found this MSDN kb article. Resolution method #1 is what I've been doing for years. The others don't seem to apply, or I'm missing something. Any tips or clarification would be beneficial.

0
5

Just in case others have gotten here for the same reason. Make sure that the account your proxy/credential is using is a user in its default database in SQL Security.

We went through all the other suggestions (checking that the password hasn't changed, switching to a local service acct for SQL Agent User, rebuilding the credentials and proxy, etc.) and until we made sure the account was a user in its default DB, for some reason, that was causing it to pass a Null SID when it was trying to authenticate to AD.

2
  • 1
    In the windows event log I'm getting Logon Audit Failure with Security ID: my account, but then below Account for which Login Failed: Security ID: NULL SID. I've fiddled with user mapping with no success. If I set the SQL Agent to run as this account directly, it also has problems authenticating – Nick.McDermaid Feb 13 '17 at 0:49
  • I discovered that this server has a duplicate SID (suing PsGetSid) but I don't know if that is an issue or not – Nick.McDermaid Feb 13 '17 at 1:27
3

A comment on this old question by the question author, RLH worked for me after I tried all the other answers, so I am adding it as an answer:

I believe I have solved this problem but I don't know why. In short, instead of using SSMS on my PC, I remoted into the server logging in with the account associated with the new credential. I removed the credential, created it again (this time SQL Server didn't complain at the @domain.com) and then I attempted a test execution of one of the jobs. It worked! Apparently this changed something and helped SQL Server resolve the account.

2
  • I tried this solution and the @ format was not accepted (got an error when creating the proxy). So I used the \ format. It didn't make any differenve to my original error – Nick.McDermaid Feb 13 '17 at 0:19
  • Worked for me, very strange... – David Rogers Jun 8 '18 at 15:50
3

Try restarting the Sql Server Agent first. I had a similar issue happen and went as far as recreating the proxy and credentials but it still did not work. We had a crash dump that occurred yesterday and apparently broke the connection and wouldn't recognize the credentials. Restarting the agent and voilà, it worked again.

0
1

I doubt there are many people that will make the same mistake as I did...

I was connected in Management Studio to the wrong server, so I kept typing in the username and password and getting this error. I was actually setting up the job on the wrong server too.

So double check the SQL Server instance you're connected to, is the one on which the credential exists.

0
1

I have a Credential running under a generic (AD) domain account. I changed the password last week. That broke the SSIS jobs. Updating the password fixed it.

1

I was getting the same error, i was using a proxy account with my own database security credentials, and the password had changed. Once i changed it in the security ->credentials, the package started working..

0

My issue was that I had the SQL Agent Service running under an account that no longer existed. Once I updated the agent service with a valid service account(See this) and restarted it my issue was resolved.

0

Community wiki answer originally left as a comment by the question author:

I believe I have solved this problem but I don't know why.

In short, instead of using SSMS on my PC, I remoted into the server logging in with the account associated with the new credential. I removed the cred, created it again (this time SQL Server didn't error at the @domain.com) and then I attempted a test execution of one of the jobs.

It worked?! Apparently this changed something and helped SQL Server resolve the account.

0

In my case the SQL Server agent was running under a service account, it was specified in the format - "abc@Xyz.com" (username@domainName.com) , I changed the format to "xyz\abc" i.e. DomainName\Username and proxy worked fine. Strange indeed. I am still wondering what changed.

Error Message reported earlier: Unable to start execution of step 1 (reason: Error authenticating proxy , system error: The username or password is incorrect ) The step failed.

1
  • Had the same issue and none of the above worked. Finally fixed it. There is something wrong (still unknown) with the domain account we used to start the SQL Agent job. Changed the account, restarted and all proxy accounts on server now working. – Garrett Devine Nov 12 '19 at 12:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.