August 2, 2007 at 10:37 am
This is driving me insane. We recently reset our AD domain password. We had the SQLServer Agent running under this password. Now all of the jobs that run under SQL Agent are now failing. Here is what I have done.
I have SQL Server Agent login\password under SQL Server Configuration Manger to new Domain\Administrator and new password. I have also into Microsoft SQL Server Management Studio Server --> Security --> sa and reset that password to our new domain password.
I also went into Windows Services and changed our account there. Rebooted server a couple of times and all my jobs still fail with
"... -2147217843 (80040E4D) Error string: Login failed for user 'sa'. "
I have also tried sp_password: sp_password @new='new domain password,@loginame='sa'
Here is the challenging part when I go to SQMS --> Security -->sa and change it our our old domain password everything works fine.
There has to be something I am over looking.
Any help would be greatly appreciated.
thanks
August 2, 2007 at 11:00 am
I would *highly* recommend against using a domain administrator account to run your SQL Server services. And also it's really not good to use the same password for sa and your domain admin. If someone compromises one, they've got the proverbial keys to the castle.
Without knowing more about the scenario, I would recommend changing the sa password, then using the GUI to generate the scripts for the job and re-creating it from scratch using the scripts.
August 2, 2007 at 12:26 pm
thanks for the advice I will take make necessary changes.
As far as changing the sa account. When I change the sa account under Security from that point forward I have to close and reopen SQL Management Studio then re-authenticate with new password I just set. So that lets me know that the sa account has been reset to new password and all my DTSX packages run just fine. Now when I change the SQL Agent Service account to a DOMAIN\User account. I get a 'sa' login failed.
Why would sa even be brought up if DTS and job both reference DOMAIN\User account? that the part that is frustrating where is this SQL Agent getting info for sa?
Aaron thank you for your promopt response I appreciate it.
- Oskar
August 2, 2007 at 4:12 pm
Oskar,
Aaron is correct. Do not use a Domain Admin account for SQL Server Service accounts. Use a Windows domain account in the Domain Users group.
Also, you said you that changed the service accounts using Windows Services. Use the SQL Server Configuration Manager to set SQL Server and SQL Server Agent serice account, instead. This will ensure that the service account gets all the permission it needs to perform SQL Server functions.
Once your service accounts are step, configure your jobs to run under the service account. If your job requires permissions to a resource that are not on the local server, assign permisions to the domain account accordingly.
If this still does not fix your problem, recreating your job, as Aaron said, may help.
For more detail take a look at SQL Server 2005 Books Online. Look for SQL Server Agent on the index tab.
- Jon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply