What account gets used for Windows Authentication OLE DB connection.
- A DTS package runs fine when I execute it myself. I schedule the package to run, with the job owner equal to the service account, which also happens to be a windows domain account and an SA. The job fails because the data source connection in the package throws an error of [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.When using Windows Authentication for an OLE DB connection in a DTS package, what account is supposed to get used for that connection when run as a scheduled job? I though it would be the owner of the job, but that doesn't seem to be the case.Thank you.
-
Just for the heck of it, set the owner of the job to sa and see what happens.
-
I just tried that and I get the same error.
I think it may have something to do with the Service Principle Names. I don't have a regular user account on that server so it is difficult for me to verify the SPN settings.
-
Worth a shot. That was the only way we could get our DTS packages to run as scheduled jobs at my last company.
If I think of anything else, I add a post here.
-
When a DTS package is scheduled as part of a SQL job, it runs under the same account that runs the SQL Server Agent unless you specifically set the username & password for it to run under in the DTSRUN command using /U and /P switches.
Have you tried using the /E switch in the DTSRUN line (use trusted connection)?
-
Yes, the DTS package is scheduled as /E. I may also note that if I schedule the package using the normal method, which results in an encrypted /Z string in the job, the job fails for the same null user reason without even beginning execution. I have to schedule all packages using the decrypted format. I am not sure why. It might be related.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply