SQL Agent – CMDExec – ODBC – Oh My!

So, a vendor app sets up some SQL Agent jobs that call out to the OS to run an executable. This Executable then connects back to SQL and to some other systems to compute some values and store those. I didn’t write this app, and I didn’t design the system. If I did, I wouldn’t be letting them use SQL Server Agent as a Task Manager.

Current system has SQL Server Agent running under a domain level service account – lets say CORP\SQLAcct. The CMDExec calls go out as the user running SQL Agent to fire an executable. This Executable uses a System DSN set up to connect back into SQL Server. The DSN is configured to verify the authenticity of the login ID With Windows NT authentication using the network ID.

Queue the investigation… SQL Server log files are logging
2011-04-19 16:15:03.78 Logon Error: 18456, Severity: 14, State: 5.
2011-04-19 16:15:03.78 Logon Login failed for user 'SQLAcct'. Reason: Could not find a login matching the name provided. [CLIENT: local_ip]

over and over. Initially it doesn’t tell me anything, other than the only executable running under the SQLAcct user is SQL Agent. So, I stop the Agent. Blam, errors disappear. Start the agent, they start again. Now I involve the vendor, and I ask them to check their configurations and DSN settings. They report back that everything seems fine. So I decide to change the account that the Agent is running under – change to CORP\ServAcct and the errors follow, logged under ‘ServAcct’. Interestingly both domain accounts have access to the SQL Instance.

So my next step is that I create a SQL Login as SQLAcct with the domain service account’s password. Get a new error…
2011-04-19 16:16:52.20 Logon Error: 18456, Severity: 14, State: 8.
2011-04-19 16:16:52.20 Logon Login failed for user 'SQLAcct'. Reason: Password did not match that for the login provided. [CLIENT: local_ip]

Closer though!!! Some how the SQL Agent CmdExec call is not passing out the domain info, or the DSN call is not passing the domain info back to SQL Server as the executable runs. So I change the SQLAcct SQL Login to a blank password, and BLAM!!! no more connection errors.

Process Explorer shows me that when the executable is running, it’s running as the full domain/user, so I think that’s being passed out correctly. I think it’s the DSN configuration or runtime is not passing it back to SQL. I’ll have to get back with the vendor tomorrow and have them change their DSN’s, like I asked 2 days ago!

One thought on “SQL Agent – CMDExec – ODBC – Oh My!”

  1. I was suggested this web site by my cousin. I am not sure whether this post is written by
    him as no one else know such detailed about my problem.
    You are incredible! Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

*