SQL SERVER – Maintenance Plan failing with 0x80131904 – A network-related or instance-specific error occurred while establishing a connection to SQL Server.

One of my clients contacted me and informed that after patching of SQL Server, they noticed that their maintenance plan were failing. I asked them to share the complete error message about the Maintenance Plan failing with 0x80131904.

End Warning Error: 2017-01-09 17:22:27.10 Code: 0xC0024104 Source: Back Up Database
 Task Description: The Execute method on the task returned error code 0x80131904
 (A network-related or instance-specific error occurred while establishing a connection to SQL Server.
 The server was not found or was not accessible. Verify that the instance name is correct and that
 SQL Server is configured to allow remote connections.
 (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)).
 The Execute method must succeed, and indicate the result using an “out” parameter.
 End Error

I have removed some part of a message which is not relevant. In above long error information, interesting piece is “error: 26 – Error Locating Server/Instance Specified”.

Whenever we create a Maintenance Plan, SQL Server Management Studio always takes the name of the connection manager as “Local server connection” by default.

The important thing to note is, it uses the server name that we have used in SSMS to connect to the instance and puts this exact name in the server name property of the connection manager “Local server connection”

Does this give some hint?

SOLUTION / WORKAROUND

It is quite possible that the port of SQL might have been changed. When we connected earlier, when maintenance plan was created. We need to check on what is the server name, value in the connection manager “Local server connection” mentioned. To see that we can click “Manage Connections” after we click modify the Maintenance Plan.

Now, we can use the SQL Server Configuration manager and create an alias with the exact same name, provide the TCP and manual port number. Once we created alias, maintenance plan ran without an error?

Hopefully you know how to create TCP aliases in SQL Server Configuration Manager. If not, refer to books online documentation Create or Delete a Server Alias for Use by a Client (SQL Server Configuration Manager)

Reference: Pinal Dave (http://blog.SQLAuthority.com)