Actual Emails: Will MSOLEDB work for connecting to an Availability Group?

Scenario:

We need to configure an existing legacy application from an external vendor to talk to our new SQL Server 2017 Availability Group, which spans multiple subnets. In the end, that last bit is the key. The old data provider MSOLEDB will work for connecting to single-subnet Availability Group listeners, but won't work consistently when connecting to a multisubnet Availability Group's listener. The key is the ability to specify MultiSubNetFailover=True in the connection parameters.

Client's software vendor:

The connection used is the Microsoft OLE DB Provider for SQL Server that is supplied by Microsoft to create the Data Link Properties. When configuring our Data Link, we use the "Microsoft OLE DB Provider for SQL Server". The connection string is formatted: 

Provider=SQLOLEDB.1;Password="whatever";Persist Security Info=True;User ID=username;Initial Catalog=Test;Data Source=ServerName 

Answer:

Good info, but we do need to make a change here. SQLOLEDB is the provider from back in the SQL 2000 era. Do not recommend its use for new development. It has been replaced by the Native Client (SNAC), which has since been replaced by MSOLEDBSQL (I linked below).  It should be easy and transparent to upgrade the provider from SQLOLEDB with no negative impact.

Here’s why we need to upgrade the data provider to talk to our SQL Availability Group. The SQL Server Listener for a multi-subnet Availability Group actually has two IP’s. When you perform at a command line:

Nslookup SQLListenerNameWhatever 

You get back an IP in each subnet (in our case, two), for each replica SQL instance in the Availability Group. 

When a connection string uses MultiSubNetFailover=True and connecting to the Availability Group Listener name (not the IP or either SQL Server instance name), BOTH IP’s are tried simultaneously and immediately, and the driver talks only to the IP that replies: the primary replica.  After a failover, the other IP begins to reply immediately, and so there is no delay in reconnectivity when a failover occurs. 

Without specifying MultiSubNetFailover=True, your application will (essentially randomly) pick ONE of the two IP’s for the Listener, and try it. There is no way to “rig” one IP to be first consistently over time. If it picks the primary replica, everything works! If it picks the IP for the current secondary replica… your application’s connection timeout will have to expire and then try the next IP.  This is why I’m bringing this up – the application will timeout upon SQL login without MultiSubNetFailover=True.

This hasn’t been an issue with your other clients if they aren’t using a multisubnet availability group. If they have an Availability Group all inside only one subnet, then the Listener only has one IP in DNS, and MultiSubNetFailover=True isn’t required.

You should be fine to install the MSOLEDBSQL provider released in 2018 and use that in your data link. Obviously it should be tested for due diligence, but it should work. At the very least, you could try instead the SQL Native Client 11 (SQLNCLI11), which was released for SQL Server 2012, and it also should work just fine for both OLEDB or ODBC.

Let me know if you have any more questions.

Connection string information for SQL Server Availability Groups:

• The SQL Native Client (SNAC) Sqlncli10 will NOT work for connecting to a multisubnet Availability Group because it cannot specify MultiSubNetFailover=True. Must use the newer SQLNCLI11, however, the SQL Native Client (SNAC) is no longer maintained by Microsoft, so you should instead use the new discrete OLE DB or ODBC drivers.
• Recommend convert your connection strings from sqlncli1n or sqloledb, to MSOLEDBSQL. OLEDB has been un-deprecated via the new MSOLEDBSQL provider released in 2018. Download the new driver here if needed: https://www.microsoft.com/en-us/download/details.aspx?id=56730 (Note that of the two identically-named files, the 64-bit one is the larger file size of the two.)
• There is also the ODBC Driver 17 for SQL Server provider, and the newer ODBC Driver 18 for SQL Server.

• In SSIS, the new MSOLEDBSQL OLE DB driver appears as "Native OLE DB\Microsoft OLE DB Driver for SQL Server". The old OLE DB driver is "Native OLE DB\Microsoft OLE DB Provider for SQL Server". 

Note: you also need to make sure your Windows Cluster has RegisterAllProviderIP's set to ON for a multisubnet Availability Group!

Edit March 2023: Added latest OLE DB/ODBC information.

SQL Server on TLS 1.2: Checklist to disabling TLS 1.1 and 1.0

A common finding in security audits these days is the failure to conduct all communications via TLS 1.2. (Correspondingly, a common cause for sudden SQL Server application connectivity failures is a sysadmin's inadvisable, reckless deactivation of TLS 1.0 and 1.1 on a server. Been there.)

News August 2023: TLS 1.0 and TLS 1.1 soon to be disabled in Windows, raising the importance of being aware of any outdated connection providers that are reliant on TLS 1.0 or 1.1. Make an effort to update to TLS 1.2 or 1.3.

Moving SQL Server connections to TLS 1.2 is not solely (or even mostly) a SQL server change. We need to get all application/vendor developers in the loop to make the transition to TLS 1.2, apply a lot of .NET version-specific patches, and more.

Disabling TLS 1.0 and 1.1 on the Windows Server that runs the SQL instance is definitely something a lot of security-sensitive folks are wanting to do (what's TLS anyway?), but they’re often hamstrung by the applications connecting to the SQL server, or by features inside SQL Server itself that have been configured to use legacy algorithms or version settings.

Contrary to some opinion out there, connections will not use the lowest common denominator allowed by the server and the application's client. Connections will use TLS 1.2 if possible. This is usually a limitation of the application connectivity client or .NET framework version. But if you want to prevent (and therefore break/expose) connections from using TLS 1.0 or TLS 1.1, you need to disable TLS 1.0 and 1.1. This is the only way to make sure you're sniffing out the insecure connections.

We definitely need to test this out in pre-production before any production changes. The list of things needed to get onboard with TLS 1.2 could be lengthy, but it’s a worthwhile endeavor. An initial checklist to consider:

• If using SQL Server prior to 2016, patch SQL Server. Info here. 
• We need to make sure that the latest version of the SQL Server Native Client is configured on the SQL server itself, and also all client machines that connect to SQL, per version. See the "Client component downloads" section in this same link.
• KB3135244 details TLS 1.2 support for Microsoft SQL Server.
• Any clients that use the .NET framework and ADO.NET connectors will need to get up to .NET Framework 4.6 to use TLS 1.2. See the "Additional fixes needed for SQL Server to use TLS 1.2" in this same link as above. There are patches for other connectivity platforms like ODBC and JDBC as well that are needed for both the client and servers. 
• The clients/webservers/appservers, and the SQL Server will ALL need these .NET patches. There are patches needed for frameworks starting with .NET Framework 3.5 for TLS 1.2, again see link above.
• For example, SQL Server Database Mail still uses .NET Framework 3.5 SP1, which needs a specific patch to allow TLS 1.2. See above link for OS-specific links.
• You need to change registry keys on the Windows Server.
• On operating systems prior to Windows 8/Server 2012, to enable TLS 1.2 you need to add keys, and modify existing keys to disable TLS 1.0 and 1.1. 
• Starting with Windows 8 and later and Windows Server 2012 and later, TLS 1.2 is already enabled, and you need to add registry keys to disable TLS 1.0 and 1.1.
• Keys here
• Or, you can use a free tool like Nartac IIS Crypto to manage the registry edits for you.
• Note that a reboot is required to make the registry changes take effect.
• Anything in SQL Server that is encrypted using MD5 algorithms should probably be changed anyway (certificates, database keys, and endpoints for example) but it’s definitely going to be required for TLS 1.2. In fact, starting with SQL Server 2016, all algorithms other than AES_128, AES_192, and AES_256 are deprecated.
• SQLOLEDB will not receive support for TLS 1.2, so some connections using the OLEDB driver (as opposed to ODBC, Native Client, or ADO.NET) will need to be rewritten. should be replaced by the new MSOLEDBSQL OLEDB driver, released in 2018. 
• The 'Microsoft.ACE.OLEDB.12.0' provider continues to work, apparently. 
• Linked server connections using @provider='SQLOLEDB' will continue to work, because this actually uses the 'SQLNCLI' provider, the SQL Native Client. Edit: The SNAC is no longer maintained, this should also be migrated to either the new MSOLEDBSQL or the new ODBC drivers actively maintained.  The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 and SQL Server Management Studio 19 (SSMS).
• For ODBC connections, I recommend using the latest ODBC Driver for SQL Server, not the ODBC functionality of the SQLNCLI/SQLNCLI11 or SQL Native Client (deprecated).
• For SSRS, we also need to make sure SSRS is using only HTTPS. Again the above .NET framework components need to be updated on the SSRS server and application servers. More info here.      

Update: Next up? SQL Server 2022 introduced support for Transport Layer Security (TLS) 1.3 when TDS 8.0 is used. 

How can you tell what version of TLS is currently used for client connections? See my companion blog post on this topic.

Edit: Updated for news about MSOLEDBSQL re: connection providers.