July 12, 2010 at 7:34 am
Our 2-node sql 2005 cluster with 2 instances (one default, one named) had the primary node fail. The SSIS pkgs (maintenance plans) being run as jobs are failing on the named instance. Job history shows:
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.
I've looked into the proper configuration of the MsDtsSrvr.ini.xml file as outlined at the following url's, but no luck.
http://support.microsoft.com/kb/942176/en-us?p=1
http://msdn.microsoft.com/en-us/library/ms345193(SQL.90).aspx
Not sure where to proceed from here - looking for any and all help in getting this up and running.
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 13, 2010 at 3:42 pm
Wayne,
What are the service accounts being used for SQL Server Agent? (Domain, local, or network service)
Do you have logging enabled?
Can you open and edit the maint plans? Export them to structured storage files and open in bids? Once in bids, can you debug the package and have it run successfully?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 13, 2010 at 5:24 pm
CirquedeSQLeil (7/13/2010)
Wayne,What are the service accounts being used for SQL Server Agent? (Domain, local, or network service)
Do you have logging enabled?
Can you open and edit the maint plans? Export them to structured storage files and open in bids? Once in bids, can you debug the package and have it run successfully?
Good questions Jason,
SS Agent using a domain login.
Can open/edit/save the MP in SSMS.
Logging enabled, but when run it's not saving anything in the log files. Appears to be crashing before it gets to this step.
In BIDS, I was able to import the MP. There, the "Local Server Connection" would not pass a "Test Connection". In looking at the server info, it did not have the instance name as part of the server name (it shouldn't need this for the cluster). Since the server name can't be modified in SSMS when editing the MP, I added an alias (configuration manager) for the cluster name and the appropriate IP/port. It now works.
When the primary node gets back up, I'll have to check to see if it already has an alias set up - does the cluster install do this on the primary node, but not the secondary node?
Again, thanks for your help!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 13, 2010 at 5:35 pm
WayneS (7/13/2010)
In BIDS, I was able to import the MP. There, the "Local Server Connection" would not pass a "Test Connection". In looking at the server info, it did not have the instance name as part of the server name (it shouldn't need this for the cluster). Since the server name can't be modified in SSMS when editing the MP, I added an alias (configuration manager) for the cluster name and the appropriate IP/port. It now works.When the primary node gets back up, I'll have to check to see if it already has an alias set up - does the cluster install do this on the primary node, but not the secondary node?
Again, thanks for your help!
Default behavior of my clusters did not create an alias. This makes me a little curious.
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply