SQL Server pre-login handshake failed Android 5,6,8.1

jonathanpeppers commented 2 years ago

Issue moved from dotnet/maui#6859 Issue moved from https://github.com/xamarin/xamarin-android/issues/6990

Please respond to @janseris.

From @janseris on Thursday, May 5, 2022 12:39:34 PM

Description

SQL Server - the connection was established but pre-login handshake failed when calling database via Entity Framework

**Microsoft.Data.SqlClient.SqlException:** 'A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)'

What does not help:

Encrypt=false in connection string
Encrypt=true in connection string
Encrypt=false;TrustServerCertificate=true; in connection string

The same works without any issue on Android 9 and up (both emulator and real device). Tested: works on Android 9 for all TLS settings: Native TLS 1.2+ and Managed TLS 1.0 and also for "no option".

The issue is in Debug (and thus probably also in Release) configuration.

Output for Android 5 (API 21): Microsoft.Data.SqlClient.SqlException: 'A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)'

Output for Android 8, for all TLS configuration options: Native TLS 1.2+ and Managed TLS 1.0 and also for "no option".

[System.err] java.lang.IllegalStateException: Handshake has already been started
[System.err]    at com.android.org.conscrypt.OpenSSLEngineImpl.beginHandshakeInternal(OpenSSLEngineImpl.java:335)
[System.err]    at com.android.org.conscrypt.OpenSSLEngineImpl.beginHandshake(OpenSSLEngineImpl.java:325)
[System.err]    at crc640ec207abc449b2ca.ShellSectionRenderer.n_onCreateView(Native Method)
[System.err]    at crc640ec207abc449b2ca.ShellSectionRenderer.onCreateView(ShellSectionRenderer.java:42)
[System.err]    at androidx.fragment.app.Fragment.performCreateView(Fragment.java:2995)
[System.err]    at androidx.fragment.app.FragmentStateManager.createView(FragmentStateManager.java:523)
[System.err]    at androidx.fragment.app.FragmentStateManager.moveToExpectedState(FragmentStateManager.java:261)
[System.err]    at androidx.fragment.app.FragmentManager.executeOpsTogether(FragmentManager.java:1840)
[System.err]    at androidx.fragment.app.FragmentManager.removeRedundantOperationsAndExecute(FragmentManager.java:1764)
[System.err]    at androidx.fragment.app.FragmentManager.execPendingActions(FragmentManager.java:1701)
[System.err]    at androidx.fragment.app.FragmentManager.dispatchStateChange(FragmentManager.java:2849)
[System.err]    at androidx.fragment.app.FragmentManager.dispatchViewCreated(FragmentManager.java:2777)
[System.err]    at androidx.fragment.app.Fragment.performViewCreated(Fragment.java:3020)
[System.err]    at androidx.fragment.app.FragmentStateManager.createView(FragmentStateManager.java:551)
[System.err]    at androidx.fragment.app.FragmentStateManager.moveToExpectedState(FragmentStateManager.java:261)
[System.err]    at androidx.fragment.app.FragmentManager.executeOpsTogether(FragmentManager.java:1840)
[System.err]    at androidx.fragment.app.FragmentManager.removeRedundantOperationsAndExecute(FragmentManager.java:1764)
[System.err]    at androidx.fragment.app.FragmentManager.execPendingActions(FragmentManager.java:1701)
[System.err]    at androidx.fragment.app.FragmentManager.dispatchStateChange(FragmentManager.java:2849)
[System.err]    at androidx.fragment.app.FragmentManager.dispatchActivityCreated(FragmentManager.java:2784)
[System.err]    at androidx.fragment.app.FragmentController.dispatchActivityCreated(FragmentController.java:262)
[System.err]    at androidx.fragment.app.FragmentActivity.onStart(FragmentActivity.java:478)
[System.err]    at androidx.appcompat.app.AppCompatActivity.onStart(AppCompatActivity.java:246)
[System.err]    at android.app.Instrumentation.callActivityOnStart(Instrumentation.java:1333)
[System.err]    at android.app.Activity.performStart(Activity.java:6992)
[System.err]    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2780)
[System.err]    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2892)
[System.err]    at android.app.ActivityThread.-wrap11(Unknown Source:0)
[System.err]    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1593)
[System.err]    at android.os.Handler.dispatchMessage(Handler.java:105)
[System.err]    at android.os.Looper.loop(Looper.java:164)
[System.err]    at android.app.ActivityThread.main(ActivityThread.java:6541)
[System.err]    at java.lang.reflect.Method.invoke(Native Method)
[System.err]    at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
[System.err]    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:767)
**Microsoft.Data.SqlClient.SqlException:** 'A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)'

Steps to Reproduce

call database in a MAUI app with Android 8.1 or lower

Version with bug

Release Candidate 2 (current)

Last version that worked well

Unknown/Other

Affected platforms

Android

Affected platform versions

Android 8.1 and below

Did you find any workaround?

no

Relevant log output

No response

VictorCanovasA commented 2 years ago

Same problem here on Android 11 (Api 30). On my MAUI project on VS 2022 Preview (17.3.0 Preview 2.0) I don't have the option to change TLS (Android > options). It simply don't exists. DbConnectionString = "Data Source=192.168.1.xxx,1433; Database=PoC_MAUI;Id=xxx;Password=xxx;Encrypt=False;TrustServerCertificate=True";

Microsoft.EntityFrameworkCore.SqlServer 6.0.6

I trying everything with the connection string...

janseris commented 2 years ago

@VictorCanovasA The TLS options were only in preview version of MAUI probably for experimental purposes. Are you getting pre-login handshake failed on Android 11? That hasn't ever happened to me

VictorCanovasA commented 2 years ago

@VictorCanovasA the TLS options were only in preview version of maui probably for experimental purposes. Are you getting handshake failed on Android 11? That hasn't ever happened to me

Yes: "...an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)"

I think it's because you are not using Microsoft.EntityFrameworkCore.SqlServer isn't it?

janseris commented 2 years ago

@VictorCanovasA the TLS options were only in preview version of maui probably for experimental purposes. Are you getting handshake failed on Android 11? That hasn't ever happened to me

Yes: "...an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)"

I think it's because you are not using Microsoft.EntityFrameworkCore.SqlServer isn't it?

I am using EF Core 6 with SQL Server. That's odd. My SQL Server is some version of 2012. What is yours? Could you share a sample application? I will try it out.

janseris commented 2 years ago

@VictorCanovasA the TLS options were only in preview version of maui probably for experimental purposes. Are you getting handshake failed on Android 11? That hasn't ever happened to me

Yes: "...an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)"

I think it's because you are not using Microsoft.EntityFrameworkCore.SqlServer isn't it?

Sample application posted by me is here: https://github.com/xamarin/xamarin-android/issues/6990#issuecomment-1136289977 In it, you will see that I am using EF Core with SQL Server

VictorCanovasA commented 2 years ago

@janseris I am using SQL Server 2019. Sadly i can't share any code by now. I can't figure out what's wrong so I'll continue investigating. ¯_(ツ)_/¯

janseris commented 2 years ago

@janseris I am using SQL Server 2019. Sadly i can't share any code by now. I can't figure out what's wrong so I'll continue investigating. ¯(ツ)/¯

Could you post stats for all android versions (handshake error or not) ? That would help a lot!

VictorCanovasA commented 2 years ago

@janseris Thank you for all your help but I created an SQL Azure Database and it works perfect. In production there isn't a local SQL Server so I continue in this way to avoid losing more time.

JRahnama commented 2 years ago

Hi, Microsoft.Data.SqlClient throws error 35 under couple of circumstances:

If MARS (Multiple Active Result Sets) is set to true and current session cannot handle the Acknowledge packet.
MARS is requested and packets are messed up.
Authentication fails on enabling SSL (TCP error will occur)
Async calls are used and writing to Stream in async cannot be completed.

Now the question is were the users able to make the connection with previous versions of M.D.S any version before 4.0 ? If yes they probably have certificate issues. Are you able to test with the latest preview release of M.D.SqlClient. Simply add a package reference to your csproj with this version as below:

<ItemGroup>
      <PackageReference Include="Microsoft.Data.SqlClient" Version="5.0.0-preview3.22168.1" />
</ItemGroup>

if the issue persists try adding Encrypt = false These steps are just to make sure user is not having certificate/authentication issues.

janseris commented 2 years ago

Hi. I appreciate your deep dive into the issue.

In my opinion, the issue is definitely in pre-login handshake. The connection string I am using has the following structure.

Server=x;Database=y;User ID=login;Password=password

The server is our company's production SQL Server 2012 which we have been using for years with both old .NET 4.5.2 WinForms apps and also EF Core 5 and EF Core 6. Everything has always worked smoothly. We are accessing the server with ancient packages such as this one: https://www.codeproject.com/Articles/15666/Data-Access-Application-Block-NET-2-0-Get-Return-V

The only thing which does not work is connection with Android device for Android 8.1 and below with EF Core 6.

Encrypt=false does not help and cannot help because this only affects the communication after SQL authentication (sending the data) (please correct me if I am wrong). The authentication is always encrypted because otherwise plaintext SQL login and password would be sent over the network. Do you think it is even possible to disable SSL for the SQL authentication (that would be a very bad hack but a possible solution and also proof of the issue)?

Also, the SqlException is not Error 35 but that is TCP providererror (on Windows, there is Named Pipes providerinstead and for example when internet connection is not available, it returns error 40 while Android TCP provider returns error 35). The SqlException details in this case are:

Class 20
Number 258 - what I have detected previously (but for some reason, now I am getting always Number 0 for any "network" SqlException of class 20 on Android 6 and 8 emulator (and also on Android 11 and 12 emulator and device for no internet Class 20 SqlException which normally has Number 53) when I tried it, Number is 0)

I will try latest preview of standalone SqlClient on the older android device. However is it possible to use the preview SqlClient inside EF Core 6? I am using SqlClient indirectly via EF Core 6 only (I am not calling raw SQL nor doing anything low-level). Also, on Android MAUI C# app, it is not possible to use .NET 7 Preview if that would help bringing the latest preview of SqlClient in via EF Core 7 latest preview.

User is not having certificate/authentication issues, I am sure about it because on Windows I connect normally and also in SQL Server management studio. Also wrong credentials would yield a different SqlException, right (I haven't tested wrong credentials).

With this info, is there anything else I can do (I will try the preview version of SqlClient and will let you know in a few hours).

I think that the issue is the socket layer provider in Android (which I have no control of). Because of that, I am also wondering if anyone from the SqlClient team can do anything about this issue at all.

JRahnama commented 2 years ago

@janseris you can directly upgrade to the latest version of M.D.SqlClient by adding that to the csproj of the project and that will overwrite the previous version.

janseris commented 2 years ago

@janseris you can directly upgrade to the latest version of M.D.SqlClient by adding that to the csproj of the project and that will overwrite the previous version.

Hi, I tried 4.1.0 SqlClient alone which is latest stable according to NuGet and also your 5.0.0 preview which doesnt show in preleases in NuGet but works:

I am using .NET MAUI. Latest VS 2022 Preview (17.3.0 Preview 2) which includes latest MAUI (it is not possible to use MAUI in a different version of VS).

The result in short is: SqlClient 5.0.0 Preview does not fix the problem - the problem for Android 8.1 and lower perists.

What I think is a bug/issue as well are wrong SqlException Number properties on Android.

The EF package which I have been using when reporting the problem was EF Core 6.0.3 (I am not sure which SqlClient version is included in that version)

Certificate details:

verified by DigiCert, showing as OK in browser
Not Before: Thu, 24 Feb 2022 00:00:00 GMT
Not After: Fri, 24 Feb 2023 23:59:59 GMT

SqlClient Preview 5.0.0 - online behavior

Android 5 emulator: Class 20, Number -2146893019
Android 8 emulator: Class 20, Number -2146893019
Android 9 emulator: no issue
Android 11 emulator: no issue
Android 12 device: no issue
Windows 10: no issue

version latest stable 4.1.0 - online behavior:

Android 5 emulator: Class 20, Number -2146893019 handshake failed (TCP Provider, error: 35)
Android 6 emulator: Class 20, Number -2146893019 handshake failed (TCP Provider, error: 35)
Android 8 emulator: Class 20, Number -2146893019 handshake failed (TCP Provider, error: 35)
Android 9 emulator: Class 20, Number -2146893019 handshake failed (TCP Provider, error: 35)
Android 11 emulator: Class 20, Number -2146893019 handshake failed (TCP Provider, error: 35) Train Wi-Fi and also mobile data
Android 12 emulator: Class 20, Number -2146893019 handshake failed (TCP Provider, error: 35) Train Wi-Fi and also mobile data
- [System.err] javax.net.ssl.SSLHandshakeException: Chain validation failed
- [System.err] at com.android.org.conscrypt.SSLUtils.toSSLHandshakeException(SSLUtils.java:363)
- Caused by: sun.security.provider.certpath.PKIX$CertStoreTypeException: java.io.IOException: Cleartext HTTP traffic to crl3.digicert.com not permitted
Android 12 device: Class 20, Number -2146893019 handshake failed, no internal Java Exception, Mobile Data connection
Windows 10 - no issue

No internet behavior SqlClient 4.1.0:

Android 5 emulator: Class 20, Number 0 The server was not found or was not accessible.
Android 8 emulator: Class 20, Number 0 The server was not found or was not accessible.
Android 11 emulator: Class 20, Number 0 The server was not found or was not accessible.
Android 12 device: Class 20, Number 0 The server was not found or was not accessible (TCP Provider err 35)
Windows 10 - Class 20, Number 53 The server was not found or was not accessible

janseris commented 2 years ago

@JRahnama

Now the question is were the users able to make the connection with previous versions of M.D.S any version before 4.0 ?

for my surprise, EF Core 6.0.3 uses Microsoft.Data.SqlClient 2.1.4. So yes, connecting to our server definitely worked before version 4.0.0

My server is SQL Server 2012 with latest patch (11.0.7507.2) - release date 2021-01-12.

Here is a similar issue (but not stated exact versions of Android) https://docs.microsoft.com/en-us/answers/questions/826630/i-am-not-able-to-connect-net-maui-project-with-ms.html

janseris commented 2 years ago

Hi, Microsoft.Data.SqlClient throws error 35 under couple of circumstances:
1. If MARS (Multiple Active Result Sets) is set to true and current session cannot handle the Acknowledge packet.

2. MARS is requested and packets are messed up.

3. Authentication fails on enabling SSL (TCP error will occur)

4. Async calls are used and writing to Stream in async cannot be completed.
Now the question is were the users able to make the connection with previous versions of M.D.S any version before 4.0 ? If yes they probably have certificate issues. Are you able to test with the latest preview release of M.D.SqlClient. Simply add a package reference to your csproj with this version as below:
<ItemGroup>
      <PackageReference Include="Microsoft.Data.SqlClient" Version="5.0.0-preview3.22168.1" />
</ItemGroup>
if the issue persists try adding Encrypt = false These steps are just to make sure user is not having certificate/authentication issues.

It would have been nice if you told me why you asked if it worked before 4.0.0 and the reason is the following: https://docs.microsoft.com/en-us/sql/connect/ado-net/introduction-microsoft-data-sqlclient-namespace?view=sql-server-ver15#breaking-changes-in-40 (The default value of the Encrypt connection setting has been changed from false to true) (discovered through https://stackoverflow.com/questions/17615260/the-certificate-chain-was-issued-by-an-authority-that-is-not-trusted-when-conn)

janseris commented 2 years ago

Here is a better test application for this issue. It calls the SQL Server using various versions of SqlClient: https://github.com/janseris/MAUI_SQLServer_Test/commit/72b40bfb03d35ef1d1acd79fb7ed8707160abee4

ExtSolTech commented 2 years ago

Is found any soultion someone?

janseris commented 2 years ago

@ExtSolTech No and there is even worse problem with SQL Server Express 2019 - follow here and you can try their suggestion what they need to test out so they can continue https://github.com/dotnet/SqlClient/issues/1662

chandra-arifin commented 2 years ago

so, it works with sql server 2012 ? if so, then just install sql server 2012 ;p

janseris commented 2 years ago

so, it works with sql server 2012 ? if so, then just install sql server 2012 ;p

With SQL Server 2012, it doesn't work on Android 8.1 and lower but works on Android 9 and higher. With SQL Server Express 2019, it doesn't work on any version.

chandra-arifin commented 2 years ago

what a pity, maybe in the future, someone will fixed this bugs. or create another package to connect to android. just like jtds on java, can connect to android from that library.

janseris commented 2 years ago

Also happens with SMTP and SignalR https://github.com/dotnet/maui/issues/9587#issuecomment-1252090053

lcheunglci commented 2 years ago

I understand that this issue was logged for older versions of Android namely 5, 6 and 8, but for those using the newer version i.e. the Android 12.1 emulator and Sql Server 2019 with MDS 5.0, I managed to get it working. You can find my solution here. Unfortunately, I tried the same solution for the Android 6 emulator with the self-signed certs installed, but I was still getting the error 35 with the same setup.

thevirtualdj commented 2 years ago

Same issue with VS 2022 17.4.0 Preview 2.1 and .net 7.0. Microsoft.Data.SqlClient 5.0.0. android:usesCleartextTraffic="true" -> Not working. VS has no TLS chose option. network-security-config -> cleartextTrafficPermitted="true" -> Not working

Tried to add certificates to SQL server and trust -> no success. Any ideas would be appreciated.

My test app: https://github.com/thevirtualdj/MAUISqlTestApp

thevirtualdj commented 2 years ago

Managed to solve it with cert and IP address. How?

Create a cert with powershell for your IP address: New-SelfSignedCertificate -certstorelocation cert:\localmachine\my -dnsname '192.168.0.15','localhost' -KeySpec KeyExchange -FriendlyName '192.168.0.15' -NotAfter (Get-Date).AddMonths(240) (Will work with any IP address of your sql server later).
Set your cert in SQL Server Configuration manager.
Export that certificate as Base-64 encoded X.509 (.CER) file.
Import that cer in Platforms\Android\Resources\raw folder (if it does not exist create it).
In Platforms\Android\xml folder create nsc.xml (for me was not working if I name it network_security_config.xml) with content: (change the data ofc IP and domain names and @raw/certname mine was called razvoj1.cer in \Platforms\Android\Resources\raw folder and my SQL server IP was 192.168.0.15 )

<?xml version="1.0" encoding="utf-8"?>
<network-security-config>
    <base-config cleartextTrafficPermitted="true">
        <trust-anchors>
            <certificates src="system" />
            <certificates src="user" />
            <certificates src="@raw/razvoj1"/>
        </trust-anchors>
    </base-config>
    <domain-config cleartextTrafficPermitted="true">
        <domain includeSubdomains="true">localhost</domain>
        <domain includeSubdomains="true">192.168.0.15,1433</domain>
        <domain includeSubdomains="true">192.168.0.15</domain>
        <domain includeSubdomains="true">RAZVOJ1</domain>
        <domain includeSubdomains="true">RAZVOJ1.localhost</domain>
        <trust-anchors>
            <certificates src="system" />
            <certificates src="user" />
            <certificates src="@raw/razvoj1"/>
        </trust-anchors>
    </domain-config>
</network-security-config>

In AndroidManifest add android:usesCleartextTraffic="true" and android:networkSecurityConfig = "@xml/nsc" to application tag :

<?xml version="1.0" encoding="utf-8"?>
<manifest android:targetSandboxVersion="1" xmlns:android="http://schemas.android.com/apk/res/android">
<application android:allowBackup="true" 
             android:icon="@mipmap/appicon" 
             android:roundIcon="@mipmap/appicon_round"
             android:usesCleartextTraffic="true"
                 android:networkSecurityConfig="@xml/nsc"
             android:supportsRtl="true"></application>
<uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
<uses-permission android:name="android.permission.INTERNET" />
</manifest>

Use this connection string: string connectionString = @"Server=192.168.0.15,1433;Database=YOURDB;User Id=sa;Password=YOURPASS;Persist Security Info=True;Encrypt=True;TrustServerCertificate=True";
You should be able to open SQLConnection with Microsoft.Data.SQLClient 5.0.0 and .net 7 MAUI

janseris commented 2 years ago

@thevirtualdj What sql server version did that work on? Does it work with sql client 2.1.4 which EF Core uses?

thevirtualdj commented 2 years ago

@thevirtualdj What sql server version did that work on? Does it work with sql client 2.1.4 which EF Core uses?

Microsoft.Data.SQLClient 5.0.0 and .net 7 MAUI

jovanmhn commented 2 years ago

I got it working with Microsoft.EntityFrameworkCore Version="6.0.9" SQL Server 2016 (v13.0.4259.0) .NET 6

The only thing I had to do differently was generate my self signed certificate using IIS/Server Certificates/Create Self-Signed Certificate My SQL service just wouldnt start using the one generated with the power shell command (its not impossible I did something wrong, however I did try so many workarounds). A lot of people online claim that, for sql server 2016 at least, the certificate hash in the registry has to be uppercase, and if you select it from the managment console it will not be so, so you may need to manually edit that. (Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.SQLSERVER\MSSQLServer\SuperSocketNetLib\Certificate) Wasn't the case for me though

janseris commented 2 years ago

@thevirtualdj @jovanmhn Android 8.1 or lower?

thevirtualdj commented 2 years ago

@thevirtualdj @jovanmhn Android 8.1 or lower?

You should not having troubles with lower android version as you go with certificates. Because lower android versions should support TLS1.2 without problems.

jovanmhn commented 2 years ago

I am getting the same pre-login handshake error on an emulated Android 8.1 client

janseris commented 2 years ago

@thevirtualdj @jovanmhn Android 8.1 or lower?

You should not having troubles with lower android version as you go with certificates. Because lower android versions should support TLS1.2 without problems.

Should but that is what this issue is all about. Android in C#has TLS/SSL issues on multiple protocols on Android 8.1 and lower

janseris commented 2 years ago

Hi @Kaur-Parminder do you believe that this issue can be solved in SqlClient? It seems like a .NET Android team responsibility but it was moved from them here

Issue moved from https://github.com/dotnet/maui/issues/6859 Issue moved from https://github.com/xamarin/xamarin-android/issues/6990

because the issue "happens in SqlClient" but the cause is not in SqlClient because this does not happen with SqlClient on other platforms than Android (and more specifically 8.1 and lower if someone will be saying it works because they are trying it with Android 11).

MhmdTabikh commented 1 year ago

@janseris any update on this issue? I'm facing the same issue on Android 12.0 emulator

keventheriault commented 1 year ago

I have the same error with Android on .NET 6 or .NET 7 (not xamarin...) Do you have solution...

lcheunglci commented 1 year ago

@keventheriault Have you tried the solution suggested above or from another post in #1662? In order for Android to connect, regardless of if it's a MAUI/Xamarin app or not, it requires a specific set of steps on both the server and the device/emulator i.e. creating and adding the ssl certificate(s) in order to be able to connect to Sql Server.

janseris commented 1 year ago

@keventheriault Have you tried the solution suggested above or from another post in #1662? In order for Android to connect, regardless of if it's a MAUI/Xamarin app or not, it requires a specific set of steps on both the server and the device/emulator i.e. creating and adding the ssl certificate(s) in order to be able to connect to Sql Server.

This solution is not suitable for production environment. We cannot do this on user's phone.

lcheunglci commented 1 year ago

This solution is not suitable for production environment. We cannot do this on user's phone.

That is correct. We don't use self-signed certificates in a production environment as it does require the certificate to be installed on the user's device in order to suppress the warnings or workaround security restrictions, which is why the certificate should be generated from a Trusted Certificate Authority (e.g. DigiCert etc.), so it only need to be installed on the server, which is probably why Azure Sql Servers seems to work fine. Otherwise, another alternative is to expose a Web API instead of connecting directly to the Sql Server as it's how most mobile application work with data. Regardless, TrustServerCertificate=true and Encrypt=false not working on Android tell us that using the "same" implementation that for Linux, Mac and Windows must have different security API with the Android OS that prevents us to use the flag bypass encryption, which is why it's still under investigation.

jovanmhn commented 1 year ago

While I cannot disagree with, in principle, anything @lcheunglci says, and I really do appreciate that the issue is being looked into, allow me to present a different point of view on the whole 'connecting to database directly from android' theme:

A lot of the temptation to use .NET to deploy to mobile comes from the ability to use already known methods and frameworks to get the job done, and having the ability to do it all inside a single project. For me personally, this is the no1 reason I decided to look into MAUI as a technology. If I am going to design my application as a simple UI that will have all the logic and 'meat' in some API in the background, I might as well use a more tested/stable technology (lets say Flutter), or maybe even go with some web app or even develop on each platform individually (at the end of the day, I am just making API calls, right?)...

Allow us, the developers, to determine what is a security risk for a certain application in a certain surrounding, let us determine how and when will I get my connection details, where I will store them, and how important is the data, how will I setup the sql users that the app uses, etc...

mharry32 commented 1 year ago

I find an unorthodox workaround , and it works.

clone the Micosoft.Data.SqlClient( I cloned the main branch , at that time the Microsoft team are developing the 5.x version of this library)
Open the solution "Microsoft.Data.SqlClient.sln" in VS 2019+ , and locate to the project "Microsoft.Data.SqlClient" under the "netcore" directory
Locate to "SendPreLoginHandshake" method in TdsParser.cs (near lines 705) , and add this line:

This line of code will set encrypt option flag to "NOT_SUP" , then later this method will inform the server do not try to encrypt the data using ssl , so it prevent the real cause of the error : The remote certificate validation callback doesn't work correctly on Android and it is tracked in dotnet/runtime#45741

After you modified the source code of the library , you can pack the library ( simply set the configuration to "release" and right click the project -> "Pack Microsoft.Data.SqlClient" ), and then you can find the nuget package in the "artifacts" directory . you can add it to your project , it will override the original library referencing by EFCore , or something else..

WARNING: If you chose this solution , it may reduce security , because the network communications between server and client is not protected by ssl.

janseris commented 1 year ago

I find an unorthodox workaround , and it works.
1. clone the Micosoft.Data.SqlClient( I cloned the main branch , at that time the Microsoft team are developing the 5.x version of this library)

2. Open the solution "Microsoft.Data.SqlClient.sln" in VS 2019+ , and locate to the project "Microsoft.Data.SqlClient" under the "netcore" directory

3. Locate to "SendPreLoginHandshake" method in TdsParser.cs (near lines 705) , and add this line:
This line of code will set encrypt option flag to "NOT_SUP" , then later this method will inform the server do not try to encrypt the data using ssl , so it prevent the real cause of the error : The remote certificate validation callback doesn't work correctly on Android and it is tracked in dotnet/runtime#45741
4. After you modified the source code of the library , you can pack the library ( simply set the configuration to "release" and right click the project -> "Pack Microsoft.Data.SqlClient" ), and then you can find the nuget package in the "artifacts" directory . you can add it to your project , it will override the original library referencing by EFCore , or something else..
WARNING: If you chose this solution , it may reduce security , because the network communications between server and client is not protected by ssl.

Thank you for tip. Also thanks for mentioning the issue with Android, that's something I've been looking for a long time. Does this also bypass SSL for login handshake? If yes, password to sql server is sent in plaintext

mharry32 commented 1 year ago

I find an unorthodox workaround , and it works.
1. clone the Micosoft.Data.SqlClient( I cloned the main branch , at that time the Microsoft team are developing the 5.x version of this library)

2. Open the solution "Microsoft.Data.SqlClient.sln" in VS 2019+ , and locate to the project "Microsoft.Data.SqlClient" under the "netcore" directory

3. Locate to "SendPreLoginHandshake" method in TdsParser.cs (near lines 705) , and add this line:
This line of code will set encrypt option flag to "NOT_SUP" , then later this method will inform the server do not try to encrypt the data using ssl , so it prevent the real cause of the error : The remote certificate validation callback doesn't work correctly on Android and it is tracked in dotnet/runtime#45741
4. After you modified the source code of the library , you can pack the library ( simply set the configuration to "release" and right click the project -> "Pack Microsoft.Data.SqlClient" ), and then you can find the nuget package in the "artifacts" directory . you can add it to your project , it will override the original library referencing by EFCore , or something else..
WARNING: If you chose this solution , it may reduce security , because the network communications between server and client is not protected by ssl.
Thank you for tip. Also thanks for mentioning the issue with Android, that's something I've been looking for a long time. Does this also bypass SSL for login handshake? If yes, password to sql server is sent in plaintext

I haven't done further testing yet , but i guess since encryption is disabled , all the datas are transfered in plaintext @janseris

lcheunglci commented 1 year ago

Yes, usually regardless if encryption is enabled or not, it would always encrypt the credentials; however, by hard coding the SqlClient driver to not support encryption, I believe the credentials will be sent over in plain text which makes it a security risk to consider as I mentioned in https://github.com/dotnet/SqlClient/issues/1840. However, from my understanding following the dotnet runtime issue https://github.com/dotnet/runtime/issues/45741 mentioned, there was a PR opened on the dotnet runtime repo to address the issue and would hopefully address the self-signed certificate issue on Android. https://github.com/dotnet/runtime/pull/77386

devmartin commented 1 year ago

Workaround:

If you are concerned with security this is not for you. This workaround disables server certificate verification in your app. My app is running on a closed network, so i am not that concerned.

I experienced this issue: SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)

I also noticed this error among some of the exception messages: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Trust anchor for certification path not found.

For context i tried this on Android 10 and 11 using SQL Server 2019. VisualStudio 2022 17.4.2 and MAUI with .NET 7 and Microsoft.Data.SqlClient 5.0.1

I followed the guide from noelex at StackOverflow (thanks for digging into the source code). The first answer in the thread: https://stackoverflow.com/questions/71047509/trust-anchor-for-certification-path-not-found-in-a-net-maui-project-trying-t

In short:

I created DangerousTrustProvider.cs and placed it in the folder: Platforms/Android
In MauiProgram.cs i call: Platforms.Android.DangerousTrustProvider.Register();
In my connection string i have: "Persist Security Info=True;TrustServerCertificate=True;...."

I didnt add DangerousAndroidMessageHandlerEmitter or modify manifest file.

Platforms/Android/DangerousTrustProvider.cs:

using System;
using Java.Net;
using Java.Security;
using Java.Security.Cert;
using Javax.Net.Ssl;

namespace MyApp.Platforms.Android
{
    internal class DangerousTrustProvider : Provider
    {
        private const string TRUST_PROVIDER_ALG = "DangerousTrustAlgorithm";
        private const string TRUST_PROVIDER_ID = "DangerousTrustProvider";

        public DangerousTrustProvider() : base(TRUST_PROVIDER_ID, 1, string.Empty)
        {
            var key = "TrustManagerFactory." + DangerousTrustManagerFactory.GetAlgorithm();
            var val = Java.Lang.Class.FromType(typeof(DangerousTrustManagerFactory)).Name;
            Put(key, val);
        }

        public static void Register()
        {
            Provider registered = Security.GetProvider(TRUST_PROVIDER_ID);
            if (null == registered)
            {
                Security.InsertProviderAt(new DangerousTrustProvider(), 1);
                Security.SetProperty("ssl.TrustManagerFactory.algorithm", TRUST_PROVIDER_ALG);
            }
        }

        public class DangerousTrustManager : X509ExtendedTrustManager
        {
            public override void CheckClientTrusted(X509Certificate[] chain, string authType, Socket socket) { }
            public override void CheckClientTrusted(X509Certificate[] chain, string authType, SSLEngine engine) { }
            public override void CheckClientTrusted(X509Certificate[] chain, string authType) { }
            public override void CheckServerTrusted(X509Certificate[] chain, string authType, Socket socket) { }
            public override void CheckServerTrusted(X509Certificate[] chain, string authType, SSLEngine engine) { }
            public override void CheckServerTrusted(X509Certificate[] chain, string authType) { }
            public override X509Certificate[] GetAcceptedIssuers() => Array.Empty<X509Certificate>();
        }

