Fix: An Exception Occurred While Executing a Transact-SQL Statement or Batch

If you attempt to test a connection to an SQL database server in Microsoft SQL Serer Management Studio, you may receive a network related error, similar to the below.

The test connection to the linked server failed.

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.ConnectionInfo)

TCP Provider: The specified network name is no longer available.

Client unabe to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too bust to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.

Client unable to establish a connection due to a prelogin failure.

Microsoft SQL Server, Error: 64

Resolution #1:

To resolve this issue, make sure that you select the Map To Credential check box on the General tab of the Login Properties - sa dialog box before you change the "sa" login properties and then click OK to close the dialog box.

Resolution #2:

Try to disable TCP Offloading on the SQL Server documented here. TCP Offloading has been found to be a common cause of network related TCP errors and cause instability of a network connection.

Resolution #3:

If you are using the the System login credentials and using SQL Server 2012, you must add back sysadmin rights to the account.