April 5, 2012 at 2:49 pm
SQL 2000, 2005, 2008 - Many instances - I am changing the sa password.
Some posts seem to say that there isn't any impact on jobs when you change sa password.
Some say no impact to a job - per se - but packages may have a problem.
I seem to remember some years back that there really was something about SQL Agent properties
that needed close attention when changing sa password. Maybe it was just SQL Server 7.
Maybe it was 7 and 2000, but I'm pretty sure there was something.
Questions:
1. Does that sound familiar? Can somebody remind me what that was? Or am I just
too old to remember clearly? Other DBAs in my shop have the same recollection, but
can't recall the details.
2. If there were issues, are they still a concern with SQL 2005 and 2008?
3. If there are concerns, is there a tsql query that can ferret out an instance in danger?
April 13, 2012 at 11:04 am
Ok. We hit it. SQL 2008. Maintenance jobs fail. Code: 0xC00291EC
"Connection may not be configured correctly or you may not have the right permissions on this connection."
Other forum posts (including SQLServerCentral post topic 583656) have suggestions to change the
connection properties of the SQL Agent: They say either change the password for sa or change the connection
properties to Windows Authentication.
No good.
Right click on Agent and pick Properties. Click on Connection. This shows
Windows Authentication and all choices are Greyed out. Sa should not be involved.
The Agent service is run by domain id (not local system). Always has been.
CURIOUS THING:
-- The full database backup maintenance plan succeeds. No problem connecting.
-- The log backup maintenance plan fails with the messages at the top of this post.
-- If I create a new log backup maint plan exactly the same, it runs fine.
So You Say:
What's the big deal? Just recreate it.
Response:
I have a lot of instances using many versions of SQL running plans and jobs that may
not be this simple. I need to
1. Understand what fails.
2. Be able to run some kind of test (TSQL from CMS?) to find out before I change sa password which jobs are in peril.
3. Have a fix that does not involve recreating the job.
If you can help me with any part of this, I would be very grateful.
April 13, 2012 at 11:11 am
One more curious thing:
I can log in to SSMS as sa with the new password. I can modify the maintenance plan.
I CANNOT delete the maintenance plan. Error message: Login failed for sa.
What the heck is that? Obviously there is some password connected to the
maint plan or the job. Anybody got a clue where that is and how to change it?
Again, this is SQL 2008. Not R2
April 18, 2012 at 11:37 am
I had the same issue with the maintenance plan, i removed the existing connection and created a new one using Manage Connections in the package. That fixed this issue. Hope this helps you.
August 31, 2012 at 11:48 am
I ran across this error today. It makes sense that you would be able to edit a job (but not delete it) because before you updated the SA password in the job (under "Manage Connections"), you didn't have any authority to do so. You are able to edit the job because that is the way to update the password value for the authentication of SA (i.e., the owner of the package). Once you change the value for the SA password to the new value (and save the change), you can rename, or delete it.
BTW, in my case, I believe we encountered this error because the SA password was changed by a windows authenticated System Administrator (out of necessity). We didn't have to delete and recreate the maintenance plans, just update the SA password to the new value.
Cool.
October 15, 2012 at 6:58 am
I know this post is starting to get old but I though I would just add my recent experience in case it will prove useful to others. Our server team changed the SA password on our Sql 2008 instances and thereafter, some (but not all) of the overnight maintenance-plans were failing with connection errors. What I found was similar to the information posted by Surii, except that I didn't need to remove and re-create the existing connection information. I selected the 'Modify' option on a maintenance-plan and once opened, I selected 'Edit' on the first item in the plan. The first tab of the dialog that appears showed the 'Connection' and 'Database' dropdown boxes as blank and greyed out. This turned out to be the same for all the steps in the plan. In the 'Manage Connections' doprdown on the toolbar, the 'local connection' entry with the correct details was still shown. By clicking on this local connection in the dropdown, it automatically reset the connection details for each step in the plan, I then saved the changes and the jobs have worked fine ever since. Somehow, the change of SA password had removed the connection details from some plans but not all, I don't know why this should happen but we'll know what to look for if this happens again in the future. Hope this helps.
October 15, 2012 at 7:43 am
Thanks to participants for your contributions. I think I have a couple of ways to approach failures now.
I'm still interested to know, if anyone has thoughts on this, if there is a TSQL query that can identify a potential failure before the plan fails. The information on the plans and their connection properties must reside in system tables somewhere. When your shop has many instances spread across domains and spanning several version levels, with varied maintenance plan requirements created by different DBAs with different skill sets, there are going to be surprises. The running of discovery scripts helps to minimize the pain of those surprises. In this case, we have no script and don't know where the information lives. The truth is out there .... somewhere.
October 16, 2012 at 7:00 am
One possible reason that when you try to see the SQLAgent properties and it is greyed out is that you are not logged into the physical server... are you logged into Mgt Studio on your PC when you try this? SQL Agent will run with a Network account not a SQL Server generated type ID so this won't help.
I think... again.. think your issue is that when you change the 'sa' password the maint plans won't run now because it is trying to use the old 'sa' password when the jobs run. You need to edit the connections for each maint plan and change the password.
October 16, 2012 at 10:54 am
Hayden - Sorry I can't offer any suggestions as to whether there are system tables or something to identify potential failures however, as I've re-read this thread again, I noticed that in my reply, I didn't elaborate on the plans that failed. When I mentioned that some of the plans continued working fine, these were check/backup/reindex/etc jobs which were configured to process the whole of a particular database. The jobs that were failing were ones that had been set up to maintain specific tables and views. I checked the other SQL database servers and the same pattern emerged: any maintenance-plan that processed all of a database was unaffected when the SA password was changed, only those which were being specific/restrictive lost their connection configuration and had to be reset in the manner that I described in my first reply. Whilst this information may not help you to identify future problems before they happen, a good starting point might be to list all the plans which do NOT process the whole database unconditionally? Just a thought, hope this helps.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply