We ran into a situation where SQL jobs started failing on a SQL server in a DMZ domain seemingly out of the blue. As background, we have a situation where:

• Domain-A trusts Domain-B, but Domain-B doesn’t trust Domain-A (This is common in a DMZ configuration)
• SQL Server is installed on a Domain-A machine (This is not that common, but it is possible)
• The SQL Server (and agent jobs) services are running on a Domain-A account.

We started receiving information that specific jobs which were running under credentials from Domain-B:

JOB RUN: ‘{jobname}’ was run on {date/time}

DURATION: 0 hours, 0 minutes, 0 seconds

STATUS: Failed

MESSAGES: The job failed. Unable to determine if the owner (Domain-B\{Username}) of job {jobname} has server access (reason: Could not obtain information about Windows NT group/user ‘Domain-B\{Username}’, error code 0x5. [SQLSTATE 42000] (Error 15404)).

Digging into this alert ran into information that this is an access denied message (http://www.windows-tech.info/15/944bdabc733a57e3.php).

On the web the recommendation to address this is:  “In order to work, SQL Server should be running under a Domain-B service account, otherwise it is very likely that Domain-B will not accept the token from the service and fail.” (taken from http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/841a5446-6689-4612-8629-5029a341a77e/). 

For our environment however what we found was a little more interesting. There had been an account defined on both Domain-A and Domain-B with the same name and the same password. This account was using pass-through authentication to make this work. Upon digging into both of these accounts (Service account name in Domain-A and the same Service account name in Domain-B) we found that the account in Domain-A had been locked out! We unlocked the account (and started logging information on this account going forward to trap any failed logons or lockout situations).

Summary: If you have a DMZ domain, SQL installed in the DMZ and agent jobs start failing? Check to see if you are using pass-through authentication and if both accounts are not either password expired or disabled.