SQL Server Connectivity & TLS 1.2

Question

I've seen a number of posts of varying ages addressing issues people are having connecting to Sql Server using various connection types, particularly with TLS 1.2.  I'd like to engage the community of Sql Server shops and find out how they are working around the issues I'm having.  We have multiple on prem Sql Server 2016 instances we are using as Read Only, not using Alteryx to write back.

Background:

To date, we've been using the MS DB Provider for SQL Server (SQLOLEDB) without issues.  However, this driver is deprecated and does not support TLS 1.2.   Our security team is working to disable TLS 1.0/1.1, and we've been working under an exception for the past several months.  Pressure is growing to resolve this issue.

Options we've explored:

1) Switching over to MS OLE DB Driver for SQL Server (MSOLEDBSQL).  We've tested some of our larger workflows with mixed results.  We saw intermittent issues; some success, but many failures with the "InboundNamedPipe GetOverlappedResult: The pipe has been ended" message.  We also have some users report incomplete & inconsistent runs, even without a failure message.  After working with Alteryx support for quite some time we were told that this driver is not supported by Alteryx and wouldn't be for the foreseeable future.  Eliminated this as a solution in the near term.

2) SQL Native Client 11 has been deprecated by Microsoft beyond 2014.  Since we're on 2016, this is not a solution we are exploring.

3) ODBC:  We've experienced a number of issues that occur whether we use System DSN or DSN-Less connections.

- Queries are creating cursors on the server to return data, and are excruciatingly slow.  A query that in SSMS or using OLE DB that returns 500k records in 4-5 seconds runs for over an hour and returns about 16k rows in that time.  Support suggested appending a SET NOCOUNT ON to the end of every query, and while that does work for my test query, it's a rather clunky solution and we have hundreds of workflows that would require this update.  I've only tested one query using this solution, so I'm not sure if it's a universal answer.

- Every open/edit of the input data component creates & leaves a connection open in the database, in our case, our production databases.  Support has suggested to just have a post script that kills the spid, but we won't give (and I wouldn't recommend) users kill permissions in a production environment.

- I've yet to find a way to return the results of a stored proc to a flow using ODBC.  This works fine in OLE DB (with or without RESULT SETS), but I've tried this a number of ways in ODBC without success.  Others seem to be reporting this issue as well.

So my simple question: for Sql Server shops with only TLS 1.2 available, how are you configuring your Sql Server connections?

Thanks in advance for any discussion and/or suggestions you may have.

Jeff

JeffSpilinek · Answer

At the time we were told that MSOLEDBSQL would not be on their roadmap.  We found it to be buggy. So, we continue on using SQLOLEDB for Alteryx workflows.  I don't see anything in the release notes to indicate that it's been resolved or is listed as a known issue.

I'd be happy to be corrected if I'm in error.

KuZi · Answer

Dear Jeff, we are experiencing exactly the same issue, since your last answer, did you  manage to solve it or are you still waiting since December 2020 for a solution from Alteryx Support? Looking forward for your reply.... best regards, Kurt

JeffSpilinek · Answer

Aaroncornish, my apologies for missing your question.  Microsoft apparently patched SQLOLEDB late last year to support TLS1.2, so we've stuck with the tried and true.