LinqQueryAsync throwing connection error if we setup Persist Security Info=False in connection string. (OSOE-801)

[mohit-naroliya]

SQL login failed due to incorrect username and password, which was being thrown from the _session.LinqQueryAsync method. We investigated and discovered that the transaction.Connection.ConnectionString did not contain the password. As a workaround, we configured the Persist Security Info flag as true, which resolved the issue.

We received exceptions from Azure Application Insights, but no exceptions appeared on the local environment console.

Jira issue

[Piedone]

Maybe we can pass the transaction or the DB connection instead of using the connection string. ConfigurationInfo.GetDataProvider() that DataConnection.GetDataProvider() uses internally supports that, but we can't access that type.

However, a question is, why does linq2db tries to connect to the DB on its own, when we pass in the transaction via LinqToDbConnection already?

[MaceWindu]

linq2db needs to know which SQL Server dialect to use. For that you need to specify SqlServerVersion enum value in connection options or it will default to SqlServerVersion.AutoDetect which connects to SQL Server to query it's version

[Piedone]

Thanks for chiming in! Do you mean that if we specify SqlServerVersion somewhere (I'm not sure where that would be in our use case yet, since we try to use an existing transaction) then this initial unauthenticated SQL query won't happen, and the subsequent actual queries will use the authenticated connection?

[MaceWindu]

In your case you can do it a bit differently. Change this line

Helpful-Libraries/Lombiq.HelpfulLibraries.LinqToDb/LinqToDbQueryExecutor.cs

Line 81 in 756c82c

"SqlServer" => "Microsoft.Data.SqlClient",

to

 "SqlServer" => ProviderName.SqlServer20xx,

1. you don't need to specify Microsoft.Data.SqlClient as it is default provider for SqlServer since AFAIC linq2db 4.0
2. SqlServer20xx should specify dialect version you want to use

Dialect version shouldn't be greater than your database version to avoid incompatible sql generation
But it could have lower version if your code could be used with different SQL Server versions

[Piedone]

Ah, great, thank you! I did this and it appears to be working well. @mohit-naroliya, I released a v9.1.1-alpha.1.osoe-801 just with this fix (note that v9+ requires Orchard Core >=1.8.2), could you please check?