azist / azos

A to Z Sky Operating System / Microservice Chassis Framework
MIT License
213 stars 29 forks source link

NET 6: MsSql provider converted to Microsoft.Data.SqlClient.dll as System.Data.SqlClient is not properly resolving the assembly reference in Net 6 #768

Open itadapter opened 2 years ago

itadapter commented 2 years ago

Microsoft.Data.SqlClient adds bloat:

So SQL connectivity support alone,

Adds 14 extra Microsoft DLLs at 3.48 mbytes

None of that is needed for anything but their SQL client.

itadapter commented 2 years ago

@g8perry Needed by DPIPE to connect to legacy systems today. A good example why how one benefits greatly from staying away from proprietary technologies like Microsoft SQL server :)

itadapter commented 2 years ago

The new Microsoft dll gives the same old message "Platform not supported"

itadapter commented 2 years ago

https://www.koskila.net/how-to-resolve-microsoft-data-sqlclient-is-not-supported-on-this-platform-in-an-azure-function-app/ “Microsoft.Data.SqlClient: Microsoft.Data.SqlClient is not supported on this platform.“. Reason: I’m not going to dig into the details, but essentially, you’re in dependency hell. Let’s try and get you out of there as quickly as possible!

Apparently Microsoft Build System still does not work properly and puts a wrong version of DLL file in the output.

This is the same issue we had before. I was able to solve this before by manually copying one of *.dll files taken from MSFT nuget package by hand.

The issue is still current in 2022:

https://github.com/dotnet/SqlClient/wiki/Frequently-Asked-Questions#11-why-do-i-get-a-platformnotsupported-exception-when-my-application-hits-a-sqlclient-method

Why do I get a PlatformNotSupported exception when my application hits a SqlClient method?

The Microsoft.Data.SqlClient NuGet package includes a number of DLLs supporting different .NET targets and different runtime platforms. If you are getting a PlatformNotSupported Exception when you don't think you should be, it ultimately means your application is not loading the appropriate DLL. There could be a number of reasons for this. The NuGet package structure and infrastructure around referencing and loading referenced NuGet packages includes logic that allows a package to contain multiple DLLs which implement support for different .NET and platform targets. Meaning a different DLL for .NET Framework, .NET Core, .NET Standard, Windows, Linux, etc. The NuGet infrastructure will automatically reference and load the appropriate DLL based on your application's needs.

If your application loads a DLL from a NuGet package directly, it bypasses all this logic and probably loads the incorrect DLL. The DLL in the NuGet package under lib/netstandard2.0/Microsoft.Data.SqlClient.dll is basically the fallback DLL for any unsupported target and simply throws the PlatformNotSupported exception for any call. This is a nicer exception than what you would otherwise get when running on a platform that does not have a DLL built for it. Ultimately, you want to use the NuGet package reference infrastructure or you would have to implement all this target framework and platform support logic yourself when determining which DLL to load.

Additionally, the NuGet package contains all the dependency information for the SqlClient library and facilitates the downloading and referencing of dependencies. If you reference and load an individual DLL manually, it is up to you to ensure all dependencies are also available to the SqlClient library.

itadapter commented 2 years ago

https://github.com/dotnet/SqlClient/issues/1521

JohnPKosh commented 2 years ago

This occurs when attempting to run dpipe locally.

                  "StackTrace": "   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(PacketHandle packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock)\r\n   at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)\r\n   at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)\r\n   at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData, SqlConnectionEncryptOption encrypt)\r\n   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword, SqlConnectionEncryptOption encrypt)\r\n   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)\r\n   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)\r\n   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)\r\n   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)\r\n   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)\r\n   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)\r\n   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)\r\n   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)\r\n   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)\r\n   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)\r\n   at Microsoft.Data.SqlClient.SqlConnection.Open()\r\n   at Azos.Data.Access.MsSql.MsSqlRpcHandler.GetSqlConnection(JsonDataMap headers)\r\n   at Azos.Data.Access.MsSql.MsSqlRpcHandler.ReadAsync(ReadRequest request)",
                  "AppId": "hub-dpip",
                  "AppName": "hub-dpip",
                  "ExternalStatus": null,
                  "InnerException": {
                    "TypeName": "System.ComponentModel.Win32Exception",
                    "Message": "The certificate chain was issued by an authority that is not trusted.",
                    "Code": 0,
                    "Source": "",
                    "StackTrace": "",
                    "AppId": "hub-dpip",
                    "AppName": "hub-dpip",
                    "ExternalStatus": null,
                    "InnerException": null
                  }
itadapter commented 2 years ago

Same error generated in plain console .NET6 app without any lib references. So this is a generic issue with SQL or bad connect string that now have changed because of driver change.

JohnPKosh commented 2 years ago

When connecting with Microsoft.Data.SqlClient newer versions require to add TrustServerCertificate=True unless you install cert on client.

var conn = "data source=someserver;initial catalog=user;user id=ASPTEST;password=password;TrustServerCertificate=True";
JohnPKosh commented 2 years ago

replacing on Windows dev PC dll from following location: image

JohnPKosh commented 2 years ago

also we need to be able to include the SNI dll shown here image

itadapter commented 2 years ago

https://github.com/dotnet/SqlClient/issues/1521 Added comment