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.
| 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 | |
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.
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 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
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
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 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 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.
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
3099845 should be for Windows 7 SP1/Vista and Windows Server 2008 R2/Windows Server 2008.
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?