Cannot connect to a sql database using system.data.sqlclient and native aot win 64 publish

[jdurnil]

Hello, using .net 8 preview 6 and trying to use the latest system.data.sqlclient nuget package to connect to a sql database. the project runs fine from visual studio, connects to my database using sql server id and password and returns data. i am building off the new aot api project created from command line. However after publish and running the exe in the publish folder i put a try catch on my db connection to catch the error and get this. Mind you its not my connection string as that works fine running the same app from visual studio.
A type initializer threw an exception. To determine which type, inspect the InnerException's StackTrace property.
A type initializer threw an exception. To determine which type, inspect the InnerException's StackTrace property.
at System.Runtime.CompilerServices.ClassConstructorRunner.EnsureClassConstructorRun(StaticClassConstructionContext*) + 0x14d
at System.Runtime.CompilerServices.ClassConstructorRunner.CheckStaticClassConstructionReturnNonGCStaticBase(StaticClassConstructionContext*, IntPtr) + 0xd
at System.Data.SqlClient.TdsParser..ctor(Boolean, Boolean) + 0x60
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo, String, SecureString, Boolean, SqlConnectionString, SqlCredential, TimeoutTimer) + 0x144
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer, SqlConnectionString, SqlCredential, String, SecureString, Boolean) + 0x1b2
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity, SqlConnectionString, SqlCredential, Object, String, SecureString, Boolean, SqlConnectionString, SessionData, Boolean, String) + 0x362
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions, DbConnectionPoolKey, Object, DbConnectionPool, DbConnection, DbConnectionOptions) + 0x2e7
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool, DbConnection, DbConnectionOptions, DbConnectionPoolKey, DbConnectionOptions) + 0x33
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection, DbConnectionOptions, DbConnectionInternal) + 0x2a8
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection, DbConnectionOptions, DbConnectionInternal) + 0x5e
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection, UInt32, Boolean, Boolean, DbConnectionOptions, DbConnectionInternal&) + 0x35b
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection, TaskCompletionSource1, DbConnectionOptions, DbConnectionInternal&) + 0x8b at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection, TaskCompletionSource1, DbConnectionOptions, DbConnectionInternal, DbConnectionInternal&) + 0x16c
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection, DbConnectionFactory, TaskCompletionSource1, DbConnectionOptions) + 0x118 at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1) + 0x101
at System.Data.SqlClient.SqlConnection.Open() + 0xf8
at MyFirstAotWebApi.SQLQuery.GetTodos() + 0x9c

Tagging subscribers to this area: @DavoudEshtehari, @David-Engel, @JRahnama
See info in area-owners.md if you want to be subscribed.

Issue Details

---

Hello, using .net 8 preview 6 and trying to use the latest system.data.sqlclient nuget package to connect to a sql database. the project runs fine from visual studio, connects to my database using sql server id and password and returns data. i am building off the new aot api project created from command line. However after publish and running the exe in the publish folder i put a try catch on my db connection to catch the error and get this. Mind you its not my connection string as that works fine running the same app from visual studio.
A type initializer threw an exception. To determine which type, inspect the InnerException's StackTrace property.
A type initializer threw an exception. To determine which type, inspect the InnerException's StackTrace property.
at System.Runtime.CompilerServices.ClassConstructorRunner.EnsureClassConstructorRun(StaticClassConstructionContext*) + 0x14d
at System.Runtime.CompilerServices.ClassConstructorRunner.CheckStaticClassConstructionReturnNonGCStaticBase(StaticClassConstructionContext*, IntPtr) + 0xd
at System.Data.SqlClient.TdsParser..ctor(Boolean, Boolean) + 0x60
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo, String, SecureString, Boolean, SqlConnectionString, SqlCredential, TimeoutTimer) + 0x144
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer, SqlConnectionString, SqlCredential, String, SecureString, Boolean) + 0x1b2
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity, SqlConnectionString, SqlCredential, Object, String, SecureString, Boolean, SqlConnectionString, SessionData, Boolean, String) + 0x362
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions, DbConnectionPoolKey, Object, DbConnectionPool, DbConnection, DbConnectionOptions) + 0x2e7
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool, DbConnection, DbConnectionOptions, DbConnectionPoolKey, DbConnectionOptions) + 0x33
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection, DbConnectionOptions, DbConnectionInternal) + 0x2a8
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection, DbConnectionOptions, DbConnectionInternal) + 0x5e
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection, UInt32, Boolean, Boolean, DbConnectionOptions, DbConnectionInternal&) + 0x35b
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection, TaskCompletionSource1, DbConnectionOptions, DbConnectionInternal&) + 0x8b at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection, TaskCompletionSource1, DbConnectionOptions, DbConnectionInternal, DbConnectionInternal&) + 0x16c
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection, DbConnectionFactory, TaskCompletionSource1, DbConnectionOptions) + 0x118 at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1) + 0x101
at System.Data.SqlClient.SqlConnection.Open() + 0xf8
at MyFirstAotWebApi.SQLQuery.GetTodos() + 0x9c

Author:	jdurnil
Assignees:	-
Labels:	area-System.Data.SqlClient, untriaged
Milestone:	-

[vitek-karas]

When you publish the app for NativeAOT - does it produce warnings?
I don't think SqlClient is AOT compatible yet.

[jdurnil]

No warnings are produced. The publish goes through fine but the executable produces the error I gave.

…

On Tue, Aug 1, 2023 at 5:02 PM Vitek Karas ***@***.***> wrote: When you publish the app for NativeAOT - does it produce warnings? I don't think SqlClient is AOT compatible yet. — Reply to this email directly, view it on GitHub <#89782 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB7DCF6BZZWDA4LKAJ6G433XTFVGHANCNFSM6AAAAAA273CNL4> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[vitek-karas]

Any chance for a repro? Or at least how you're using SqlClient.

[Wraith2]

It's likely that the native component required for network connectivity has not been restored, look for System.Data.SqlClient.*.sni.dll. Also, as @vitek-karas says System.Data.SqlClient has not been audited and made AOT safe because it no longer ships as part of the framework. The replacement is Microsoft.Data.SqlClient and work to make it AOT safe is on-going.

[jdurnil]

In the publish folder for the executable there is an sni.dll that is there along with the executable. Everything was restored prior to publish but it is still failing.Thank you,Jason DurnilOn Aug 1, 2023, at 8:11 PM, Wraith ***@***.***> wrote: It's likely that the native component required for network connectivity has not been restored, look for System.Data.SqlClient.*.sni.dll. Also, as @vitek-karas says System.Data.SqlClient has not been audited and made AOT safe because it no longer ships as part of the framework. The replacement is Microsoft.Data.SqlClient and work to make it AOT safe is on-going. —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***>

[jdurnil]

Ok, maybe getting somewhere, I switched to Microsoft.data.sqlclient and am now getting the following error which someone may be able to tell me how to fix, the Microsoft.data.sqlclient.sni.dll is showing up in the publish folder. I am now getting this

Only the invariant culture is supported in globalization-invariant mode. See https://aka.ms/GlobalizationInvariantMode for more information. (Parameter 'name')
en-us is an invalid culture identifier.

at System.Globalization.CultureInfo.GetCultureInfo(String) + 0x189
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1, SqlConnectionOverrides) + 0x73
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides) + 0x1ed
at MyFirstAotWebApi.SQLQuery.GetTodos() + 0x96

[Wraith2]

try this https://blog.wraithware.org/2020/05/23/SqlClient-and-Globalization-Invariant-mode.html

[jdurnil]

Actually, thanks for all the help again, this was set to true in my project file
<InvariantGlobalization>false</InvariantGlobalization>
so i set it to false and it worked!!!
I do get a ssl error and have to set Encrypt=False in my connection string which i do not get when running in debug mode. Any ideas how to set this to use the local certificate store in the connection, forgive my ignorance. Otherwise all the sql stuff is working in my executable. I love the new aot web api but it is pretty useless without being able to connect to a database. Anyway if i can get the necrytpion thing working im off and running.

[Wraith2]

Set TustServerCertificate to true in your connection string. Microsoft.Data.SqlClient started being more secure a few versions ago and no longer defaults to allowing no encryption or automatically trusting server certificates.

You can also try it with AOT and see what works. It's been at least partially annotated and using most features should work. If there are things that don't work open issues on the the https://github.com/dotnet/SqlClient repo with a reproduction and it'll get looked into.