February 26, 2008 at 9:50 am
Morning all...
My frustration today is about the MP's. Here is the scenario:
I am the owner on the plans (I know, bad girl!!). Over the weekend, my login expired and the plans failed because of it (at least I think so). Yesterday, I updated my login, changed the MPs owner to be 'sa' and tried to run the MPs again. It still fails and the SQL Server Log says it is because my login is failing. My login is fine....
Here is the error in the 'view history' of the MP job:
Executed as user: DEMO\SYSTEM. ...ersion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:00:00 AM Error: 2008-02-25 01:00:03.04 Code: 0xC00291EC Source: {158BD216-6E3D-4D9C-8D54-BF7F86FEEF31} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-02-25 01:00:03.04 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2008-02-25 01:00:03.06 Code: 0xC0024104 Source: Check Database Integrity ... The package execution fa... The step failed.
Any ideas?? I am at the point of deleting out the MP and re-creating it to see if I can get around this issue......
thank you!!!
February 26, 2008 at 7:14 pm
I had a similar problem when I was messing about with maintenance plans (in a test environment fortunately). I can't quite remember what I did to rectify it. It was either recreating the plan or just logging in with a different account, editing the plan & creating a new connection and telling the plan to use that instead. I don't think I could edit the default connection that was in there, it had been using Windows Authentication and seemed to have cached the credentials it first used, with no option to purge.
Or something. Hopefully it gives you somewhere to start looking.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 26, 2008 at 8:52 pm
Thanks Scott for the insight. I tried to change the owner, which it lets you do, but somehow, somewhere your original owner/login credentials must remain, because it didn't recognize the change. In thinking about it, when you change the owner, you don't get to put in any password, so how would it know? So...I did what you probably did, deleted out the old and created the new and it works great! I just think it really sucks that if a login/password expires on a weekend or whenever you can't get to it that the jobs would fail. That NEVER happened to me in SQL 2000. Ahh...how I miss 2000..... :hehe: Thanks again!!
February 27, 2008 at 9:30 am
Just restart SQL Server services and that would do the trick for you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 9:48 am
February 27, 2008 at 10:22 am
So your problem has solved after restarting the services.?
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 27, 2008 at 10:39 am
March 27, 2008 at 7:00 am
/*Here's how to change the owner of a maintenance plan to dbo in SQL Server 2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'
Benjamin Lotter
http://BenjaminLotter.info/[/url]
http://www.LinkedIn.com/in/BenjaminLotter
Delight thyself also in the LORD and He shall give thee the desires of thine heart.
~Psalm 37:4
December 11, 2008 at 8:53 am
I got the same problem in SQL 2008 and found this page when searching for a solution to changing ownership of the maintenance plans. The 2005 script doeasn't work on 2008 (no surprise there;) ), but this got me thinking and I figured out how to do it in SQL 2008.
UPDATE msdb.dbo.sysssispackages
SET [ownersid] = 0x01
WHERE [name] = 'YourPackageName'
Note: 0x01 sets the owner to sa but you can use the SID from another account as required.
December 11, 2008 at 9:13 am
Phill Carter (12/11/2008)
I got the same problem in SQL 2008 and found this page when searching for a solution to changing ownership of the maintenance plans. The 2005 script doeasn't work on 2008 (no surprise there;) ), but this got me thinking and I figured out how to do it in SQL 2008.UPDATE msdb.dbo.sysssispackages
SET [ownersid] = 0x01
WHERE [name] = 'YourPackageName'
Note: 0x01 sets the owner to sa but you can use the SID from another account as required.
Thanks for clarifying. I should have mentioned that. So here are the scripts for the various versions.
/*Here's how to change the owner of a maintenance plan to dbo in SQL Server 2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers
--SQL 2005 version
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01 --sa user
WHERE
[name] = 'MaintenancePlan'
/* --SQL 2000 version
--note you need to update the owner column as well
UPDATE
[msdb].[dbo].[sysdtspackages]
SET
[owner] = 'sa',
[owner_sid] = 0x01 --sa user
WHERE
[name] = 'MaintenancePlan'
*/
/* --SQL 2008 version
UPDATE
[msdb].[dbo].[sysssispackages]
SET
[ownersid] = 0x01 --sa user
WHERE
[name] = 'MaintenancePlan'
*/
Benjamin Lotter
http://BenjaminLotter.info/[/url]
http://www.LinkedIn.com/in/BenjaminLotter
Delight thyself also in the LORD and He shall give thee the desires of thine heart.
~Psalm 37:4
April 21, 2009 at 7:22 am
I re-created the same problem in my test machine and restarted the SQL Server services. However, it did not resolved the problem. The Maintenance Plan jobs still fails
May 12, 2009 at 4:52 am
I have a dev box and for certain reasons I was using sa to create MPs. Once I’ve changed the sa password I had the same issue. Even worse - I could not even delete the mp. It does seem like the password is cached, not sure where though. I guess it is saved in the properties of the SSIS package.
To fix this all you need to do is edit the MP and in the Design screen click "Manage Connections". There you can specify the password for the account you are using to connect to the database server, in this case just update the sa password.
April 12, 2010 at 4:43 am
I am having the same problem but i think the problem is not only with user but my package local connection is pointing to old server name, Can we change the server name as well?
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
April 12, 2010 at 5:05 am
Hi Nagesh,
Can you modify the plan, and go "Manage connections", local connections. You may change the user name /password and save the MP again. This should solve the problem.
July 22, 2010 at 6:20 am
Thanks Benjamin... that summary is very useful.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply