So interesting problem that I ran into with regards to maintenance plans and SQL Server 2012/2014.

The test case for this is on a clustered Windows 2012 R2 environment install SQL of your choice without SSIS installed. Create a maintenance plan that just does "select 1" and it will run fine via SQL Agent. Now, install SSIS, and the job now fails due to:

Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

Even when uninstalling SSIS, the package still fails when being run through SQL Agent. In all cases, the package can be run via the command line or through Integration Services directly.

The connection string is:

Data Source=instance\name;Integrated Security=True;Pooling=False;MultipleActiveResultSets=False;Packet Size=4096;Application Name="Microsoft SQL Server Management Studio";

It seems like SSIS changes some type of reference to the package but I can't narrow down what is causing this to fail. I've compared registry keys as well with other instances and there isn't anything would would reference '.\name' or anything to that effect that would black hole it.

Named pipes, shared memory, and TCP/IP are all enabled and firewall rules both outbound and inbound are open as well.

Thoughts on what to check for next?

  • Is the package security set to - encrypt all with user password ? – Kin Shah Aug 25 '16 at 19:19
  • Hi @Kin, from what I can tell you can't adjust package security with maintenance plans so it would be whichever the default SQL sets upon creation. We haven't exported anything or imported anything. – TechnicalBeef Aug 25 '16 at 19:56
  • Have you tried recreating the maintenance plan? I read somewhere that if you drop/create the plan it'll now work. – Tara Kizer Aug 25 '16 at 20:19
  • Also verify that the server is named properly by executing "select name from sys.servers where server_id =0" and matching it to the name returned from select @@servername. I had this happen once when performing a side by side upgrade of a server. The new server was named identically to the replaced one and I believe I did recreate the plan as Tara suggested. – Michael Keleher Aug 25 '16 at 20:57
  • Hey @Tara and Michael, we've tried dropping/recreating the maintenance plans and select @@servername returns fine. This is a new installation and not a upgrade so the server names are the same. For this test, i was able to reproduce it in a new clustered environment when testing. – TechnicalBeef Aug 26 '16 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.