Generic ODBC external data sources may not work after installing Cumulative Update
Published Mar 30 2023 02:14 PM 9,841 Views
Microsoft

Symptoms

 

After you install SQL Server 2019 CU19 or SQL Server 2022 CU2, external data sources using generic ODBC connector may no longer work. When you try to query external tables that were created before installing the cumulative update, you receive the following error message:

 

Msg 7320, Level 16, State 110, Line 68

Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". Object reference not set to an instance of an object.

 

If you try to create a new external table, you receive the following error message:

 

Msg 110813, Level 16, State 1, Line 64

Object reference not set to an instance of an object.

 

Cause

The issue occurs if the Driver keyword isn't specified in the CONNECTION_OPTIONS argument of the EXTERNAL DATA SOURCE definition. This issue was introduced due to a code change that was released in SQL Server 2019 CU19 and SQL Server 2022 CU2.

 

Workaround

You can uninstall SQL Server 2019 CU19 or SQL Server 2022 CU2 to work around this issue. Or, if you create a new external data source and only reference the DSN keyword, add the Driver keyword to the CONNECTION_OPTIONS argument. You can add anything in the Driver key-value pair (KVP) because the DSN definition will override whatever is specified in the Driver KVP.

 

For example, if you create a new external data source as follows:

 

CREATE EXTERNAL DATA SOURCE MyExternalDataSource

WITH

       ( LOCATION = 'odbc://{server}:port'

         , CREDENTIAL = MyCredential

         , PUSHDOWN = ON

         , CONNECTION_OPTIONS = 'DSN=MyDSN'

       );

 

You can add the Driver keyword to the CONNECTION_OPTIONS argument. Separate the KVPs with a semi-colon.

 

CREATE EXTERNAL DATA SOURCE MyExternalDataSource

WITH

       ( LOCATION = 'odbc://{server}:port'

         , CREDENTIAL = MyCredential

         , PUSHDOWN = ON

         , CONNECTION_OPTIONS = 'DSN=MyDSN;Driver=LiterallyAnyString'

       );

 

If you have an existing external data source that is no longer working after installing the cumulative update, you can use ALTER EXTERNAL DATA SOURCE and reset the CONNECTION_OPTIONS argument to add the Driver keyword. For example, if the existing external data source was defined as follows:

 

CREATE EXTERNAL DATA SOURCE MyAlreadyExistingExternalDataSource

WITH

       ( LOCATION = 'odbc://{server}:port'

         , CREDENTIAL = MyCredential

         , PUSHDOWN = ON

         , CONNECTION_OPTIONS = 'DSN=MyDSN'

       );

 

You can add the Driver keyword to the existing CONNECTION_OPTIONS argument. Separate the KVPs with a semi-colon.

 

ALTER EXTERNAL DATA SOURCE MyAlreadyExistingExternalDataSource SET CONNECTION_OPTIONS = 'DSN=MyDSN;Driver=LiterallyAnyString'

 

If you don't know what connection options are set, check the connection_options column in the sys.external_data_sources system catalog view. If you script out the external data sources, it won't show the CONNECTION_OPTIONS argument correctly.

 

Status

Microsoft is working on a fix for this issue that will be available in a future CU.

8 Comments
Co-Authors
Version history
Last update:
‎Mar 30 2023 02:14 PM
Updated by: