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

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:

1. I created DangerousTrustProvider.cs and placed it in the folder: Platforms/Android
2. In MauiProgram.cs i call: Platforms.Android.DangerousTrustProvider.Register();
3. 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

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…

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 <certificates src="@raw/razvoj1"/> trust -> no success. Any ideas would be appreciated.

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

@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?

@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:

1. Figure out how to setup connection using a certificate
2. Use a webservice which communicates with the database
3. 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 😃

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

1. 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.

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.

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

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

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

1. 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).

2. Set your cert in SQL Server Configuration manager.

3. Export that certificate as Base-64 encoded X.509 (.CER) file.

4. Import that cer in Platforms\Android\Resources\raw folder (if it does not exist create it).

5. 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>

1. 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>

1. 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”;

2. You should be able to open SQLConnection with Microsoft.Data.SQLClient 5.0.0 and .net 7 MAUI

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

@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.

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…