Android - cannot connect to SQL Server Express 2019 using any casual SqlClient version (v2.1.4, v4.1.0, v5Preview) #1662
Comments
janseris
changed the title
|
Note: Day 2:
I read here: https://docs.microsoft.com/en-us/sql/connect/ado-net/introduction-microsoft-data-sqlclient-namespace?view=sql-server-ver15#breaking-changes-in-40 That since verison 4.0.0, Microsoft.Data.SqlClient uses Encrypt=true request on server (which if it can, then encrypts the data transfer communication). What is strange about this is inconsistency: EF Core 6.0.1 on one project does not output the error and on the other one it does (and incorrectly because Microsoft.Data.SqlClient 2.1.4 should be not encrypting by default. Why doesn't it work in a way such that this error about certificate on Windows is received only on parts with Microsoft.Data.SqlClient 4.0.0 and newer? Why is this error received with Microsoft.Data.SqlClient 2.1.4 and also with System.Data.SqlClient 4.8,3? |
|
The app has been updated for faster testing and examination. It now allows selecting connection string, entering a custom connection string, adding suffixes to the connection string, disable connection pooling (that did not help getting different results on different versions of SqlClient on Windows machine concerning the certificate though) etc.
The results for me are (for all versions of SqlClient):
What is the expected result:
P.S. results are printed to Debug Output as well. |
|
Hi, no it is not but the sample app can be used to test both. And also about inconsistent certificate issues (randomly yes and no) with SQL Express 2019 when connecting from Windows but there is a workaround for that by ignoring the certificate so it is not important. There is no workaround for the Android issue though and it's fatal. I believe there is an issue with various other versions of SQL Server or SQL Server Express called from Android because I saw multiple threads about that and none with a solution but I don't have access to more versions than these which I documented so I cannot tell for other SQL Server versions. |
|
one other step to check before going deeper into the issue. Can you look into
and make sure the
|
|
Hi @janseris, we spend a LOT of hours to get this working. You have to use certificates, without them there is no connection possible. Setup the Device and Server
Setup the MAUI-App (You have to explicitly allow User Certificates)
Give a Try. Links: |
|
@janseris were you able to make a connection to server with the provided information above? |
Hi, I had days off last week. I hope to get to this later this week. I haven't tried yet but saw your tips. Thank you. |
|
Hello, I've been dealing with this error for 2-3 days, SQL SERVER 2019 Express is installed and all settings are done, even if I install the certificate, it is not accepted, I am trying to connect with DbCOntext via EF CORE.
|
|
@DavoudEshtehari, @stegl83 |
|
@janseris |
|
@FikretAkin thank you for tip but currently I am not planning this solution because this is a very expensive workaround. |
|
i know the security of "direct" communicate with SQL Server Database. but we just "connect" locally, and no security issues here with small team like me. and too expensive with Restfull API, because we are not big team or corporate. |
|
Thanks @janseris for your thorough analysis. I'm currently investigating dotnet/maui#1412 and it seems like a similar issue. I'm able to reproduce the connection failure issue using Sql Server 2019 express edition, and my .NET6 MAUI app running with MDS v5.0 on the Android Emulator running Android 12.1. Also did some digging and saw the issue also mentioned here dotnet/maui#3522 that the project needs to set it's SSL/TLS Implementation to |
|
@lcheunglci
Yes you are right. It disappeared with one of last MAUI release candidates in May/April 2022 and was there because of HttpClient issues I think. |
|
Thanks for the insight @janseris, I'll keep that in mind when I'm doing my testing. |
|
btw. there are very similar issues with SSL in SMTP, WebSocket and gRPC to this one also for MAUI Android. SMTP: dotnet/maui#9587 probably related and matching Android versions, too
|
|
Hi folks, sorry for replying so late. Here are some information about creating a valid cert. this is the first call to openssl-Tool: (of course, you have to replace every "[PCName].[Domain].local" in cnf-File) Second call to openssl to create PFX from CRT and KEY: openssl pkcs12 -export -in mycert27.crt -inkey mykey27.key -out mycert27.pfx After creating these to files, you can install them on server and device as described in my previous post. |
|
@stegl83 |
|
Hi, I managed to get it working on the android emulator running Android 21 with Sql Server 2019 and M.D.SqlClient 5.0 GA. After I generated the self signed certs and installing them on both the sql server and android emulator by following the instructions from @stegl83 on creating the self signed certificate here and installing certs i.e. installing pfx file on the my local sql server and the crt file onto the android device by copying it using Go into Settings > Security > Encryptions & credentials > Install a certificate > CA Certificate Since my android emulator and the sql server were both on the same machine. I found this article on the MAUI documentation and use the 10.0.2.2 ip address instead of my machine's hostname and adding the following to the network_security_config.xml in addition to the previous instructions on the MAUI side. Now boot up your app, and connect to 10.0.2.2,1433 or whichever port you chose for your Sql Server and it should get passed the and the connection string I used is: note: don't use As soon as I removed the self-signed cert from either end i.e. android or sql server, the error 35 returns. I recall reading that the Azure Sql Server doesn't run into this issue, which I'm going to guess is because it's the url i.e. Let me know if this works for you. |
|
@lcheunglci Perfect. |
|
From a brief look at how the |
|
I'm glad that I could help you. The next step is to find if its possible to rollout a certificate by MDM (Android Enterprise). |
|
Managed to solve it with cert and IP address.
<?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>
<?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>
|
|
Thanks @thevirtualdj for providing the steps in concise and organized manner. It'll definitely make it easier for others to follow. As for the |
The SSL/TLS moment is here during SQL Server login process. This part of the connection is always encrypted even when Encrypt=false. Otherwise, plaintext login and password would be sent over the network because SQL Server communication is not encrypted by default. AFAIK what we are talking all the time about here is that the SSL problem is in the login process because everything fails at this moment (the Encrypt true /false option isn't used at all because the actual data transfers aren't even initiated when login fails because of Android errors) |
Glad you managed to get it rolling :) Hope that .NET team will manage to solve usesCleartextTraffic case. Because in most cases apps are hidden behind VPN or some private net and does not need for cert encryption on this level. |
Clear text is quite dangerous. Small businesses which use direct communication to db to save api development aren't probably the case you describe. Going plaintext on sending password to the DB over the network is the worst solution. |
We understand that, but we are having a lot of troubles with certificates. [System.err] java.lang.IllegalStateException: Handshake has already been started |
|
@thevirtualdj |
|
We still have no solution to make it works without a cert right now, right? |
Yes, that is correct for a local/on-prem SQL Server. I believe it works for an Azure SQL Database without manually trusting the self-signed certificate on the device because the certificate comes from a trusted certificate authority. My assumption is that implementation to Trust Server Certificate is overwritten by the OS security levels on the device, but further investigation is required. |
I followed your steps but still getting the same error with SQL connection, Microsoft.Data.SQLClient 5.0.1 on SQL server 2019 but still get the error 31 T_T btw i tested on Android 9 and Android 11 real devices. |
|
There are good news about the issue: Maybe we have a solution in .NET 8. |
I followed that post while ago, the bad thing is that we have to wait 10 months, Right? xD |
Yeah, right. |
|
I'm closing this issue as it's an issue caused by the runtime and is fixed when a new version of .NET is released. |
Describe the bug
Edit: please use the test application version added in the latest comment in this issue (more functions, less hardcoded)
Introduction:
This is a continuation of this issue:
#1656
There, only Android 9 and higher can communicate with a SQL Server 2012 with latest patch and a valid certificate from DigiCert.
The message in SqlException is always the same for both of these issues:
(
A connection was successfully established with the server, but then an error occurred during the pre-login handshake.)In this issue, I have set up a local SQL Server Express 2019 instance and opened it to the local network (port 1433).
TCP/IP connection is enabled and a SQL account for authentication is set.
These versions of SQL Server API from C# fail to communicate to the instance from Android (.NET MAUI):
Microsoft.Data.SqlClient 2.1.4 (EF Core 6.0.6)Microsoft.Data.SqlClient 5.0.0-preview2.22096.2 (EF Core 7.0.0-preview.5.22302.2)Microsoft.Data.SqlClient 4.1.0Microsoft.Data.SqlClient 2.0.0Microsoft.Data.SqlClient 5.0.0-preview3.22168.1System.Data.SqlClient 4.8.3Result of the test:
No Android verison can communicate with the SQL Server Express 2019 instance properly after connection
SqlExceptionofClass 20(A connection was successfully established with the server, but then an error occurred during the pre-login handshake.)The Android devices do in fact connect to the server, because if they did not, I would have received a different SqlException.
TrustServerCertificate=true;and/orEncrypt=false;does not help solving the errorTest project:
About the test project:
Debug Output windowor place a breakpoint or execute step-by-step with debugger to observe the results (Label UI controls (which are supposed to show the text result) in MAUI are bugged and do not show on Android)SqlClients listed aboveHere a detailed look into a separate test of
Microsoft.Data.SqlClient 2.1.4(EF Core 6.0.6= current) alone:The following are results (debug output from Visual Studio) with internal errors on Android versions 5,6,8,9,11,12 for attempted communication with SQL Server Express 2019 (all fail with the same error "pre-login handshake...") for EF COre 6.0.6 (using SqlClient 2.1.4 internally)
Encrypt=falseandTrustServerCertificate=true-
[System.err] javax.net.ssl.SSLHandshakeException: java.security.cert.CertPathValidatorException: Trust anchor for certification path not found. [System.err] at com.android.org.conscrypt.SSLUtils.toSSLHandshakeException(SSLUtils.java:363) [System.err] at com.android.org.conscrypt.ConscryptEngine.convertException(ConscryptEngine.java:1134) [System.err] at com.android.org.conscrypt.ConscryptEngine.readPlaintextData(ConscryptEngine.java:1089) [System.err] at com.android.org.conscrypt.ConscryptEngine.unwrap(ConscryptEngine.java:876) [System.err] at com.android.org.conscrypt.ConscryptEngine.unwrap(ConscryptEngine.java:747) [System.err] at com.android.org.conscrypt.ConscryptEngine.unwrap(ConscryptEngine.java:712) [System.err] at com.android.org.conscrypt.Java8EngineWrapper.unwrap(Java8EngineWrapper.java:237) [System.err] at crc64fcf28c0e24b4cc31.ButtonHandler_ButtonClickListener.n_onClick(Native Method) [System.err] at crc64fcf28c0e24b4cc31.ButtonHandler_ButtonClickListener.onClick(ButtonHandler_ButtonClickListener.java:30) [System.err] at android.view.View.performClick(View.java:7451) [System.err] at com.google.android.material.button.MaterialButton.performClick(MaterialButton.java:1194) [System.err] at android.view.View.performClickInternal(View.java:7425) [System.err] at android.view.View.access$3700(View.java:842) [System.err] at android.view.View$PerformClick.run(View.java:28690) [System.err] at android.os.Handler.handleCallback(Handler.java:938) [System.err] at android.os.Handler.dispatchMessage(Handler.java:99) [System.err] at android.os.Looper.loopOnce(Looper.java:346) [System.err] at android.os.Looper.loop(Looper.java:475) [System.err] at android.app.ActivityThread.main(ActivityThread.java:7889) [System.err] at java.lang.reflect.Method.invoke(Native Method) [System.err] at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:548) [System.err] at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1009) [System.err] Caused by: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Trust anchor for certification path not found. [System.err] at com.android.org.conscrypt.TrustManagerImpl.verifyChain(TrustManagerImpl.java:672) [System.err] at com.android.org.conscrypt.TrustManagerImpl.checkTrustedRecursive(TrustManagerImpl.java:549) [System.err] at com.android.org.conscrypt.TrustManagerImpl.checkTrusted(TrustManagerImpl.java:505) [System.err] at com.android.org.conscrypt.TrustManagerImpl.checkTrusted(TrustManagerImpl.java:425) [System.err] at com.android.org.conscrypt.TrustManagerImpl.getTrustedChainForServer(TrustManagerImpl.java:368) [System.err] at android.security.net.config.NetworkSecurityTrustManager.checkServerTrusted(NetworkSecurityTrustManager.java:102) [System.err] at android.security.net.config.RootTrustManager.checkServerTrusted(RootTrustManager.java:106) [System.err] at com.android.org.conscrypt.Platform.checkServerTrusted(Platform.java:255) [System.err] at com.android.org.conscrypt.ConscryptEngine.verifyCertificateChain(ConscryptEngine.java:1638) [System.err] at com.android.org.conscrypt.NativeCrypto.ENGINE_SSL_read_direct(Native Method) [System.err] at com.android.org.conscrypt.NativeSsl.readDirectByteBuffer(NativeSsl.java:569) [System.err] at com.android.org.conscrypt.ConscryptEngine.readPlaintextDataDirect(ConscryptEngine.java:1095) [System.err] at com.android.org.conscrypt.ConscryptEngine.readPlaintextDataHeap(ConscryptEngine.java:1115) [System.err] at com.android.org.conscrypt.ConscryptEngine.readPlaintextData(ConscryptEngine.java:1087) [System.err] ... 19 more [System.err] Caused by: java.security.cert.CertPathValidatorException: Trust anchor for certification path not found. [System.err] ... 33 moreTo reproduce
sa)Note:
OrdinacedevReminsasaa table calledUSERin the defaultdboschemathe EF test projects require that it has some columns but you can skip using the EF Core projects because they useSqlClientanywaysthe other projects simply select number of rows from thethe current version simply executesUSERtable (could have selected something from a dummy table or be doingSELECT 'Hello World')SELECT LEN('Hello World')Note dotnet/maui#2:
192.168.0.234ipconfigon the host machine running the SQL Express (this example requires that the Android device and the machine hosting the SQL Server are in the same local network so that private IP can be used (or you can share your SQL Server Express to the internet and use a public IP address in fact but I did not do that)IPv4 Addressentry in the output of theipconfigcommand, that's the private/local IP addressExpected behavior
Android can communicate with the SQL Server Express.
Further technical details
.NET target: .NET 6.0 (
dotnet --version: 6.0.400-preview.22301.10)SQL Server version: SQL Server Express 2019
Operating system: Windows 10 21H1
The text was updated successfully, but these errors were encountered: