SQL Server Import From Excel And TLS 1.0

Chris Thompson has an important notice if you import data into Excel from SQL Server:

As you may know, TLS 1.0 is being deprecated due to various known exploits and will no longer be PCI compliant as of June 30th, 2018 (see PCI DSS v3.1 and SSL: What you should do NOW below).   You may also know that Microsoft has provided TLS 1.1/1.2 patches for the SQL Server Database Engine (2008+) as well as the client connectivity components (see TLS 1.2 support for Microsoft SQL Server below). What you may NOT know is that there is a popular feature in Excel to import data from SQL Server. See the screen print below from Excel 2016.

The problem with this feature lies in the fact that this menu option will, by default, leverage SQLOLEDB.1 as the OLE DB provider when connecting to SQL Server. This provider is an older MDAC/WDAC provider (see Data Access Technologies Road Map below) that comes built into the Operating System (including Windows 10) but DOES NOT support TLS 1.1+. So, if you have SQL Servers that have TLS 1.0 Server disabled, you will no longer be able to use this feature. You will receive an error similar to the one below. You will also receive the same or similar error if you have existing workbooks that use this feature and attempt to refresh those workbooks.

Chris has a workaround for current versions of Excel and notes that future versions will hide this particular import option behind a legacy wizards menu.

Related Posts

When MS_SSISServerCleanupJobLogin Is Orphaned

Sreekanth Bandarla noticed a problem in cleaning up SSIS metadata: Couple of weeks ago I was analyzing a server for space and noticed SSISDB database was abnormally huge (this Instance was running just a handful of packages). I noticed couple of internal schema tables in SSISDB were huge (with some hundreds of millions of rows), […]

Read More

The Key Hierarchy And SQL Server Encryption

David Fowler walks us through the various keys used in encrypting data in SQL Server: I’m sure that we all know that SQL Server includes all sorts of interesting functionality to allow us to encrypt our data and like with all encryption techniques, that data is encrypted using keys. In SQL Server we’ve got a […]

Read More

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930