Base solution for your next web application
Open Closed

SQL Always Encrypted #10148

User avatar
0
fgonzalez created

Hi

  • My product version: v9.3.0
  • My product type: Angular
  • My framework type: .net core

I am implementing SQL Always Encrypted, according to Microsoft documentation I need to add an additional connection parameter (Column Encryption Setting = enabled) in the connection string Default in appsettigns.json, to be able to perform the encryption and decryption tasks for tenants with shared database in main DB .

When adding the parameter to the connection string and run the project I get the following error:

Application startup exception: System.ArgumentException: Keyword not supported: 'column encryption setting'. at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms, Boolean firstKey) at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms) at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) at System.Data.SqlClient.SqlConnection..ctor(String connectionString) at Hangfire.SqlServer.SqlServerStorage.<.ctor>b__6_0() at Hangfire.SqlServer.SqlServerStorage.CreateAndOpenConnection() at Hangfire.SqlServer.SqlServerStorage.UseConnection[T](DbConnection dedicatedConnection, Func`2 func) at Hangfire.SqlServer.SqlServerStorage.UseConnection(DbConnection dedicatedConnection, Action`1 action) at Hangfire.SqlServer.SqlServerStorage.Initialize() at Hangfire.SqlServer.SqlServerStorage..ctor(String nameOrConnectionString, SqlServerStorageOptions options) at Hangfire.SqlServer.SqlServerStorage..ctor(String nameOrConnectionString) at Asincro.Camaleon.Web.CamaleonWebCoreModule.PreInitialize() in /Users/jsierra/Projects/Camaleon/aspnet-core/src/Asincro.Camaleon.Web.Core/CamaleonWebCoreModule.cs:line92 at Abp.Modules.AbpModuleManager.<>c.<StartModules>b__15_0(AbpModuleInfo module) at System.Collections.Generic.List`1.ForEach(Action`1 action) at Abp.Modules.AbpModuleManager.StartModules() at Abp.AbpBootstrapper.Initialize() at Abp.AspNetCore.AbpApplicationBuilderExtensions.InitializeAbp(IApplicationBuilder app) at Abp.AspNetCore.AbpApplicationBuilderExtensions.UseAbp(IApplicationBuilder app, Action`1 optionsAction) at Asincro.Camaleon.Web.Startup.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env, ILoggerFactory loggerFactory) in /Users/jsierra/Projects/Camaleon/aspnet-core/src/Asincro.Camaleon.Web.Host/Startup/Startup.cs:line205 at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder) at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.&lt;Build>b__0(IApplicationBuilder builder) at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app) at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<&gt;c__DisplayClass0_0.&lt;Configure&gt;g__MiddlewareFilterBuilder|0(IApplicationBuilder builder) at Microsoft.AspNetCore.Hosting.WebHost.BuildApplication() Unhandled exception. System.ArgumentException: Keyword not supported: 'column encryption setting'. at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms, Boolean firstKey) at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms) at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) at System.Data.SqlClient.SqlConnection..ctor(String connectionString) at Hangfire.SqlServer.SqlServerStorage.<.ctor>b__6_0() at Hangfire.SqlServer.SqlServerStorage.CreateAndOpenConnection() at Hangfire.SqlServer.SqlServerStorage.UseConnection[T](DbConnection dedicatedConnection, Func`2 func) at Hangfire.SqlServer.SqlServerStorage.UseConnection(DbConnection dedicatedConnection, Action`1 action) at Hangfire.SqlServer.SqlServerStorage.Initialize() at Hangfire.SqlServer.SqlServerStorage..ctor(String nameOrConnectionString, SqlServerStorageOptions options) at Hangfire.SqlServer.SqlServerStorage..ctor(String nameOrConnectionString) at Asincro.Camaleon.Web.CamaleonWebCoreModule.PreInitialize() in /Users/jsierra/Projects/Camaleon/aspnet-core/src/Asincro.Camaleon.Web.Core/CamaleonWebCoreModule.cs:line92 at Abp.Modules.AbpModuleManager.<>c.<StartModules>b__15_0(AbpModuleInfo module) at System.Collections.Generic.List`1.ForEach(Action`1 action) at Abp.Modules.AbpModuleManager.StartModules() at Abp.AbpBootstrapper.Initialize() at Abp.AspNetCore.AbpApplicationBuilderExtensions.InitializeAbp(IApplicationBuilder app) at Abp.AspNetCore.AbpApplicationBuilderExtensions.UseAbp(IApplicationBuilder app, Action`1 optionsAction) at Asincro.Camaleon.Web.Startup.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env, ILoggerFactory loggerFactory) in /Users/jsierra/Projects/Camaleon/aspnet-core/src/Asincro.Camaleon.Web.Host/Startup/Startup.cs:line205 at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder) at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.&lt;Build>b__0(IApplicationBuilder builder) at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app) at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<&gt;c__DisplayClass0_0.&lt;Configure&gt;g__MiddlewareFilterBuilder|0(IApplicationBuilder builder) at Microsoft.AspNetCore.Hosting.WebHost.BuildApplication() at Microsoft.AspNetCore.Hosting.WebHost.StartAsync(CancellationToken cancellationToken) at Microsoft.AspNetCore.Hosting.WebHostExtensions.RunAsync(IWebHost host, CancellationToken token, String startupMessage) at Microsoft.AspNetCore.Hosting.WebHostExtensions.RunAsync(IWebHost host, CancellationToken token, String startupMessage) at Microsoft.AspNetCore.Hosting.WebHostExtensions.RunAsync(IWebHost host, CancellationToken token) at Microsoft.AspNetCore.Hosting.WebHostExtensions.Run(IWebHost host) at Asincro.Camaleon.Web.Startup.Program.Main(String[] args) in /Users/jsierra/Projects/Camaleon/aspnet-core/src/Asincro.Camaleon.Web.Host/Startup/Program.cs:line12

<br> What I found on the web was that this error occurs when using the System.Data.SqlClient library, and it is recommended to use Microsoft.Data.SqlClient https://stackoverflow.com/questions/59826833/net-core-3-1-always-encrypted

It is important to mention that for tenants with databases other than the main one, SQL Always Encrypted works perfectly because the mentioned parameter was added in the CS of each tenant without problem.

Please could you give me some guidance on this?

Go to accepted answer
2 Answer(s)
  • User Avatar
    0
    fgonzalez created

    Update:

    The problem occurred with Hangfire is this library that generated conflict with the new parameter in the cs, what I did was add another special cs, without the parameter, for Hangfire in appsettings.json then change the following lines of code:

    appsettings.json

    "ConnectionStrings": {
  "Default":"Server=localhost; Database=xxx; User Id=xxx; Password=xxx; Column Encryption Setting = Enabled;",
  "Hangfire":"Server=localhost; Database=xxx; User Id=xxx; Password=xxx;"
},

    Startup.cs

    if (WebConsts.HangfireDashboardEnabled)
{
    //Hangfire(Enable to use Hangfire instead of default job manager)
    services.AddHangfire(config =>    {
        config.SetDataCompatibilityLevel(CompatibilityLevel.Version_170);
        config.UseColouredConsoleLogProvider();
        config.UseSimpleAssemblyNameTypeSerializer();
        config.UseRecommendedSerializerSettings();
        config.UseSqlServerStorage(_appConfiguration.GetConnectionString("Hangfire"), new SqlServerStorageOptions
        {            CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
            SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
            QueuePollInterval = TimeSpan.Zero,
            UseRecommendedIsolationLevel = true,
            DisableGlobalLocks = true,
            UsePageLocksOnDequeue = true
        });    });
}

    WebCoreModule.cs

    JobStorage.Current = new SqlServerStorage(_appConfiguration.GetConnectionString("Hangfire"));

    Do I have to change something else in the ABP Zero Project so that Hangfire does not have problems?

  • User Avatar
    0
    ismcagdas created
    Support Team

    Hi,

    Hangfire doesn't support this, see https://github.com/HangfireIO/Hangfire/issues/1297. You can disable Hangfire or replace it with an alternative.

Assignee
No one
Labels
None yet