July 6, 2008 at 8:28 am
Hi,
This weekend I attempted to change our sa password, it went relatively successfully apart from one particular aspect:
Any scheduled tasks (jobs) which call DTS packages, fail when they try to run the DTS package. I can run jobs which do not call DTS packages fine. I can also run the DTS packages via the "local packages" section of EM fine. The *only* thing that doesn't work is when a scheduled task attempts to run a DTS package. When this happens, i get the following error message:
Executed as user: DBSRV-01\sqlservice. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
Am I right in thinking that DBSRV-01\sqlservice is the SQL Server Agent process? This was orignally set up to use Windows authentication, I updated it with the new sa authentication details (and stopped and started the agent service) but got the same error.
Could the DBSRV-01\sqlservice agent/service/whatever-it-is be caching the original sa password somewhere, and that is causing the problem?
I need to get this password changed, so any help anyone could provide would be gratefull received. If more info is required, just let me know.
Thanks in advance
July 6, 2008 at 9:12 am
Am I right in thinking that DBSRV-01\sqlservice is the SQL Server Agent process?
Probably but to confirm, look at the configuration of the Windows Service named "SQLSERVERAGENT" on then "Log On" tab. This may also be viewable from Enterprise Manager under the SQL Server Agent properties.
Fixing the job failures due to security violations.
The root cause is that a DTS SQL Server connections are configured to use the "SA" login with a hard coded the password. The solution is to change the DTS packages to use Windows Accounts, which will result in the DTS package to be executed under whatever windows account executes DTSRUN.EXE, which for jobs, is the SQL Server Agent windows account ( if the owner of the job is "sa")
In a DTS Package that is failing, check the properties of all SQL Server connection objects.
If "Use SQL Server Authentication" is selected (probably using the "sa" login ), then change to use "Windows Authentication" and save the package. Run the job and if this corrects the error, then repeat for all DTS packages.
As a few followups:
Insure that all personnel developing DTS packages specify "Windows Authentication" for connection properties and that their Windows accounts have appropriate SQL Server and Windows privileges.
Insure that for all personnel executing DTS packages, their Windows accounts have appropriate SQL Server and Windows privileges.
As this solution has all jobs running under the Agent's privileges which always includes SQL Server system administrator and whatever windows rights have been specified, consider having a separate application Windows Account that will own application SQL Server jobs with reduced SQL Server and Windows security rights. Database Maintenance jobs can also run under a more restrictive account but as these are usually only visible to DBAs who already have unlimited rights, the benefit of reducing privileges is not significant and is usually not performed
SQL = Scarcely Qualifies as a Language
July 6, 2008 at 9:45 am
Thanks a lot for this reply.
If I understand correctly, you are suggesting that the DB connections in the DTS package could be causing the problem? If so, i don't think this is the case, for two reasons:
1. They don't use the 'sa' user (so there is no reason why that would be mentioned in any error message)
2. I can run the DTS packages on their own, just not via a scheduled task.
Does that help?
Thanks a lot
PS I checked out the Windows service as you said, and it confirmed that DBSRV-01\sqlservice is the SQL Server Agent, thanks.
July 6, 2008 at 10:21 am
Some further information:
If I change the sa password to what I want it to be, and then right click on one of the DTS packages and create a scheduled task by selecting "Schedule Package...", that new scheduled task runs fine. If, however, I get the script from the original scheduled task, and create a new scheduled task using that, i still get the authentication failure.
i don't want to have to manually recreate all scheduled tasks by the laborious method above, we have 36 DTS packages, and the scheduled tasks that call them contain other steps which would need to be recreated. Hopefully this information will be of some use.
What could be happening during the "Schedule Package..." process, which doesn't get reflected when simply generating a script for a scheduled task? I am sure I am near the answer, but still very confused!
Thanks
July 6, 2008 at 10:47 am
Check the JOB Owner and also check the SQL Agent Service is Undder under. Use a Domain Account with Sufficient privileges to run the SQL Agent Service account.
Maninder
www.dbanation.com
July 7, 2008 at 5:45 am
When using DTSRun.exe with an encrypted command string (the ~Z option), I have encountered some strange behavior including execution of older package versions and security violations.
Appending "/!X /!C" to the dtsrun will cause the decrypted command string to be placed into the windows clipboard.
Example:
DTSRun /~Z0x3E2E70BEE7A2E715C2C76BB552BFF74EE67ED14FA9874F9947E729618F059D8FB8136A83345D3CD82E8A96642105A68DF5F2057A860D1A4D9146F97E671C78003D7BB210D29025CED6CDDC891D9A9A4A1CC173B790F69D97883B3B6A839DE8121DE9F51E6E33164B19E155F148B459332E105FF7C9392B3E84DF2A48108E43925C1DA5 /!X /!C
Output for this particular command is:
DTSRun /S "DBREPOSITORY" /N "DBAct-GrantAccessAuditor-8" /E /!X /!C
After you have the decrypted command, replace the command string in the job step and remove the "/!X /!C" options.
SQL = Scarcely Qualifies as a Language
July 7, 2008 at 10:15 am
Thanks a lot for this response, this is almost certain to be the problem!
I found DTSRunDec here: http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx but I cannot work out what I need to install to be able to compile it.
Does anyone have any info on that, or any other ways I can decrypt the encrypted DTSRun commands i have?
Thanks a lot
July 8, 2008 at 6:27 am
I found DTSRunDec here: http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx but I cannot work out what I need to install to be able to compile it.
Does anyone have any info on that, or any other ways I can decrypt the encrypted DTSRun commands i have?
[/code]
I also tried to use this tool and was not able to get it to work.
As an alternative, use the "/!X /!C" switches as previously posted.
SQL = Scarcely Qualifies as a Language
July 8, 2008 at 8:08 am
How strange, I couldn't get /!X /!C to work - i kept getting the encrypted command back in the clipboard. Not sure what i was doing wrong.
However - i did find a compiled version of DTSRunDec and it worked perfectly - proving the problem. We will be using plain text DTSRun commands from now on - and also never again using the 'sa' user for them!
Thanks for the help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply