Currently, we are using LLBLGen to generate data access code to connect and fetch data from SQL server. We want to introduce additional security in the system, so we are trying to introduce SQL server Always Encryption with secure enclave. Does LLBLGen Pro Support this sql feature.

It does in Adapter, where you can override the CreateNewPhysicalConnection (https://www.llblgen.com/Documentation/5.11/ReferenceManuals/LLBLGenProRTF/html/F32BF935.htm), call the base to get access to the newly created connection object and then set the properties accordingly

I have tried to override CreateNewPhysicalConnection Method and even changed factory to Microsoft.Data.SqlClient. However, Override CreateSelectDQ method of DynamicQueryEngine class giving error "The SqlParameterCollection only accept non-null Microsoft.Data.SqlClient objects, not System.Data.SqlClient.SqlParameter object". Always Encryption with secure enclave only works with Microsoft.Data.SqlClient library and these classes by default creating object of System.Data.SqlClient.

Ashutosh wrote:

I have tried to override CreateNewPhysicalConnection Method and even changed factory to Microsoft.Data.SqlClient. However, Override CreateSelectDQ method of DynamicQueryEngine class giving error "The SqlParameterCollection only accept non-null Microsoft.Data.SqlClient objects, not System.Data.SqlClient.SqlParameter object". Always Encryption with secure enclave only works with Microsoft.Data.SqlClient library and these classes by default creating object of System.Data.SqlClient.

You then have to configure the factory properly. I assume you're on .NET 6+ so you need to use the RuntimeConfiguration: https://www.llblgen.com/Documentation/5.11/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_runtimeconfiguration.htm#dbproviderfactory Instead of the System.Data.SqlClient factory you have to specify the Microsoft.Data.Sqlclient.

We are working on.net framework 4.8. I tried to use the RuntimeConfiguration however it is not working. Also tried to add provider detail in web.config still its not working.

Ashutosh wrote:

We are working on.net framework 4.8. I tried to use the RuntimeConfiguration however it is not working. Also tried to add provider detail in web.config still its not working.

You have to use the RuntimeConfiguration and not the web.config file, however you have to make sure it's used prior to any query in your app. "Is not working" and "doesn't work" has little value, tho. Did you run into an exception, what code did you try? We tested it with the Microsoft.Data.SqlClient and .NET 4.8 and it worked fine.

RuntimeConfiguration.AddConnectionString("ConnectionString.SQL Server (SqlClient)", "data source=devserver;initial catalog=Northwind;integrated security=SSPI;persist security info=False;packet size=4096;Encrypt=false");
RuntimeConfiguration.ConfigureDQE<SQLServerDQEConfiguration>(c => c.SetTraceLevel(TraceLevel.Verbose)
                                                                   .AddDbProviderFactory(typeof(Microsoft.Data.SqlClient.SqlClientFactory))
                                                                   .SetDefaultCompatibilityLevel(SqlServerCompatibilityLevel.SqlServer2012));
RuntimeConfiguration.Tracing.SetTraceLevel("ORMPersistenceExecution", TraceLevel.Info);
RuntimeConfiguration.Tracing.SetTraceLevel("ORMPlainSQLQueryExecution", TraceLevel.Info);

I have tried to use RuntimeConfiguration, however this class does not exist in SD.LLBLGen.Pro.ORMSupportClasses V5.0 and we are using the same version of ORMSupportClasses. We can't update to latest version as it will be altogether a huge task as I tried, and it is giving lots of error. Any other way to accomplish that. Also, I was reading reference document for LLBLGen Pro and found that it only support "SQL Server 2016+ Always Encrypted support" not secure enclave. Does it support secure enclave feature also?

Is there any way to register factory in V5.0 version instead of runtimeconfiguration class.

Ashutosh wrote:

I have tried to use RuntimeConfiguration, however this class does not exist in SD.LLBLGen.Pro.ORMSupportClasses V5.0 and we are using the same version of ORMSupportClasses. We can't update to latest version as it will be altogether a huge task as I tried, and it is giving lots of error. Any other way to accomplish that. Also, I was reading reference document for LLBLGen Pro and found that it only support "SQL Server 2016+ Always Encrypted support" not secure enclave. Does it support secure enclave feature also?

Is there any way to register factory in V5.0 version instead of runtimeconfiguration class.

5.0 is out of support for quite some time. You should have mentioned this in your start post so we wouldn't have wasted time to investigate why it doesn't work for you on the latest runtime.

The main thing that's needed for secure enclaves and always encrypted is the usage of the right ADO.NET provider, and the right connection string specifications (which are mentioned in the microsoft tutorials/docs about this). The runtime by default uses System.Data.SqlClient, to enforce a different ADO.NET provider you have to alter the common name for the DbProviderFactory. This is defined in the SqlServerSpecificCreator.cs file at the top. So you have to compile from source (it's available to you on the website -> log in -> My account -> Downloads -> v5.0 -> Extras section). You have to sign the runtimes yourself or remove the strong name reference from teh csproj files.

Or you can upgrade to v5.11.1