"Communication link failure" error message is reported for SSIS packages on SQL servers configured to use encryption and a large network packet size

Article translations Article translations
Close Close
Article ID: 2006769 - View products that this article applies to.
Expand all | Collapse all

Symptoms

Consider the following scenario for either SQL Server 2005 or SQL Server 2008 environments:

  • You configure your SQL Server to use SSL for connections.
  • You configure your SQL server to use a network packet size that is greater than the default value (4K)

In this scenario, you will notice the following:

  • An attempt to either save SSIS packages to the MSDB package store will fail with the following error message:

The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Communication link failure).The SQL statement that was issued has failed. 

Note: You also run into the above error message when saving maintenance plans created in SQL Server Management Studio as SSIS packages to MSDB databases since that operation inherently uses encryption for connections to SQL Server.

  • Data collector feature in SQL Server 2008 that uses SSIS, runs into various issues as noted below:
    • A Data Collection Set job reports the following errors in the Job History: 

dcexec: Error: Internal error at Main (Reason: The system cannot find the file specified).
dcexec: Error: Internal error at Main (Reason: The handle is invalid).

    • When running a Data Collection Set directly from the Data you may encounter the following error message:

Package "Set_{7B191952-8ECF-4E12-AEB2-EF646EF79FEF}_Master_Package_Collection" failed.

If you review the Data Collector logs you will find an error message similar to the following.

SSIS error. Component name: TaskForCollectionItem_1, Code: -1073602332, Subcomponent: (null), Description: Error 0xC0014062 while preparing to load the package. The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Communication link failure).  The SQL statement that was issued has failed. 

Note: The problem could happen with any operation that uses either LoadFromSqlServer or SaveToSqlServer methods when both the conditions (encryption and large packet size) discussed in this section are true.

Cause

Secure Socket Layer (SSL) and its replacement, Transport Layer Security(TLS), limit data fragments to 16k in size. This is documented in the public RFC 2246 (section 6.2.2) and the current implementation of SQL Network Interface (SNI) layer adheres to this specification. So when using a network packet size that is greater than 16388 (16K) in environments where encryption is enabled on SQL Server, you will run into errors discussed in the Symptoms section. For further information refer to the following KB article:

  • 2008195 Issues to consider when changing the default value of network packet size for SQL server connections

Resolution

To resolve this problem, specify a network packet size that is smaller than 16384 (16K) bytes. You may use the following code to set the network packet size configuration option of the sp_configure system stored procedure:

EXEC sp_configure 'network packet size', 16383
RECONFIGURE WITH OVERRIDE 
GO 

The network packet size can also be altered via the Server Properties page in Object Explorer. Select the Advanced option and type in the new value for Network Packet Size and click OK.

Note: You do not need to restart SQL Server for the change to be effective. After this setting is changed, all new connections receive the new value.

More Information

 Steps to reproduce: 
  1.  Make sure your Data Collector is installed.
  2.  Set the Network Packet Size to a value greater than 8K
  3. sp_configure 'network packet size', 32767
    RECONFIGURE WITH OVERRIDE
    GO
  4. Right click on Data Collection in Object Explorer (OE) and Disable Data Collection
  5. Right click on Data Collection in OE and select Enable Data Collection.
  6. Right click on Server Activity in the collection sets and  select Start Data Collection Set.
  7. To get the error, right click on Server Activity and select Collect and Upload Now. (The DC logs show the error in detail)
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2006769 - Last Review: January 14, 2010 - Revision: 5.0
APPLIES TO
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Evaluation Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 Standard
Keywords: 
KB2006769

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com