Using TLS 1.2

  • We have SQL Server 2008R2 SP3 and I did the following

    A: Enabled TLS 1.2

    B: Installed SP 3

    B: Installed the Hotfix update for TLS 1.2

    After doing this I can no longer connect to the instance from SSMS 2014 on my workstation and BizTalk Server 2010 cannot connect. We get the following errors

    Microsoft SQL Server Native Client Version 10.50.1617

    Running connectivity tests...

    Attempting connection

    [Microsoft][SQL Server Native Client 10.0]Encryption not supported on the client.

    [Microsoft][SQL Server Native Client 10.0]SSL Provider: The client and server cannot communicate, because they do not possess a common algorithm.

    [Microsoft][SQL Server Native Client 10.0]Client unable to establish connection

    [Microsoft][SQL Server Native Client 10.0]

    A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

    The client and server cannot communicate because they do not possess a common algorithm

    SQLState: 08001

    SQL server Error: 21

    Client unable to establish connection

    We enabled TLS 1.2 and the connection error changed.

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

    An existing connection was forcibly closed by the remote host

    I believe we have to resolve this “A fatal error occurred while creating an SSL client credential. The internal error state is 10013.”

    We are also getting these messages when trying to use an ODBC connection.

    Any help is appreciated.

    Jeff

  • The requirements for TLS 1.2 (server and client) are listed in this MS article which may help:

    https://support.microsoft.com/en-us/kb/3135244

    Sue

  • Thank you very much for the reply. I have looked at this article before, but I think i need to revisit and verify that all the patches, updates and hotfixes are applied.

    I will let you know what happens

    Jeff

  • I am still having trouble getting connected to this server.

    I have a Database server on Windows Server 2008 box running SQL Server 2008R2 SP3 and the hotfix to support TLS 1.2 The SQL Version is 10.50.6542

    I have a biztalk Server on Windows server 2008 box running Biztalk 2010 and the .NET hotfixes have been installed to support TLS 1.2

    My problem is that after enabling TLS 1.2(only) on both servers Biztalk cannot connect, nor can I make an ODBC connection from the Biztalk server to the database server. Heck I cannot even get ODBC to connect from on the Database Server to the installed instance on the same box.

    I am a database administrator, so i am not worried about the Biztalk connection at this poing because the Biztalk admin can deal with that, but I need to get at least the ODBC connection to work so that i can say "the two can communicate"

    The ODBC drivers on both boxes are 6.0.9600(not exactly sure the number)its the one that came with the Windows Server 2008R2 Build

    My question. Does this ODBC driver/version support connecting while TLS 1.2 is enabled?

    On the Microsoft support site the requirement is an ODBC driver "Microsoft® ODBC Driver 11 for SQL Server - Windows" so is this what I need to install before I can make an ODBC connection?

    Please any help is appreciated.

    Jeff

  • Hey Jeff -

    Yes you will definitely need to update the drivers.

    I believe you may also need the TLS 1.2 hotfix - I know we had to use that before:

    https://support.microsoft.com/en-us/hotfix/kbhotfix?kbnum=3144114&kbln=en-us

    Since it's a hotfix, the URL will have you accept and then you do the routine of providing your email, etc and they will send you the link to get the hotfix.

    They went through a lot of fixes, pulling fixes, changing this that, etc but the files you need should all be stable at this point.

    Not sure if you found this article when you were digging into this but it's pretty thorough with all the pain points and the required updates/downloads and it's listed by the SQL Server versions:

    https://blogs.sentryone.com/aaronbertrand/tls-1-2-support-read-first/

    Sue

  • Thank you very much for the quick response.

    Jeff

  • I actually did two things to resolve the issues discussed here and it was actually very simple.  

    First to get the ODBC to connect from the app server to the database all we did was enabled FIPS in the Local Security Policy of both servers

    Second to get the BizTalk administrator to connect to the database we had to use a static port in the SQL Configuration of the database instance.  

    Any feedback is appreciated

    Jeff

  • jayoub - Monday, January 16, 2017 5:19 PM

    I actually did two things to resolve the issues discussed here and it was actually very simple.  

    First to get the ODBC to connect from the app server to the database all we did was enabled FIPS in the Local Security Policy of both servers

    Second to get the BizTalk administrator to connect to the database we had to use a static port in the SQL Configuration of the database instance.  

    Any feedback is appreciated

    Did you disable TLS 1.0/3.0 ? I think that's the tweek for when you disable those but that may not apply since MS updated TLS support for 2008R2. 
    I have always had to use static ports with BizTalk servers for one reason or another. Just one of the many quirks with that one.

    Sue

  • jayoub - Tuesday, December 20, 2016 1:29 PM

    We have SQL Server 2008R2 SP3 and I did the followingA: Enabled TLS 1.2 B: Installed SP 3B: Installed the Hotfix update for TLS 1.2After doing this I can no longer connect to the instance from SSMS 2014 on my workstation and BizTalk Server 2010 cannot connect. We get the following errorsMicrosoft SQL Server Native Client Version 10.50.1617Running connectivity tests...Attempting connection[Microsoft][SQL Server Native Client 10.0]Encryption not supported on the client.[Microsoft][SQL Server Native Client 10.0]SSL Provider: The client and server cannot communicate, because they do not possess a common algorithm.[Microsoft][SQL Server Native Client 10.0]Client unable to establish connection[Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.The client and server cannot communicate because they do not possess a common algorithmSQLState: 08001SQL server Error: 21Client unable to establish connectionWe enabled TLS 1.2 and the connection error changed.A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476An existing connection was forcibly closed by the remote hostI believe we have to resolve this “A fatal error occurred while creating an SSL client credential. The internal error state is 10013.â€We are also getting these messages when trying to use an ODBC connection. Any help is appreciated.

    You first have to enable TLS1.2 at the OS level. What OS version does your 2008 R2 instance run on?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thank you very much for the reply

    We are using Windows Server 2008R2 for both the DB server and the Biztalk application server.  We ended up getting biztalk to work by enabling TLS 1.0 on both db and app server.  It seems that Biztalk 2010 cannot connect using any higher protocol.  

    As far as the ODBC connection - I am not sure what happened with that - my fellow DBA took over the issue, but I think once he finally got Biztalk to connect he no longer cared about the ODBC connection.  I think the ODBC connection would not work because the driver version is so old.  I wanted to update the ODBC drivers, but once Biztalk is working nobody cares any longer.

    The question is, would Biztalk connect using TLS 1.2 if I had updated the ODBC drivers.  It probably would have if Biztalk is actually using the ODBC driver to connect.  If it makes its own connection then it would not.  

    Now the problem they are having is the connection to Oracle for the Send Ports.  Since its Oracle I am not longer involved.    

     Again thanks for the reply and your feedback is always appreciated.

    Jeff

  • jayoub - Wednesday, February 15, 2017 7:36 AM

    Thank you very much for the reply

    We are using Windows Server 2008R2 for both the DB server and the Biztalk application server.  We ended up getting biztalk to work by enabling TLS 1.0 on both db and app server.  It seems that Biztalk 2010 cannot connect using any higher protocol.  

    As far as the ODBC connection - I am not sure what happened with that - my fellow DBA took over the issue, but I think once he finally got Biztalk to connect he no longer cared about the ODBC connection.  I think the ODBC connection would not work because the driver version is so old.  I wanted to update the ODBC drivers, but once Biztalk is working nobody cares any longer.

    The question is, would Biztalk connect using TLS 1.2 if I had updated the ODBC drivers.  It probably would have if Biztalk is actually using the ODBC driver to connect.  If it makes its own connection then it would not.  

    Now the problem they are having is the connection to Oracle for the Send Ports.  Since its Oracle I am not longer involved.    

     Again thanks for the reply and your feedback is always appreciated.

    Biztalk may well be able to communicate back if TLS1.2 is enabled at the OS level.
    Under Windows 2008 R2 TLS1.2 is disabled by default, you actually have to enable it and reboot the servers

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • jayoub - Monday, January 16, 2017 5:19 PM

    I actually did two things to resolve the issues discussed here and it was actually very simple.  

    First to get the ODBC to connect from the app server to the database all we did was enabled FIPS in the Local Security Policy of both servers

    Second to get the BizTalk administrator to connect to the database we had to use a static port in the SQL Configuration of the database instance.  

    Any feedback is appreciated

    Hi,
    I have tried enabling FIPS before.  Though it helps BizTalk Admin console to connect to SQL, during BizTalk runtime instantiating an orchestration fails due to this being enabled.  Did you have the same issue at your end?
    Any help much appreciated.
    Regards,
    APUK

  • apuk - Wednesday, March 29, 2017 2:48 AM

    jayoub - Monday, January 16, 2017 5:19 PM

    I actually did two things to resolve the issues discussed here and it was actually very simple.  

    First to get the ODBC to connect from the app server to the database all we did was enabled FIPS in the Local Security Policy of both servers

    Second to get the BizTalk administrator to connect to the database we had to use a static port in the SQL Configuration of the database instance.  

    Any feedback is appreciated

    Hi,
    I have tried enabling FIPS before.  Though it helps BizTalk Admin console to connect to SQL, during BizTalk runtime instantiating an orchestration fails due to this being enabled.  Did you have the same issue at your end?
    Any help much appreciated.
    Regards,
    APUK

    FIPS also breaks Dynamics AX and SSRS - fwiw.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply