Have you ever had an error when using a SQL Server Proxy Account?  I ran across a misleading error.  Let me qualify that a little.  It wasn’t misleading in the sense of it saying one thing and meaning another.  It was misleading because it’s one of those errors you see for multiple things and I happen to see this one fairly often.  Let’s look at the error first…

Unable to start execution of step1 (reason: Error authenticating proxy SERVER1\Administrator. System error: Logon failure: unknown user name or bad password.).  The step failed.

You can see from the error that it came from a SQLAgent job.  You can also see that the job step was using a proxy account.  The last part about “unknown user name or bad password” is the part I see most often in errors.  Before you read on to the next paragraph, what’s the first thing that comes to mind as to the cause of this error?  No seriously…stop reading…what’s your first instinct?

Did you guess that it was a bad password?  You didn’t stop reading and think did you?  That would be way too obvious and easy!  My guess?  A Kerberos double hop issue.  That did not turn out to be the case.  It’s not the most common error message for a Kerb issue as you’re more likely to see an “Anonymous User” or “SSPI Context” error, but it’s not uncommon.  So what’s the issue?

The issue turned out to be someone configuring the SQLAgent service account to use a UPN or User Principal Name.  As a DBA you may be wondering what that is.  My former life was Active Directory and the simple explanation is that it’s an account name in the format “User@Domain.com”.  All you really need to know is that SQL Server does NOT support the use of UPN names.

I want to point out two things here.  The first is the user name you see in the error message.  “Server1\Administrator” is called a SAM account name.  If it had been in a UPN format the issue would have been very obvious.  However, that account is the credential that the proxy account is using.  In fact the job should have executed under the security context of that account as opposed to the account the SQLAgent service account was running under.  Since the SQLAgent was set to run using a UPN name we failed before we ever got that far.  So  how did I figure out the issue?  You’ll recall that I said I thought it was a Kerberos error, so I went to look at the SQL Server Service account so I could check the SPNs and that’s when I saw the bad account name.

The second thing I want to point out is that SQL Server Configuration Manager will NOT allow you to use a UPN.  That means the account was mostly likely set using Services.msc.  This is just another example of why you should ALWAYS use SQL Server Configuration Manager to make account changes to your SQL services.