        public class DangerousTrustManagerFactory : TrustManagerFactorySpi
        {
            protected override void EngineInit(IManagerFactoryParameters mgrparams) { }
            protected override void EngineInit(KeyStore keystore) { }
            protected override ITrustManager[] EngineGetTrustManagers() => new ITrustManager[] { new DangerousTrustManager() };
            public static string GetAlgorithm() => TRUST_PROVIDER_ALG;
        }
    }
}

MauiProgram.cs:

public static class MauiProgram
{
    public static MauiApp CreateMauiApp()
    {
#if ANDROID && DEBUG
        Platforms.Android.DangerousTrustProvider.Register();
#endif

You could also call the Register() function from Platforms/Android/MainActivity.cs:

public class MainActivity : MauiAppCompatActivity
{
    protected override void OnCreate(Bundle bundle)
    {
        base.OnCreate(bundle);
#if DEBUG
        Platforms.Android.DangerousTrustProvider.Register();
#endif
    }
}

Update 26 NOV 2023:

I tried MAUI with .NET 8 and I am not experiencing the issue anymore. Now I only have "TrustServerCertificate=True" in my connection string.

For context I tested this using android 9, 10, 11, 12 and 13 using SQL Server 2019 + 2022, Microsoft.Data.SqlClient 5.1.2, VisualStudio 2022 17.8.1

salarcode commented 1 year ago

Workaround:

If you are concerned with security this is not for you. This workaround disables server certificate verification in your app. My app is running on a closed network, so i am not that concerned.

Brilliant, I confirm this workaround also works when using SslStream.AuthenticateAsClient and also RemoteCertificateValidationCallback is called which wasn't before.

jsegura17 commented 1 year ago

Workaround:

If you are concerned with security this is not for you. This workaround disables server certificate verification in your app. My app is running on a closed network, so i am not that concerned.

Waooooooooo your are a Rock Star!!!!.. The Workaround it's amazing......

yunisaga commented 1 year ago

Brilliant workaround for Closed networks.Thanks

energywave commented 1 year ago

Workaround:

If you are concerned with security this is not for you. This workaround disables server certificate verification in your app. My app is running on a closed network, so i am not that concerned.

Hey dude, I have to thank you so much!!! I'm working on industrial devices (like Zebra, Datalogic, Athesi and so on) that connect with an on premise SQL Server and that's a very common scenario in WMS, MES and those kinds of applications that works in the LAN. It surprised me so much that Microsoft SqlClient didn't work on Android! All my previous code (from Windows CE) would not be usable, otherwise. Your solution worked perfectly on MAUI .net 7, Android 10. Thank you, for real.

altmoola commented 1 year ago

Workaround:

If you are concerned with security this is not for you. This workaround disables server certificate verification in your app. My app is running on a closed network, so i am not that concerned.

Dude, I had to log in to thank you. Was absolutely pulling my hair out trying to figure this out. One question I had though, is it safe to assume that if this works, I probably only need to add the Self-Signed cert into the trusted CA on the device?

devmartin commented 1 year ago

@altmoola

With the workaround you don't need the certificate. The security issue is that your app will no longer validate the identity of the server you are connecting to and therefore the certificate is not needed. When connecting to the server, it will just say: whoever you claim to be, I trust you. This is not only true for the database connection, but also for any https connection your application might make.

I see three options:

Figure out how to setup connection using a certificate
Use a webservice which communicates with the database
Disable certificate validation (the workaround), if your app is running on a closed network, this might not be an issue.

I am still a bit confused about this issue. Ex.: It works when connecting from MAUI Windows app without a workaround, but not from android. It seems like the Android version insists on higher security unless it’s told otherwise.

@energywave I also work with WMS, MES using industrial devices like Zebra, Honeywell. Previous application was written for Windows.CE. I guess, same industry, same problems :)

hamja-prakash commented 1 year ago

Is there any solution for Release mode?

dotnet / SqlClient