Error Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 - Encryption(ssl/tls) handshake failed) #633
Comments
|
Hi @machoubie , From the docker info, it's hard to tell what SQL server version you are using. Could you provide more information about your server? And what docker images you are using? Is the Ubuntu 18.04 OS the actual system or the docker container you are using to connect to the server? I assume that you are using the Microsoft.Data.SqlClient v2.0.0 because you have seen the "End of stream reached" error message which is recently introduced by PR 577. This error may be related to Server certificate force encryption #609 or lower TLS version used by the server (quote from 201). You can check the following settings first on your side:
If the above two steps don't solve your issue, you may face a similar case when your server is using lower version of TLS. If possible, you can try to write a simple .NET core application to connect to your target server and see if you get warning message like |
|
I have exactly the same issue, all i did was update the package from 1.1.3 to 2.0.0 ... it then broke, downgrading back to 1.1.3 made it work again. This leads me to believe its not SQL Server at fault, its the sqlclient package |
|
ee the end of this message for details on invoking ************** Exception Text ************** ************** Loaded Assemblies **************
|
|
@madhon It is a documented breaking change in the 2.0 provider, yes. https://erikej.github.io/sqlclient/efcore/2020/06/22/sqlclient-2-breaking-changes.html |
Why would that connection string value be force required now ? Its effectively saying trust the certificate regardless of if it verifies up to a trusted root certificate or not ? I was under the impression that parameter was only to override untrusted certificates ? |
|
The equivalent of the following code to override trust of certs when accessing web apis/sites ServicePointManager |
|
@karinazhou i am using dotnet/core/aspnet:3.0-buster-slim docker image to run my apps , the tests you suggest don't work for me |
|
another thing, when i add Encrypt=False to my connectionString , the sqlclient still use ssl for th connection |
|
Please ensure your server is not enforcing SSL if it's an on-premise SQL Server, see comment here: #609 (comment) |
|
I confirm that the server is not enforcing ssl |
|
Ours isn’t enforcing either |
|
Ok, I got a bit confused in discussions above. To clarify, SSL/TLS handshake is always performed never-the-less client requests encryption, it's part of login flow. But, whether the stream is going to be encrypted and certificates are validated are not, depends on client/server settings where "Force Encryption" plays a role, which doesn't seem to be the case here. Also, I see two different stack traces in above discussions: If both issues are different, let's stick to issue in description first. @machoubie provider: SSL Provider, error: 31 .. System.IO.EndOfStreamException: End of stream reached issue is possible, when client does not support TLS 1.0/1.1 versions whereas Server does not support TLS 1.2 > leading to server not responding to client's request as no "trust" is established. To fix this error, you need to ensure TLS 1.2 is enabled on Server and is available to be used to connect. If you are not sure it is enabled, you can connect from a Windows client where lower TLS versions are enabled and verify if a lower TLS protocol version is negotiated by looking at "warning" thrown by driver. @madhon On the other hand, for stack trace regarding "provider: Shared Memory Provider, error: 36" , we'll need more details regarding your environment setup and how can we reproduce this issue? |
|
@machoubie According to what @cheenamalhotra has suggested, your issue looks similar to this one #201 . I can see some other users facing the same issue due to TLS version mismatching. Probably, your Debian image is using TLS 1.2 by default while your server is using a lower TLS version. If you can confirm that your SQL Server is using old TLS version, for example, by connecting from a Windows application and check the TLS version warning message. You can either enable TLS 1.2 on your SQL Server or try some suggestions like switching to a 3.0-bionic image (Ubuntu 18.04) if possible or bring back the minimal TLS support version on Debian if upgrading server's TLS version is not an option for you. Other ways you can also try : And here is the link about how to enable TLS 1.2 on SQL Server. |
+1 It worked for me. I've changed the minimal TLS version to 1.0 |
|
+1 It worked for me. I've changed the minimal TLS version to 1.0 |
|
+1 It worked for me. |
|
I'd like to add again that lowering TLS version would work, but it's not recommended solution as TLS 1.0 and 1.1 are insecure protocols. Ideal solution is to upgrade/enable target SQL Servers to support TLS 1.2 protocol. |
|
Just add to your Dockerfile right above the ENTRYPOINT |
|
This last entry fixed it for us. TrustServerCertificate=false was not enough. Kudos to @kfman for the save! |
|
That did it for me as well, thanks! |
Unfortunately that does not solve for .Net 5 |
|
You're possible hitting this issue: Could you try their recommendations? |
|
@cheenamalhotra I just did try their recommendation. My openssl.cnf file now points a I also replaced the |
|
Please verify your setup from this too: |
|
@charlienilsson tks for the article, but the error persists... I have tried several combinations... even using other images such as
using both images |
|
I had this when using wrong version of SqlClient -error message kept coming up but only on Linux Container not on Windows - I had latest .net 5 but somehow my SqlClient was file stamped 2017. Issue resolved by removing System.Data.SqlCient from the project and installing Microsoft.Data.SqlClient using Nuget. |
|
Yes Microsoft.Data.SqlClient is platform independent and System.Data isnt. Make sure that the dll for Microsoft.Data.SqlClient is in the bin folder of the container. We have everything on net5 and it is working like a charm. |
|
tks @Gavin1Sinai and @johanskoldekrans but im already using |
I was with the same problem I manage to work with |
|
Hello Everyone I had try run everythin that could possibly combine. My conenction String Error |
|
can you get your hands on a newer version of SQL Server? test connection to one in azure (open a free account) and let me know if the problem persists |
|
Our system is still using SQL Server 2008 R2 so with this unfortunate circumstance I need to using it in on-premise solutions. |
|
Maybe a little bit related to the topic and since this is a fresh post, I decided to give it a try... I'm having this exact same issue: We also have SQL Server 2008 R2 and the connection is made directly through our external IP address (we specified our Web App public IPs to have access from outside). The thing is...after some heavy load on our API, it simply starts to log the following error: 2021-04-29 08:10:42.2752|ERROR|WRE.Routing.API.Startup|[POST] https://XXXXXXXX.azurewebsites.net/routes/start Microsoft.Data.SqlClient.SqlException (0x80131904): 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) ---> System.ComponentModel.Win32Exception (5): Access is denied. at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource The error goes away if we don't do anything and I noticed that if I restart the Web App, it also fixes the issue. Any thoughts? |
worked for me, net 5 console application, running on aws fargate thanks |
Hi dear , i am facing same issue in .Net Maui project can you please help me , I am Stuck from the last Two Days . |
|
On mcr.microsoft.com/dotnet/aspnet:6.0 docker image, this worked for me: |
|
Hello, We are suffering from the same error message when our API is trying to access the database:
In our case, the API is made with .NET 6 and hosted in a Linux container while the SQL Server is a SQL Server 2016 (SP3) running in a Windows Server 2012 R2 with .NET Framework 4.8 installed. We assume this all means that no additional software/updates are needed for enabling TLS1.2 in that server. Regarding the configuration, SQL Server is NOT configured to Force Encryption while the server's registry is set as follows:
Last but not least, this is how our current connection string looks like:
We have also tried the same connection string with We have been researching not only in this thread but in some other articles across the Internet and nothing is solving this issue. The "funny" thing is that we have been using WireShark to check the connections to the database and, every time we try to connect from a Linux container (either AKS or Docker), the hand-shaking seems to be using TLS1.0. On the other hand, if we run the API from Visual Studio on Windows against the same server, it works perfectly using TLS1.2. In addition, we have another server with a pretty similar configuration where the container app is working fine but it's using Windows Server 2019 and SQL Server 2019, so we wonder what might be different between those server and database versions. Since this newer server is working fine without forcing the encryption for SQL Server, we have assumed that's not the problem. We have tested turning it on though with unsuccessful results. We have also tested the container configuration, just in case. It's using a mcr.microsoft.com/dotnet/aspnet:6.0 docker image and the openssl.cnf file is configured as follows:
Of course, we've tried some other configurations such as the ones mentioned above in this thread, but nothing worked. Does anyone have any idea, suggestion or comment that might put us in the right direction to solve this issue? Thank you in advance! P.S: upgrading the server to either Windows Server 2019 and/or SQL Server 2019 is, unfortunately, not an option. |
|
@sayago69 have you tried adding |
|
Yes, we have tried with both However, I think we have managed to find a solution: we have added this bit After adding that line, we started getting a slightly different error: Then, we made it work just by adding
Hope it might help someone else. |
|
@sayago69 Your answer worked for me, additionally add the 'Encrypt=False' in the connection string. Thank you |
You're welcome. Unfortunately, that solution fixed our API's connection to SQL Server but broke our API's connection to RabbitMQ which only accepts TLS1.2 as far as I'm aware. Still trying to understand why changing the minimum protocol in openssl.cnf to 1.0 is making it work if both the API and SQL Server are eventually handshaking using TLS 1.2. Why didn't they do it before when the minimum protocol was TLS1.2? We must have changed something else in that file. Anyway, we'll keep investigating... |
|
Well, it seems we have finally managed to figure out a lasting solution to this problem. We just added the following line to our docker file just after the EXPOSE lines: Note that this is just one of the lines suggested by @inlineHamed earlier but in a different place in the docker file. We started adding those two lines (and worked) but we figured out that the second line was enough. It didn't work fine with only the first line. Also, our connection to the database doesn't need Once again, hope this may help someone else. |
|
As of today, the |
|
I was having the same problem and this issue thread helped me to solve it. Simply adding the following to the connection string solved it:
|
Thanks, this saved me a bunch of time today but the configuration blocks for this aren't present in latest |
|
Unfortunately that does not solve for .Net 8
|
|
@Mehranh please visit the link below: |
and others
Hi,
I am faced the issue bellow when trying connection From .net core container to windows mssql .
Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 - Encryption(ssl/tls) handshake failed)
---> System.IO.EndOfStreamException: End of stream reached
at Microsoft.Data.SqlClient.SNI.SslOverTdsStream.ReadInternal(Byte[] buffer, Int32 offset, Int32 count, CancellationToken token, Boolean async)
at Microsoft.Data.SqlClient.SNI.SslOverTdsStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.FixedSizeReader.ReadPacket(Stream transport, Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Security.SslStream.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslStream.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslStream.StartSendBlob(Byte[] incoming, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslStream.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslStream.ProcessAuthentication(LazyAsyncResult lazyResult, CancellationToken cancellationToken)
at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions)
at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation)
at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost)
at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)
at Microsoft.Data.SqlClient.SNI.SNIProxy.EnableSsl(SNIHandle handle, UInt32 options)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired) at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool) at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource
1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource
1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides)at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open()
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable
1.Enumerator.InitializeReader(DbContext _, Boolean result) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext()at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
at TEST.Api.DAL.EnqueuingToDb.DbMilor2Context.EnqueueData(ApiCallIdentification metadatas, String json) in /src/TEST/DAL/EnqueuingToDb/DbMilor2Context.cs:line 57
at TEST.Api.Business.EnqueuingToDb.Enqueuing.Create() in /src/TEST/Business/EnqueuingToDb/Enqueuing.cs:line 51
at TEST.Api.Models.ActionFilters.DataEnqueuingFilterAttribute.OnActionExecuting(ActionExecutingContext context) in /src/TEST/Models/ActionFilters/DataEnqueuingFilter.cs:line 109
at Microsoft.AspNetCore.Mvc.Filters.ActionFilterAttribute.OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
ClientConnectionId:490aab56-2f44-4824-b5a1-907042f6e801 Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 31 - Encryption(ssl/tls) handshake failed)
Docker version:
Client: Docker Engine - Community
Version: 19.03.12
API version: 1.40
Go version: go1.13.10
Git commit: 48a66213fe
Built: Mon Jun 22 15:45:36 2020
OS/Arch: linux/amd64
Experimental: false
Server: Docker Engine - Community
Engine:
Version: 19.03.12
API version: 1.40 (minimum version 1.12)
Go version: go1.13.10
Git commit: 48a66213fe
Built: Mon Jun 22 15:44:07 2020
OS/Arch: linux/amd64
Experimental: false
containerd:
Version: 1.2.13
GitCommit: 7ad184331fa3e55e52b890ea95e65ba581ae3429
runc:
Version: 1.0.0-rc10
GitCommit: dc9208a3303feef5b3839f4323d9beb36df0a9dd
docker-init:
Version: 0.18.0
GitCommit: fec3683
Host info :
Kernel Version: 5.3.0-1031-azure
Operating System: Ubuntu 18.04.4 LTS
OSType: linux
Architecture: x86_64
Connection String :
Data Source=TESTSERVER2INT\TESTSQL02INT,52810;Initial Catalog=Test;User ID=test_user;Password=****;Integrated Security=False
Thank you in advance for your Help
The text was updated successfully, but these errors were encountered: