Help: SQL Server

In this blog we are sharing an issue which can be seen while querying external table created in SQL 2019 with data source pointing to SQL Server 2014. The error message which comes back is “TCP Provider: An existing connection was forcibly closed by the remote host”

While working with PolyBase feature, few of our customer reported an interesting issue. They informed us that when they query large amount of data by creating an external table in SQL Server 2019 with data source as a table in SQL Server 2014, they get below error.

Msg 7320, Level 16, State 110, Line 82
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.
Additional error <2>: ErrorMsg: [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure, SqlState: 08S01, NativeError: 10054.

This error doesn’t appear when:

• Data source is created for SQL Server running 2016 or above.
• Data set is not very big.

Here are the steps to reproduce the error:

• In SQL Server 2014, create a login, create a database and then table inside it. Insert some rows. (note that I have selected column as char(5000) so that we fetch big chunk of data in each row)

USE [master]
GO
DROP LOGIN [polybaselogin]
GO
CREATE LOGIN [polybaselogin] WITH PASSWORD = N'A_Very_Strong_P@ssw0rd@123ForPolyBaseLogin',
DEFAULT_DATABASE = [master] ,DEFAULT_LANGUAGE = [us_english] ,CHECK_EXPIRATION = OFF ,CHECK_POLICY = OFF
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [polybaselogin]
GO

CREATE DATABASE [SQL2014DB]
GO
USE [SQL2014DB]
GO
CREATE TABLE [dbo].[Table_2014] (
     [i] [int] IDENTITY(1, 1) NOT NULL
    ,[j] [char](5000) NULL) 
GO
INSERT INTO [dbo].[Table_2014] (j)
VALUES('Microsoft'), ('SQL'),('Server'),('2019'),('Released')

• Now, create external table in SQL Server 2019. Location given in data source is IP:Port of SQL Server 2014 instance.

CREATE DATABASE SourceDB
GO
USE SourceDB
GO
CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
WITH IDENTITY = 'polybaselogin', SECRET = 'Sysadmin@123';
GO
USE SourceDB
GO
CREATE EXTERNAL DATA SOURCE SQLServer_DestinationDB
WITH ( 
LOCATION = 'sqlserver://10.0.0.4:21433',
    CREDENTIAL = SQLServerCredentials
);
GO

CREATE EXTERNAL TABLE SourceDB.[dbo].[External_Table_SQL2019_DestinationTable]
(
    [i] [int] NULL,
    [j] char(5000) NULL
)
WITH (DATA_SOURCE = [SQLServer_DestinationDB],LOCATION = N'[SQL2014DB].[dbo].[Table_2014]')
GO

• At this point out setup is ready. Lets reproduce the behavior now.
• If we try to query 3 rows from external table, it works fine.
• If we fetch 4 rows then it fails.

-- below works
SELECT TOP 3 *
FROM [SourceDB].[dbo].[External_Table_SQL2019_DestinationTable]

-- this fails
SELECT TOP 4 *
FROM [SourceDB].[dbo].[External_Table_SQL2019_DestinationTable]

This is due to amount of data fetched by single query. Here is the workaround to overcome such behavior. We need to modify data source to use a connection option CONNECTION_OPTIONS = ‘UseDefaultEncryptionOptions=false’

First I needed to drop data source as it was used by external table. Since there is no data loss, we should be OK to drop them.

-- drop external table
USE [SourceDB]
GO
DROP EXTERNAL TABLE [dbo].[External_Table_SQL2019_DestinationTable]
GO
-- drop external data source
USE [SourceDB]
GO
DROP EXTERNAL DATA SOURCE [SQLServer_DestinationDB]
GO
-- create external data source with UseDefaultEncryptionOptions=false .
USE SourceDB
GO
CREATE EXTERNAL DATA SOURCE SQLServer_DestinationDB
WITH ( 
LOCATION = 'sqlserver://10.0.0.4:21433',
    CREDENTIAL = SQLServerCredentials,
    CONNECTION_OPTIONS = 'UseDefaultEncryptionOptions=false'
);
GO
-- create external table
CREATE EXTERNAL TABLE SourceDB.[dbo].[External_Table_SQL2019_DestinationTable]
(
    [i] [int] NULL,
    [j] char(5000) NULL
)
WITH (DATA_SOURCE = [SQLServer_DestinationDB],LOCATION = N'[SQL2014DB].[dbo].[Table_2014]')
GO

Lets try to run the same query. Voilà! It works!

Hope this helps!

Cheers,

Balmukund