Microsoft is pleased to announce the release of (Transport Layer Security) TLS 1.2 support in all major client drivers and SQL Server releases. The updates made available on January 29th, 2016 provide TLS 1.2 support for SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014. The client drivers that have support for TLS 1.2 are SQL Server Native Client, Microsoft ODBC Driver for SQL Server, Microsoft JDBC Driver for SQL Server and ADO.NET (SqlClient).
The list of SQL Server server and client component updates along with their download locations that support TLS 1.2 is available in the KB Article below:
3135244 TLS 1.2 support for Microsoft SQL Server
You can use KB3135244 to download the appropriate server and client component applicable for your environment. The first build numbers that provides complete TLS 1.2 support in each major release is available in KB3135244 as well. The following tables lists the client driver/components and server components which have TLS 1.2 support. You will need to apply the necessary client component fixes on the server that hosts the SQL Server instance (eg. MS ODBC Driver, SQL Server Native Client) to ensure that the client components installed on the server also support TLS 1.2.
Client Components | Server Components |
SqlClient (.NET Framework 4.6) | SQL Server 2014 |
SqlClient (.NET Framework 4.5.2, 4.5.1, 4.5) | SQL Server 2012 |
SqlClient (.NET Framework 4.0) | SQL Server 2008 R2 |
SqlClient (.NET Framework 3.5/a.k.a (.NET Framework 2.0 SP2) | SQL Server 2008 |
MS ODBC Driver v11 (Windows) | |
SQL Server Native Client (for SQL Server 2012 & 2014) | |
SQL Server Native Client (for SQL Server 2008 R2) | |
SQL Server Native Client (for SQL Server 2008) | |
SQL Server Native Client (for SQL Server 2005) | |
JDBC 6.0 | |
JDBC 4.2 | |
JDBC 4.1 |
|
You can use the PowerShell script from our tigertoolbox GitHub repository to determine which client drivers on your server and client machines require fixes.
Update: March 2, 2016: Please see known issue 6 for the intermittent service terminations that were reported after installing the update.
Update May 27, 2016: Additional fixes needed for SQL Server to use TLS 1.2 with Database Mail is available at KB3135244.
Update January 31, 2017: If you want to check if the TLS/SSL protocol that is being used by the client connection, then you can use the TRACE extended event (under DEBUG channel) to determine the TLS/SSL protocol, cipher, hash and peer address for the connection being made. This capability is available in SQL Server 2016 Service Pack 1 and above. See KB3191296 for more details.
Known Issues
Issue 1
SQL Server Management Studio (SSMS), Report Server, and Report Manager don’t connect to the database engine after you apply the fix for SQL Server 2008, 2008 R2, 2012, or 2014. Report Server and Report Manager fail and return the following error message:
The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)
This issue occurs because SSMS, Report Manager, and Reporting Services Configuration Manager use ADO.NET, and ADO.NET support for TLS 1.2 is available only in the .NET Framework 4.6. For earlier versions of the .NET Framework, you have to apply a Windows update so that ADO.NET can support TLS 1.2 communications for the client. The Windows updates that enable TLS 1.2 support in earlier versions of .NET framework are listed in the table in the “How to know whether you need this update” section of KB3135244.
Issue 2: Reporting Services fails to start
Reporting Services Configuration Manager reports the following error message even after client providers have been updated to a version that supports TLS 1.2:
Could not connect to server: A connection was successfully established to the server, but then an error occurred during the pre-login handshake.
To resolve this problem, manually create the following registry key on the system that hosts the Reporting Services Configuration Manager:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client : REG_DWORD=Enabled, “Enabled”=dword:00000001
Issue 3: Encrypted endpoint communication fails
The encrypted endpoint communication that uses TLS 1.2 fails when you use encrypted communications for Availability Groups or Database Mirroring or Service Broker in SQL Server. An error message that resembles the following is logged in the SQL Error log:
Connection handshake failed. An OS call failed: (80090331) 0x80090331(The client and server cannot communicate, because they do not possess a common algorithm.). State 56.
For more information about this issue, see FIX: The encrypted endpoint communication with TLS 1.2 fails when you use SQL Server.
(Update: February 22, 2016) Known Issue: If you are on a currently using Cumulative Update for SQL Server 2014 and need to use TLS 1.2 for encrypted endpoints for features like Availability Groups, Database Mirroring or Service Broker, then we recommend that you install Cumulative Update 1 for SQL Server 2014 Service Pack 1 or Cumulative Update 8 for SQL Server 2014 which adds support for this particular scenario. This is documented as a known issue in KB3135852.
Issue 4: Encrypted communication with DBM/AG fails
An encrypted connection with Database Mirroring or Availability Groups does not work when you use a certificate after you disable all other protocols other than TLS 1.2. An error message that resembles the following is logged in the SQL Server Error log:
Connection handshake failed. An OS call failed: (80090331) 0x80090331(The client and server cannot communicate, because they do not possess a common algorithm.). State 58.
There might be additional errors that you might encounter in the event logs associated with this issue as shown below.
Log Name: System
Source: Schannel
Date: 3/4/2016 2:09:28 AM
Event ID: 36888
Task Category: None
Level: Error
Keywords:
User: SYSTEM
Description:
A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 40. The Windows SChannel error state is 1205.
Log Name: System
Source: Schannel
Date: 3/4/2016 2:09:28 AM
Event ID: 36874
Task Category: None
Level: Error
Keywords:
User: SYSTEM
Description:
An TLS 1.2 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed.
This issue occurs because Availability Groups and Database Mirroring require a certificate that does not use fixed length hash algorithms, such as MD5. Fixed length hashing algorithms are not supported in TLS 1.2.
For more information, see FIX: Communication using MD5 hash algorithm fails if SQL Server uses TLS 1.2.
Issue 5: SQL Server Setup fails
SQL Server setup fails when TLS 1.2 is enabled
When you try to install Microsoft SQL Server 2012 or SQL Server 2014 on a server that has Transport Layer Security (TLS) version 1.2 enabled, you may encounter the following issues:
- If the version of SQL Server that you’re trying to install doesn’t contain the fix to enable TLS 1.2 support, you receive the following error message:Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
- If the version of SQL Server that you’re trying to install does contain the fix to enable TLS 1.2 support, you receive the following error message:A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Named Pipes Provider, error: 0 – No process is on the other end of the pipe.)In both of these situations, the installation fails.Please refer KB3135769 for the workaround for the issue.
Issue 6: Intermittent Service Termination
The following SQL Server database engine versions are affected by the intermittent service termination issue that is reported in KB3146034. For customers to protect themselves from the service termination issue, we recommend that they install the TLS 1.2 updates for Microsoft SQL Server that are mentioned in this article if their SQL Server version is listed in the following table.
SQL Server release | Affected version |
SQL Server 2008 R2 SP3 (x86 and x64) | 10.50.6537.0 |
SQL Server 2008 R2 SP2 GDR (IA-64 only) | 10.50.4046.0 |
SQL Server 2008 R2 SP2 (IA-64 only) | 10.50.4343.0 |
SQL Server 2008 SP4 (x86 and x64) | 10.0.6543.0 |
SQL Server 2008 SP3 GDR (IA-64 only) | 10.0.5544.0 |
SQL Server 2008 SP3 (IA-64 only) | 10.0.5894.0 |
Issue 7: Database Mail does not work
Database Mail does not work with TLS 1.2
Database Mail fails with the following errors:
Agent Log:
Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException:
Mail configuration information could not be read from the database.
….
….
Unable to start mail session.
See the section “Additional fixes needed for SQL Server to use TLS 1.2” in KB3135244.
Issue 8: SQL Server service does not start
You get the following error after disabling all other protocols except TLS 1.2 on the server while trying to start the SQL Server database engine service.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x139f, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The group or resource is not in the correct state to perform the requested operation.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
The above errors are reported because the SQL Server client driver fixes were not applied on the server. Please refer KB3135244 and apply the applicable client driver fixes on the server.
A recording of the TLS 1.2 session delivered the Security Virtual Chapter for PASS is available below.
Does this include support for TLS in Database Mail (SMTP) in SQL Server 2008 R2?
Could you please share any error that you are receiving using the contact option on the right side-bar at https://blogs.msdn.microsoft.com/sql_server_Team/
Please see a list of .NET fixes above for TLS 1.2 to work with Database Mail.
I have installed the TLS1.2 patch for 2008 R2 and installed .Net Framework 4.6. The SQL services no longer start up automatically. (set to Automatic via services) and I cannot connect via SSMS to the instance. I can connect via the command prompt. When I enable TLS 1.0 on the sever via the registry setting I can connect to the instance via SSMS.
Error I receive is “Pre-Login handshake” error “No process is on the other end of the pipe”
It appears this hotfix might not support the disabling of TLS 1.0 connectivity.
Could you please post the error message that is present in the SQL Server Errorlog when the service does not start automatically? You will need to install the necessary .NET update for SSMS to connect to the SQL Server database engine. The reason for this is the Management Studio for older releases require an older SqlClient. The download links for the ADO.NET updates are available in KB3135244.
When will a new hotfix be available ? According to the PCI policy the disablement of TLS 1.0 must occur by June 2016, which does not leave a lot of time to test and implement.
We will be publishing an update on the investigation as soon as it is complete along with an update on the mitigation steps.
Owen you have until June 30th, 2018 now, they changed that date at the end of 2015. You will have to provide risk and mitigation plan to your PCI assessor, but you have time to test and implement or migrate properly now.
Thanks for the status update.
I installed the 2008 R2 SP3 based hotfix 489678_ENU_x64 (sqlncli.msi).
I disabled SSL 3.0 and TLS 1.0 in the registry and enabled TLS 1.2.
The SQL service will not start.
Event log says: “TDSSNIClient initialization failed with error 0x139f, status code 0x80. Reason: Unable to initialize SSL support. The group or resource is not in the correct state to perform the requested operation.”
However, have I installed the server component at all? or have you removed it from the hotfix package? (per your Feb-13 message)
Of course if you removed it then me disabling all protocols besides TLS 1.2 will obviously cause it not to work.
Can you kindly clarify the situation?
Additionally, when to expect a working hotfix for 2008 R2 SP3?
Thanks!
The update that you have installed 489678_ENU_x64 is the update for the SQL Server Native Client (client driver). You have not installed the database engine hotfix package. As mentioned in the blog post, we are currently investigating an issue reported by two customers. We will enable the downloads as soon as we have concluded the investigation of the reported issues. Please keep a watch on this blog post for updates.
I applied this update to our SQL 2008 instance on the 12th and have now uninstalled it. We have seen Issues with the instance when under load. The SQL services stopped responding.
This error in the event log :
SQL Server Assertion: File: , line=2760 Failed Assertion = ‘pvb->FInUse ()’. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
Then later loads of information entries in the event log :
New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%%. System Idle: 99%%.
Alicia: We have been notified about this particular issue and this already under investigation. We will provide an update on this blog post about the resolution on this issue. Thank you for reporting this issue to us.
Hi,
I installed sqlncli (487893_ENU_x64_zip) forMicrosoft SQL Server 2014 (SP1-GDR) (KB3098852) and SQL service now starts automatically without an error. I have only TLS1.2 enabled and i can’t connect to SQL using SQl Management Studio. I get the error: provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host. If i enable TLS 1.0 then i can login to the Database Engine with windows Authentication. Is there any known issue regarding this behavior?
This issue occurs because SSMS, Report Manager, and Reporting Services Configuration Manager use ADO.NET, and ADO.NET support for TLS 1.2 is available only in the .NET Framework 4.6. For earlier versions of the .NET Framework, you have to apply a Windows update so that ADO.NET can support TLS 1.2 communications for the client. The Windows updates that enable TLS 1.2 support in earlier versions of .NET framework are listed in the table in the “How to know whether you need this update” section of KB3135244. The path that you installed only updates the SQL Server Native Client. You will also need to install the client driver update for “Microsoft ODBC Driver for SQL Server“.
Amit,
Thank you for the information. I removed .Net 4.6 and installed 3.5 and applied the hotfix mentioned and this solved all my problems.
Because of the DROWN attack we are being forced to use only TLS 1.2 on Windows 2008 servers that run SQL Server 2008 R2. Our ISP is testing what protocols are enabled and blocking servers running anything other than TLS 1.2. So we need the update for SQL Server 2008/R2 now.
We have re-enabled the download links for SQL Server 2008 and SQL Server 2008 R2. You should be able to download the packages.
The article mentions 3 hotfixes for NF 4.5/4.5.1/4.5.2 3099842/3099844 and 3099845. Are all of these needed to be installed?
Thanks
Chris
Chris: If your application uses .NET client drivers and you only want to use TLS 1.2, then you need to install the relevant .NET fixes for the client drivers.
Amit,
None of the 3 earmarked for NF 4.5.2 indicate and OS. So I will assume that I probably need to install all 3 for W2K8R2 and Win 7.
Chris
The following should work for you for Win2K8R2 and Win7: https://support.microsoft.com/en-us/kb/3099845. Reply back to this if you have any issues.
Is it “Enabled”=dword:00000001 as per ‘What is the correct registry setting to enable TLS 1.2 for SQL Server communication?’ or as described in issue 2 Enabled = 0xffffffff. https://support.microsoft.com/en-us/kb/3135244
The correct setting is:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
“DisabledByDefault”=dword:00000000
“Enabled”=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
“DisabledByDefault”=dword:00000000
“Enabled”=dword:00000001
I will correct the post accordingly.
I tried to install SQL Server Native Client (for SQL Server 2008 R2) from this page:
https://support.microsoft.com/en-us/kb/3135244
The file is SQL2008R2_SP3_COD_SNAC_x86_1033
But my system said:
“Installation… failed because a higher version exists on the machine”
My current version of SQL Server Native Client is 10.53.6542.0
What version is the file SQL2008R2_SP3_COD_SNAC_x86_1033 ?
If you are on the build 6542, then you are on the latest build. If you need to install only the client driver update for client machines, then you need to download the appropriate client driver from the client drivers hotfix link in the second table in KB3135244.
Great thanks – btw https://support.microsoft.com/en-us/kb/245030 mentions 0xffffffff too
David
Correct but that is for older operating systems. KB245030 mentions the registry settings for later version of windows. Look at the section “For later versions of Windows” in KB245030.
Hello Amit,
I have SQL server 2008 R2 Ent insalled on Windows server 2008 R2 Ent. I am unable to login to SQL server locally after the installation. Getting below error:
Connection handshake failed. An OS call failed: (80090331) 0x80090331(The client and server cannot communicate, because they do not possess a common algorithm.). State 56.
The error led me to this article and I updated my DB engine to 10.50.6542.0. But still unable to login. What should I do in order to allow my SSMS to connect locally.
Amit,
Just applying 3099845 didn’t allow me to use SSMS to access the upgraded server. SP3 build 6542 now comes up with TLS 1.0 disabled. The other two are not for W2K8R2 SP1. What do I do now without having NF 4.6 or 4.6.1 installed?
Chris
I have now got this to work by having the NF 3.5/2.0 hotfix installed. I can now use SSMS on the server to manage SQL.
Chris
Good to know Chris. The KB and client/server OS combinations are given below:
3099842 is for Windows 8.1 and Windows Server 2012 R2
3099844 is for Windows 8 and Windows Server 2012
Correction: 3099845 should be for Windows 7 SP1 and Windows Server 2008 R2.
Adding more details to my earlier comment:
I have SQL server 2008 R2 Ent insalled on Windows server 2008 R2 Ent. I am unable to login to SQL server locally after the installation. Getting below error:
Connection handshake failed. An OS call failed: (80090331) 0x80090331(The client and server cannot communicate, because they do not possess a common algorithm.). State 56.
The error led me to this article and I updated my DB engine to 10.50.6542.0. But still unable to login. What should I do in order to allow my SSMS to connect locally. Also the second issue is SQL agent service is not starting. It fails with below error:
Agent could not be started ( unable to connect to server (local), SQLserveragent cannot start).
How to fix this as well? Below is my environment details:
Windows server 2008 R2 Ent. SP1
SQL Server 2008 R2 Ent (10.50.6542.0)
.Net version 4.5.51209
SCHANNEL registry status:
SSL 2.0: Client: DisabledByDefault = 1 Enabled = 0 Server: DisabledByDefault = 1 Enabled = 0
SSL 3.0: Client: DisabledByDefault = 1 Enabled = 0 Server: DisabledByDefault = 1 Enabled = 0
TLS 1.0: Client: DisabledByDefault = 0 Enabled = 1 Server: DisabledByDefault = 0 Enabled = 1
TLS 1.1: Client: DisabledByDefault = 0 Enabled = 1 Server: DisabledByDefault = 0 Enabled = 1
TLS 1.2: Client: DisabledByDefault = 0 Enabled = 1 Server: DisabledByDefault = 0 Enabled = 1
Please let me know if you need any further details.
You need to install the client driver fixes installed on all the clients and the server. The download link is available on KB3135244.
Update:
I made some progress on it. I installed .Net version 4.6.01055 and ADO.NET – SqlClient (.NET Framework 3.5/.NET Framework 2.0 SP2) referring link https://support.microsoft.com/en-us/kb/3135244. I am now able to connect SQL server using SSMS locally and also able to start SQL agent service. Yipee.
But now the new issue is appearing. I can’t connect the server remotely using windows authentication. I get below errors in windows eventviewer logs when I try to connect it. Also, no errors in SQL server error logs.
Log Name: System Source: Schannel Event ID: 36888 Task Category: None Level: Error Keywords:
User: SYSTEM Description: The following fatal alert was generated: 40. The internal error state is 1205.
Log Name: System Source: Schannel Event ID: 36874 Task Category: None Level: Error Keywords:
User: SYSTEM Description: An TLS 1.0 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed.
enter image description here
You need to update the SQL Server client drivers on the client machines that you are connecting from. The client driver download links are available in KB3135244.
This is because the client drivers on the client machines have not been updated. You need to download the patches mentioned for the client drivers in KB3135244 and install them on the clients.
We have SQL Server instance where we turned off TLS 1.0 after following all the necessary updates under KB3135244.
We are able to connect locally and remotely to the instance without any issues using SSMS. However, we have an issue running any reports on a remote SSRS to the instance running on TLS 1.2. The connection is successful in SSMS, SQL Data Tools, and even Report Builder. Once deployed to the SSRS server, we receive the following error:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)
What are we missing?
How are you viewing the SSRS report? And what OS is the SSRS instance running on? The SSRS report server also needs the client component updates listed in KB315244 to be installed.
When previewing the report in SQL Data Tools or Report Builder, it works fine. Once deployed to SSRS and ran through the SSRS web-based GUI, the data source test fails giving the error above.
The SSRS server is running SQL Server & SSRS running SQL Server 2012 (11.0.6523), .NET Framework 4.6.1, SNAC 11.0.6523 (based on KB315244) on Windows 2012 R2
The data source in the report is running SQL Server 2014 (12.00.4439) with TLS 1.0 is disabled also on Windows 2012 R2
Testing the same connection from the SSRS server to the Data Source using a .udl connection gives the error:
ConnectionOpen (SECDoClientHandshake()). SSL Security error.
Test connection failed because of initializing provider.
I would like to add that this is not environment specific to the servers mentioned above. Testing this same behavior on different SSRS servers and Data Sources running with TLS 1.0 disabled, produces the same results.
Have Microsoft confirmed that SSRS can connect via the SSRS web GUI to data sources running with TLS 1.0 disabled?
Have you installed the applicable .NET fixes mentioned in KB3135244? If yes and this is not working, please open a Microsoft Support incident to determine what is missing from the environment due to which the connections are not working. And yes, we have tested this scenario with all other protocols being disabled and only TLS 1.2 being enabled. The list of known issues highlighted in this post are based on the testing done before we released these patches publicly.
After working with Microsoft’s support, the issue is specific to Windows Server 2012 R2. Some parts of SSRS depend on .NET Frameworks 2.0. The following fix is required to correct the error mentioned above with TLS 1.2.
https://support.microsoft.com/en-in/kb/3106993
Good to know that this is resolved. This is mentioned as a required client fix under “ADO.NET – SqlClient (.NET Framework 3.5/.NET Framework 2.0 SP2)” in KB3135244.
Both our DEV and QA DB Servers are SQL Server 2008 R2 SP3 and Windows 2012 R2, up to date on all Windows Updates.
DEV Server:
– SQL Server 2008 R2 SP3 TLS 1.2 Update
— Installed successfully
— No other actions needed
QA Server
– SQL Server 2008 R2 SP3 TLS 1.2 Update
— Installed successfully
— Attempting to connect gave the following error: “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.)”
– Installed Hot Fix, Windows8.1-KB3106993-x64 (https://support.microsoft.com/en-us/kb/3106993)
— Same result
– Installed update to “Microsoft ODBC Driver for SQL Server”
— “A connection was successfully established with the server, but then an error occurred during the pre-login process. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)”
– Uninstalled all updates at this point to revert to working QA server
Do I now need to try again and go down the route of modifying the registry settings? Hardly ideal if the roll-out to our production cluster fails similar to QA?
Have I ask other options Amit?
Which driver are you using to connect?
You need all the client and the server updates (MS ODBC driver, SQL Native Client see “Client component downloads” in KB3135244) applied on the server that is running SQL Server. Did you install this: Microsoft SQL Server 2012 Native Client – QFE?
Hi Amit,
We are using SSMS at the moment to test our connection.
When I installed the SQL Server 2008 R2 SP3 TLS 1.2 Update, the version of SQL Server went from 10.50.6220.0 to 10.50.6542.0. The hot fix for SQL Server Native Client (for SQL Server 2008 R2) applies to an earlier SQL Server version of 10.50.6537.0 and would not install due to this.
The MS ODBC drivers hot fix to v11 did install as did the update to .NET Framework 2.0. However, the updates to both .NET Framework 4.0 and 4.5 did not install saying they did not apply to the computer.
The OS is Windows Server 2012 R2.
If your Native Client is on the build 6542, then you are on the latest build. If you need to install only the client driver update for client machines, then you need to download the appropriate client driver from the client drivers hotfix link in the second table in KB3135244. If you connection is failing with SSMS, then it needs the .NET update. You need the .NET fix from https://support.microsoft.com/en-us/kb/3106993 as SSMS for older releases of SQL Server does not use .NET 4.0. Another option to check if TLS 1.2 connection is working is:
1. Verify if the SQL Server database engine starts after disabling all protocols except TLS 1.2. I am assuming this is working based on your comments.
2. Install the latest SSMS from https://msdn.microsoft.com/en-us/library/mt238290.aspx and check if that is able to connect to the database engine
3. Install the .NET fix that I mentioned above
Hi Amit,
We ran the installer again for KB3144114 on our QA database server and again it was successful.
This time however, we were able to log in locally and remotely and it looks like everything has gone smoothly this time.
This is good of course but a little strange and leaves us in a worrying situation deploying the update to our production cluster nodes.
Will update here hopefully after we have deployed out the update successfully 🙂
Thanks for your assistance and feedback. It was very helpful.
Good to know that this is working. Did you check if the update last time had increased the build number of the DLLs and EXEs to the build numbers mentioned in KB3135244. That would be the most sure way to determining if the fixes are applied correctly on the server along with the fact that the installers did not report any errors.
Hi Amit,
How do I apply this update to a 2 node cluster? Should the update be applied to the passive node first, if ok is good, fail over from active to passive and then install the update on the new passive node and fail back again?
Assuming of course, all goes well with the installations?
Thanks,
Brian
You will need to follow the same update method and process that you follow with any Service Pack or Cumulative Update.
Hi Amit,
Thank you for all your help. To update, we applied KB3144114 to our cluster nodes (in-active nodes first) and all went smoothly.
Your help was invaluable.
Great to know that everything worked out for you! 🙂
Hi Amit,
thanks for the post. I have a server with 2012 R2. I installed the SQL Server management studio SP 1 CU6.
.Net version is 4.5.51650 according to [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full\1033]
I have the problem that the SQL Server management studio says that the connection was forcibly closed by the remote host.
On other servers, when I installed .net 4.6, the issue was resolved.
On the particular server, however, I cannot install .Net 4.6.
The update which is suggested by https://support.microsoft.com/en-us/kb/3135244 for .Net 4.5.2 cannot be installed on my machine. It says that that version is not applicable to that machine.
What else should I do to be able to connect with the SQL Server Management Studio 2014 via TLS 1.2?
Thanks
KR
Chris
You need to install “ADO.NET – SqlClient (.NET Framework 3.5/.NET Framework 2.0 SP2)” from KB3135244.
Would this enable TLS support for SSIS (http connection manager in use with a webservice task).
We’ve fully patched SQL for TLS support and are seeing failures with SSIS connecting to a https web service that is only available via TLS 1.1 and 1.2.
So if anyone else has a similar problem, the following registry settings enable TLS 1.1 and 1.2 support by default for the .net framework 4-4.5, which SQL 2012 uses. By adding these the webservice task/http connection manager for SSIS can again connect to a https site that only supports TLS 1.1 or 1.2. For reference this worked with server 2012 running SQL 2012.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
“SchUseStrongCrypto”=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
“SchUseStrongCrypto”=dword:00000001
Thanks for the resolution comments Philip. The SSIS task makes use of the .NET components which are outside the control of SQL Server. These settings are applicable for any https connection that needs to use TLS 1.2. If you are on older Windows OS, then you would need to explicitly enable TLS 1.2 on the server as well using the registry settings mentioned in KB3135244.
Understood, we did have TLS 1.1 and 1.2 enabled at the server level per your note. Can you help explain why installing 4.6.1 .net framework didn’t resolve the issue? 4.6.1 has TLS 1.1 and 1.2 enabled by default and after disabling TLS 1.0 on the SQL server our windows 8.1 clients couldn’t connect to SQL until .net 4.6.1 or this HF for 4.5.x was installed (https://support.microsoft.com/en-us/kb/3099842). Either option enables TLS 1.2 support in the .net 4.x framework but again, installing 4.6.1 DID NOT fix the web service connection problem. I would have thought it would given your comments about SSIS using the .net components outside the control of SQL. Also surprised it’s causing my DB mail issue I commented on below. We have 4.6.1 installed and the registry fix mentioned here but database mail it’s still failing to connect to SQL.
Further I’ve VERY curious to know why disabling TLS 1.0 impacts connections to SQL that are NOT encrypted.
The client drivers used to connect to SQL Server and the client tools like SSMS in releases prior to SQL Server 2016 use older versions of .NET (<4.6). You need to install the .NET updates mentioned in KB3135244 for the older client drivers to work with TLS 1.2.
Ok, we have another issue I have no fix for. After installing the needed SP/CUs to enable TLS 1.2 support and DISABLING TLS 1.0 on the server (due to security requirements) Databasemail no longer functions. We get this error in the application event logs. Any help to resolve would be appreciated.
Seems like Microsoft hasn’t fully vetted all SQL components for TLS 1.2 support regardless of these patches.
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: There was an error on the connection. Reason: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.), connection parameters: Server Name: HERMES\RMSDEV, Database Name: msdb
Data: System.Collections.ListDictionaryInternal
TargetSite: Void OpenConnection(Microsoft.SqlServer.Management.Common.SqlConnectionInfo)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName, String dbName, String userName, String password, String appName, Int32 connectionTimeout)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName, String dbName, String userName, String password, Int32 connectionTimeout)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel, Byte[] encryptionKey, Int32 connectionTimeout)
2) Exception Information
===================
Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 20
LineNumber: 0
Number: 233
Procedure: NULL
Server: HERMES\RMSDEV
State: 0
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)
Data: System.Collections.ListDictionaryInternal
TargetSite: System.Data.ProviderBase.DbConnectionInternal GetConnection(System.Data.Common.DbConnection)
HelpLink: NULL
StackTrace Information
===================
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)
For clarification:
OS: 2012 w/ .net 4.6.1 framework installed and the registry entries that enable TLS 1.1 and 1.2 for the .net 4.5 framework in place (see previous post above)
SQL: Microsoft SQL Server 2012 (SP3-CU2) (KB3137746) – 11.0.6523.0 (X64)
This is a different issue. Please refer to the fixes in the “Additional fixes needed for SQL Server to use TLS 1.2” section added in the post. Database Mail used .NET APIs in older .NET framework versions which did not support TLS 1.2. If you apply the appropriate fix mentioned in the post, then Database Mail should work with TLS 1.2.
Thanks, we installed the KB referenced for this OS and SQL version (KB3154519). The same error occurs when databasemail tries to run. We also have the registry entry in place mentioned in the KB for both 32-bit and 64-bit frameworks.
Please advise if we’ve missed anything to ensure this functions when TLS 1.0 if off at a server level.
We ended up fixing the issue. Had to install the .net 3.5 hotfix for the ADO.net SQL client. There needs to be a matrix of OS/SQL/.net/etc versions that tells users exactly which patches are required for FULL TLS 1.2 support across all SQL components.
I’ve never seen a “packaged” product update that has so many external dependencies. From a client/user point of view, just installing the SQL patch on the server should have been enough. If not the install should look for the other “required” patches and alert the user if they aren’t installed.
Again, seems like this was a little rushed to market.
Philip: Thank you for your feedback. We have added all the fixes required to make TLS 1.2 work at https://support.microsoft.com/en-us/kb/3135244
A user can choose to install multiple client driver and side-by-side .NET versions on a Server/Client machine. Depending on what you have installed on your client and server machines, you will have to install the appropriate components. Not all the component fixes mentioned here are SQL components. Some of these are .NET components which are used by Web Servers and Client OS which are not shipped as part of the Database Engine. The patch that you install for SQL Server only patches server components like Database Engine, Reporting Services, Analysis Services, Integration Services. There has been no change in our deployment and packaging in this regard for this update.
The .NET patch is for the client driver. If you still have concerns about this, please feel to share your contact details using our contact form on http://aka.ms/sqlserverteam and I will get in touch with you.
Hi Phil,
Can’t thank you enough for providing the fix for this, i had the exact same issue, and was close to banging my head against the wall.
Cheers mate.
TITLE: Connect to Server
Cannot connect to DELL-PC.
——————————
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
How we can fix an ERROR in SQL SERVER 2008 R2
Please ,Give Your Feedback….?
This is quite a generic error. Are you able to ping the machine that is hosting the SQL Server instance? Also, for client connections to be successful using TLS 1.2, then you need to install the client driver updates mentioned in KB31354244: https://support.microsoft.com/en-us/kb/3135244
I have a Windows Server 2008 R2 Standard Service Pack 1 64-bit with Microsoft SQL Server 2008 (SP3) – 10.0.5538.0 (x64) Express Edition w/ Advanced Services. I ran the Service Pack 4 update – SQLServer2008SP4-KB2979596-x64-ENU (KB2979596), Security Update for SQL Server 2008 Service Pack 4 (KB3045311), SP4 TLS 1.2 Update – 490209_intl_x64_zip.exe (KB3144113), and set the registry as follows;
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
“DisabledByDefault”=dword:00000000
“Enabled”=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server]
“DisabledByDefault”=dword:00000001
“Enabled”=dword:00000000
Now when my IIS app tries to connect to the DB it says it’s unavailable. The SQL Active Directory Helper Service, SQL Server Agent, and SQL Server Browser are all disabled and all the options to restart or start are greyed out. I’ve gone through all of the Issues listed above but most referred me back to KB3144113 which I already applied. Any help would be appreciated.
What connectivity driver is your IIS application using? Depending on the driver, you need to apply the client driver fixes listed in the “Client component downloads” section of KB3135244.
I’ve installed SP4 for SQL Server 2008, the .NET hotfix for .NET 4.5x, the hotfix for disabling TLS 1.0 and 1.1, the Win 2008 R2 hotfix and my existing Microsoft OLEDB Provider for SQL Server does not work with TLS 1.0 disabled. The SQL Server Native Client 10.0 does work. I’m trying to avoid having to modify a lot of SSIS packages to switch OLEDB drivers. Does the Microsoft OLEDB Provider for SQL Server support TLS 1.2? I get SSL connection failures logged to the event history.
You will need to switch to one of the providers listed in KB3135244.
Is there an audit tool for SSIS packages that will identify the packages that are using this Microsoft driver or some way to identify which packages will fail without disabling TLS 1.0 and seeing what breaks?
You need to check the .NET version and the client drivers installed on the machine which runs the SSIS packages. Once you find the client drivers and the .NET version installed on the machine, you need to install the appropriate patches from KB3135244.
Is there a driver for Oracle that is supported using TLS 1.2 (I don’t see one on the list provided above of supported drivers)? After applying the changes I can no longer communicate with Oracle servers using the available drivers in SSIS. All necessary updates have been done and I am not having any issues communicating with other SQL servers that are configured for TLS 1.2.
The drivers for Oracle are provided by Oracle Corporation and their documentation should mention their supportability for TLS 1.2. What is the error that you are receiving and the is the driver version you are using?
Any help can help on tls1.2 issue with classic asp.
We have installed tls 1.2 in SQL server 2012(OS -windows 2012 R2) and enabled ‘encrypt = true’ in oledb connection string in my classic asp connection string. connection is failing. is tls 1.2 is compatible with classic asp application?
my connection string in classic asp application.
“Provider=SQLOLEDB.1;;Persist Security Info=False; Initial Catalog=DBName; Data Source=ServerName;Use Procedure for Prepare=1;Auto Translate=True; Packet Size=4096 ;encrypt=yes; TrustServerCertificate=True; User ID=webUser; Password=XXXXX”
SQLOLEDB will not receive support for TLS 1.2. You will need to switch your driver to one of the supported drivers listed in https://support.microsoft.com/en-us/kb/3135244
When using the ODBC 13 driver connecting to SQL Server 2012 CU4 and all client components updated we noticed this behavior.
When setting up a DSN using ODBC Administrator, we were unable to switch databases from default when the SQL server was hardened to use only TLS 1.2 and ECDH Key Exchanges, AES Ciphers, All hashes.
Correction to my comment. It was the client side that was hardened to use only TLS 1.2 and ECDH Key Exchanges, AES Ciphers, All hashes that would create this behavior. We found that if the SQL server was hardened or not we were able to still switch databases in ODBC Administrator.
ODBC 13 for SQL Server supports TLS 1.2 out-of-the-box. You don’t need any updates to enable TLS 1.2 for this version of the driver.
Good to know and thanks for responding!
The issue experienced when using ODBC 13 against a SQL instance revealed an inability to switch databases during the ODBC Administrative new DSN setup wizard. This behavior was only noticed with the SQL server was only using the TLS 1.2 protocol, ECDH Key Exchange, AES Ciphers and all hashes under SCHANNELS registry KEYs. Once we default those same settings to OOBE, the issue experienced with database switching is resolved.
Hi.
I have TMG 2010 SP2 RU5 with SQL 2008 SP4
We disabled the TLS 1.0 .
I have applied the Hotfixes and SQL DB and sQL reporting [ISARS] are stating now ok.
But i am still getting the following error/events
event id : 31288
SQL Server Reporting Services could not be configured for Forefront TMG. Restarting the Microsoft Forefront TMG Job Scheduler service may resolve this issue. Reporting Services error information: [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.
Q. What is missing here.my SQL version are at the following.
OS : W2008 R2 SP1, SQL 2008 SP4
2016-10-18 18:25:41.57 Server Microsoft SQL Server 2008 (SP4-OD) (KB3144113) – 10.0.6547.0 (X64)
SQL naticlient etc : 10.4.6547
.net : 3.51
.net 4.5.2 hotfix rollup KB3099845
tmg 2010 alerts :
Description: The daily summary for day “10/17/2016” was not created. This may cause the report for this period to be inaccurate. Verify that no priorreporting configuration alerts exist, and that the reporting services on the designated Forefront TMG report server are running and accessible from all the array members. Use the source location 1001.213.7.0.9193.644 to report the failure.
The error message shows that TLS 1.2 is not understood by the driver. Could you please install the client driver updates on the Reporting Services Server and the TMG server to ensure that all SQL connectivity drivers used in the environment support TLS 1.2. Also SQL Server 2008 uses an older version of .NET. You need to the older .NET fixes for the ADO.NET driver. KB3135244 has all the client driver/.NET updates required. Please apply those. If that does not work, please open a support case so that our engineers can help troubleshoot the root cause.
Thanks. But I have applied the required updates. my version is
Microsoft SQL Server 2008 (SP4-OD) (KB3144113) – 10.0.6547.0 (X64)
Feb 22 2016 19:04:50
Copyright (c) 1988-2008 Microsoft Corporation
Express Edition with Advanced Services (64-bit) on Windows NT 6.1
(Build 7601: Service Pack 1)
The SQL reporting service is on the same server as TMG 2010 is running it is bundled.
following fixes were applied and fixed lot of issues after enabling the tls1.2. The SQL services aer not stating but after installing the fixes all the Services are starting.
SQL Server 2008 SP4 TLS 1.2 Update
Hotfix rollup 3099845 for the .NET Framework 4.5.2, 4.5.1, and 4.5
SQL Server Native Client (for SQL Server 2008) [SQL Server 2008 Native Client (x86 and x64)] – 489744
Microsoft ODBC Driver 11 for SQL Server – Windows
Support for TLS v1.2 included in the .NET Framework version 3.5.1
KB3154518-x64 Support for TLS System Default Versions included in the .NET Framework 3.5.1 on Windows 7 SP1 and Server 2008 R2 SP1
—-
tried to install some updates again it says not applicable or already installed.
Windows8.1-KB3106993-x64_2 .net2 hotfix-not applicable.msu
Windows8.1-KB3106993-x64 not applicable.msu
example :
Product: Microsoft SQL Server 2008 Native Client — Installation of SQL Server 2008 Native Client failed because a higher version already exists on the machine. To proceed, uninstall the higher version and then run SQL Server 2008 Native Client Setup again.
================
Below are the pending errors:
event id : 30973 Description: The daily summary for day “10/23/2016” was not created. This may cause the report for this period to be inaccurate. Verify that no prior reporting configuration alerts exist, and that the reporting services on the designated Forefront TMG report server are running and accessible from all the array members. Use the source location 1001.105.7.0.9193.644 to report the failure.
event id : 31288
SQL Server Reporting Services could not be configured for Forefront TMG. Restarting the Microsoft Forefront TMG Job Scheduler service may resolve this issue. Reporting Services error information: [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.
==================
a case with MS support was opened but the engineer is rooming around the same hotfix things so far since a week now. They got the logs but seems not checked so far.
Have you checked if the registry keys have been created correctly. Also, look through the issues listed in KB3135244. Reporting Services requires specific registry keys. Also, what are the errors in the SQL Server Reporting Services execution logs?
We are having very strange problem with our production cluster after installing SP2, CU1 (5511) on SQL Server 2014. Operation Systems is Windows Server 2012 R2. Every morning we not able to connect to SQL server via SSMS remotely or locally. Error message saying “Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake ackhnowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time” if I try from my PC running SSMS. When I login to server running SQL Server and try connecting a bit different error: “A connection was successfully established with the server, but then an error occurred during the pre-login handshake.” All SQL Services seem to be running. TCP, port enabled.
Rebooting/restarting the SQL Service resolves the issue and it works smoothly until next morning. Noticed that if I don’t do anything everything works fine after a while (in 1 hour max). The problem is only with one node of active-active cluster. Second node works fine. Interchanging the roles in cluster shows that the problem is only with one node. We have identical test cluster environment where everything also works fine. All software versions are identical. I noticed that we have this problem after applying last SQL patch and Windows Security Update. Tried rollback to previous patch level of SQL and Windows with no luck.
Any advice on this would be appreciated.
This doesn’t look to a TLS 1.2 related issue because if TLS 1.2 connectivity was failing, it should fail always. Troubleshooting this would require analysis of the SQL Server and Windows event logs to start with. I would recommend opening a support case for this so that our engineers can help you troubleshoot this issue.
Hi Amit,
We are using SQL200R2, I have enabled TLS 1.2 for both Server and Client in the registry editor.
Also I have installed KB3144114 and KB3099845 from KB3135244.
If TLS 1.0 is disabled I am getting below error message in SSMS
“A connection was successfully established with the server, but then an error occured during the pre-login handshake. (provider: shared memory provider, error:0 – No process is on the other end of pipe.) (Microsoft SQL Server, Error: 233).
I wasn’t able to update SQL server native client because its saying the current version is a higher version, 10.53.6542.0
Can you please give suggestion what should I do to get SSMS works with TLS 1.2?
Thanks
What is the operating system that you are using? Windows Server 2008 and Windows Vista will allow you to install the fix but does not have support for TLS 1.2.
Hello,
I have an instance on a windows 10 (previously windows 8.1 but I experienced the same problem on that OS) for which I systematically have to enable FIPS compatible algorithm in my local security policy in order to get a valid connection to my SQL server from ODBC.
My SQL instance is an SQL Server Express 2014 SP1 for x86 (version 12.1.14491.0). If I enable FIPS140 compatible algorithms, everything works fine but if I disable it I got a connection failed error
—————————
Microsoft SQL Server Login
—————————
Connection failed:
SQLState: ‘01000’
SQL Server Error: 772
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (SECDoClientHandshake()).
Connection failed:
SQLState: ‘08001’
SQL Server Error: 18
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security error
—————————
OK
—————————
Thanks for any indication that might help to solve this since enabling FIPS140 algorithms might prevent other programs to run correctly (has I have experienced it).
Here are some additional information about my setup, extracted from SQL Server Installation Center -> Tools -> SQL Server features discovery report:
Product Instance Instance ID Feature Language Edition Version Clustered Configured
Microsoft SQL Server 2014 MyDB MSSQL12.MyDB Database Engine Services 1033 Express Edition 12.1.4491.0 No Yes
Microsoft SQL Server 2014 Management Tools – Basic 1033 Express Edition 12.1.4491.0 No Yes
Microsoft SQL Server 2014 Management Tools – Complete 1033 Express Edition 12.1.4491.0 No Yes
Microsoft SQL Server 2014 Client Tools Connectivity 1033 Express Edition 12.1.4491.0 No Yes
Microsoft SQL Server 2014 Client Tools Backwards Compatibility 1033 Express Edition 12.1.4491.0 No Yes
Microsoft SQL Server 2014 Client Tools SDK 1033 Express Edition 12.1.4491.0 No Yes
Could you please check if the registry keys are created appropriately as mentioned in https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server
Hello Amit,
Sorry for the late reply, indeed, the registery key values are set according the article you mentioned.
Is there any possible incompatibilities between the support of TLS 1.2 and implementations that can be achieved to communicate with the SQL database? The application we are trying to migrate makes use of an ADO RecordSet, ADO Connection, ADO Command to establish the link between the application and the SQL Server.
I would check the ODBC client driver versions on both server and client and see if they are updated. The client and server machine need the registry keys. Beyond that I think this would be a good candidate for requiring a support case.
Hello Amit,
Thanks for your reply.
I did a quick test trying to narrow down the issue here since, indeed, the registery keys have the right values.
I try, from the ODBC data source administrator tool to configure datasources with the different drivers connecting to the database while the encryption is enforced on the server itself:
– ODBC Driver 11 for SQL Server (2014.120.4491.00) works fine without FIPS activated
– SQL Server Native Client 11.0 (2011.110.6518.00) works fine without FIPS
– SQL Server Native Client 10.0 (2007.100.2531.00) requires the activation of FIPS
– SQL Server (10.00.14393.00) requires FIPS
– SQL Native Client (2005.90.3042.00) requires FIPS.
After discussing with the developer in charge of this application, it seems that he does not know for sure which driver is called, though he knows he still makes use of the MSAD026.tlb based library to handle the communication with the database engine. I think the problem must lie somewhere here and that this needs to be changed to ADO.NET to fix the problem.
Thanks for your answers though!
Regards,
Francois
MSAD026 is related to MDAC components which are very old drivers. You are correct about changing the application to use ADO.NET. None of the client driver fixes that support TLS 1.2 upgrade the older MDAC components to support TLS 1.2.
I’m trying to download the patch from the download link (below). It appears the hotfixes all versions are returning “This page doesn’t exist. Try searching for what you need”. The CU links work fine. I am particularly after the patch for 2008 R2 (10.50.6542), I checked the Microsoft Update Catalog but could not find it. Any ideas? Thank you
https://support.microsoft.com/en-au/help/3135244/tls-1.2-support-for-microsoft-sql-server
Thanks for reporting this issue. We are looking into this issue.
Any word on when these links will be restored? I’m trying to download for SQL Server 2008 sp4. Thanks.
The links have been restored. Please let us know if you have issues with accessing them.
I need “SQL Server 2008 SP4 TLS 1.2 Update” but the link is not working, could you please reactivate download of the fix?
The links have been activated. Sorry about the inconvenience.
I cannot download the TLS update of SQL Server 2008 R2 too. Can you help check the link? Thanks a lot.
I am able to get to the download link. What is the error that you are getting?
Thanks Amit. It was my browser or proxy server error. I can download it with another browser.
I need to download also “SQL Server 2008 Native Client (x86 and x64)” but the link is not working, could you reactivate this link too?
Best regards
The link is active and working. Here it is: https://support.microsoft.com/en-us/hotfix/kbhotfix?kbnum=3098869&kbln=en-us
Could you please tell me if this link is still active? https://github.com/Microsoft/tigertoolbox/tree/master/tls1.2
This tool helps us to identify what all updates that i require instead me searching manually?
Yes, the link is active. This PowerShell script tells you whether the SQL Native Client and ODBC Driver need to be updated.
The script tells me I do not need to update SQL, but we are on SQL 2008 R2 SP2 (10.50.4000 when confirming via @@version), but it’s reading 10.52.4000 in the script. Can you please advise?
We restricted to TLS1.2 successfully for IIS server communicating to the SQL server, but are encountering issues with another IIS server that also has SSRS (all on Windows 2008 R2). IIS works, SSRS does not. Applied ADO.NET hotfix as instructed, and SSRS still fails.
Trying to determine best path forward. Can you advise?
Looks like the script is not checking the version correctly. We will look into it. If your build number is 4000, then you need the database engine update as well if you want to use TLS 1.2.
Link is dead.
I just tested, it’s online.
Hi Amit,
I read whole blog, your presentation and all Microsoft documentations. After all I have question regarding relationship between SCHANNEL registry configuration and Windows Server 2008 R2. You wrote on slide 9 that Windows Server 2008 R2 provide TLSv1.2, but it is disabled by default, and we MUST configure SCHANNEL to enable it. Microsoft wrote the same for Windows Server 2008 R2 SP1.
But, I succeed to establish connection over TLS1.2 , without “TLS 1.2” folder in SCHANNEL configuration, between:
Client (machine 1): SQL Server Management Studio 2014
Server (machine 2): SQL Server 2014 SP2 on Windows Server 2008 R2 SP1
I monitored this communication with Microsoft Network Monitor, and I found regular Client Hello, Server Hello, and rest of comunication via TLS 1.2 (Without explicit SCHANNEL configuration).
HOW IS IT POSSIBLE? Where I am wrong? Microsoft also wrote that this TLS 1.2 is provided, but disabled by default on Windows Server 2008 R2 and Windows Server 2008 R2 SP1.
I produced the same on two different environments.
Ps. Great presentation in any case. Thanks.
Did you apply any registry policies which would enable this or any recent updates? TLS 1.2 might be enabled for internet traffic already on the server. Could you check the WinHttp settings?
3 weeks ago, I checked all polices (Local and Domain) and WinHttp (it is not configured) and I didn’t find anything regarding TLS. On the other side, any policies or updates has to update registry settings. My SCHANNEL and WinHttp are not configured for explicit allowing TLS 1.2.
Maybe some Windows patch changed default Windows behavior? Are you sure that Windows Server 2008 R2 SP2 (with all fixes) don’t allow TLS 1.2 by default (without explicit SCHANNEL or WinHttp settings)? Thanks.
We would need to look into the server to determine while correlating that with the change log on the server.
Hello Amit,
We need to disable TLS 1.0 in our production environment, which consists of several web and database servers. I have read the above information and below I have tried to list the procedure in points, please confirm if this is correct.
Web Server:
Managed Hosting server, host mix of many .net 3.5, .net 4 websites (Windows Server 2012 R2).
1) Backup the registry before enforcing use of TLS 1.2.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework
2) Force all .net applications to use TLS 1.2 by making below registry changes.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
“SchUseStrongCrypto”=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
“SchUseStrongCrypto”=dword:00000001
3)Enable the support for .net framework 3.5 and earlier versions to use operationg system default Protocols.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727]
“SystemDefaultTlsVersions”=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v2.0.50727]
“SystemDefaultTlsVersions”=dword:00000001
4) Install SQL Server Natice Client SQL 2012
5) Install .net patches:
ADO.NET – SqlClient (.NET Framework 4.5.2, 4.5.1, 4.5)
ADO.NET – SqlClient (.NET Framework 4.0)
ADO.NET – SqlClient (.NET Framework 3.5/.NET Framework 2.0 SP2)
6) Disable TLS 1.0, using
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\1.0
client and server Enabled 0.
Database Server:
Hosting multiple databases for multiple websites, receiving SSMS connections and connections from websites hosted on above mentioned web server.
1) First check if your MSSQL Server version support TLS 1.2 OR not by visting below link.
https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server
Check if your MSSQL version is in the list ? How to know whether you need this update
If not as in my case MSSQL 2012, SP3 CU 6-GDR (KB3194724)11.0.6567 you are good to DISABLE TLS 1.0 from the registry.
If your MSSQL VERSION is in the list for example: 10.50.4047.0 then you need to install the patch mentioned in Download links for earlier installs.
It would be a great help for me if you confirm that the above mentioned steps are suffiecent to achieve disabling TLS 1.0 SUPPORT and forcing tls 1.2 on web and database environment.
Regards,
Fahad
I would recommend to work with Microsoft Support or Microsoft Services to complete the due diligence. While the list looks to cover a number of items, it is difficult for me to tell you if this covers your complete environment or not without having the necessary context about your environment.
Using SQL Server 2012 SP3 SSRS on Windows 2008 R2 SP1 with .NET Framework 4.6. Report Manager and Report Service are installed on web server and Reports and ReportsTemp DB’s on SQL server. Using custom app to display reports. Trying to disable TLS 1.0 on web server. App consumes Report Service and displays reports fine. Can browse to Report Service. SSMS is able to connect to Reporting Services on web server and databases on SQL server. Problem is with browsing to Report Manager on web server. Receive “Error The underlying connection was closed: An unexpected error occurred on a receive.” error on Report Manager Home page, “System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a receive. —> System.ComponentModel.Win32Exception: The client and server cannot communicate, because they do not possess a common algorithm” error in the ReportServerService log and the following 15 entries in the System event log for the next 25 seconds after the request: “Source: SChannel EventID: 36871 Level: Error – A fatal error occurred while creating an SSL client credential. The internal error state is 10013.”. I am duplicating this condition on at least two identical web servers configured the same. I have PCT 1.0, SSL 2.0, SSL 3.0, and TLS 1.0 DisabledByDefault=1 and Enabled=0 and TLS 1.1, TLS 1.2 DisabledByDefault=0 and Enabled=1 for both Client and Server in registry; downloaded and installed SP3 CU1 and all updates and client drivers including ,NET and ODBC that would run and still receiving error. Have read all articles and blogs I can find and have not found anything that sounds similar since SSMS and Report Server Service are working. Not sure what to try next. Any suggestions would be greatly appreciated.
Have you installed the Windows Server fixes for .NET as well as mentioned in the following KB: https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server? If yes, then I would recommend opening a support incident to have our support engineers look into why TLS 1.2 connections are failing.
Amit,
How can I download the updates/ hotfixes for the client components?
In another post, you suggested to apply the hotfixes for sql 2014 RTM, but I couldn’t these fixes in sql 2014 rtm feature pack?
SQL14_RTM_QFE_CU8_sqlncli
SQL14_RTM_QFE_CU8_msodbcsql
And clicking on the links under client components in https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server is taking to the download page of the respective components.
Can you guide me as to how I can download the hotfixes for client components (native client, odbc/ jdbc drivers) for sql 2012 (sp3+) and sql 2014 (sp1+)? Thanks
If you just need the Native Client update for TLS 1.2 support, then you can get it from https://www.microsoft.com/en-us/download/details.aspx?id=50402. The other client driver download links are available in “Client component downloads” section.
I have a classic ASP app that is talking (on the same box) to SQL Server 2008 10.0.6547. After disabling TLS 1.0 and applying all server/client/driver patches I was only able to connect by creating a System DSN with ODBC Driver…. could not connect with SQLNCLI10 The problem there is that the application fails because of issues with datetime fields ( see http://rightondevelopment.blogspot.in/2009/10/sql-server-native-client-100-datetime.html ). Is there a way to get the SQLNCLI10 provider working without TLS 1.0 ? I’ve modified client registry keys as suggested here, and updated to latest version of sql native driver per hotfix SQL2008_SP4_COD_SNAC_x64_1033
I believe you were trying to connect with the SQL OLEDB driver which does not support TLS 1.2. If you want to use that, then you need to enable TLS 1.0 so that the driver will work with the application.
Hi Amit,
If you are still following this blog.
I am trying to connect to SQL 2008 R2 Machine from SQL 2016 using Import-Export Wizard. But it is failing with below error
[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error. (Microsoft OLE DB Provider for SQL Server)
Initially I was not able to connect to the instance using SSMS from SQL2016 box, but was able to connect after enabling TLS 1.0 in SQL 2008R2 box. But export import is not working.
What is the provider that you are using to connect? If this is an older provider which is not available in the driver list mentioned in our KB article, then it will not work. If you are using the default drivers, then I would recommend applying the driver updates on both the source and target machines. If the issue still persists, please work with Microsoft Support to resolve the issue.