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

Temporal Table Permissions

Kenneth Fisher shows us the permissions needed to create temporal tables: Msg 13538, Level 16, State 3, Line 6 You do not have the required permissions to complete the operation. Well, that’s not good. What permissions do I need exactly? Well, again, according to BOL I need CONTROL on the table and its history table. For those […]

Read More

Azure Database-Level Firewall Rules And Geo-Replication

Arun Sirpal explains that you don’t need to create database-level firewall rules in Azure on secondary databases when using Active Geo-Replication: The main purpose of this post today is to discuss this point – If you have an Azure SQL Database involved in Active Geo Replication and opt to use database level firewall rules do […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

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