Posted on

How to use Microsoft SQL Always On Column Encryption?

Issue:

How to use Microsoft SQL 2017 Always On Column Encryption?

Environment:

Windows, MS SQL 2017, SSMS 2017, Visual Studio 2019

Resolution:

Follow these steps to get unencrypted value with different tools.

Step-by-step instructions:-

Setting Up Encrypted Column on a Table

  1. Right click on a table and select “Encrypt Columns…”
  2. Depend on what type of column, choose “Randomized” if possible for better security
  3. Select default for other values
  4. Follow the screen instructions and finish with successful message
  5. You keys are created and column is ready to store encrypted data.
  6. See next section for inserting and retrieving data.

Insert Value into Encrypted Column with SSMS 2017

  1. Enable Query Parameterization on SSMS
  2. Insert Query with parameter for encrypted data 
    DECLARE @myEncryptedColumnValue char(36) = '09DF3BDF-078E-EA11-A2FA-005056A14182';

INSERT INTO [dbo].[myEncryptedColumnTable]
           ([myEncryptedColumn])
     VALUES
           (@keyEncrypted)
  3. Select Query to retrieve encrypted data (no special syntax needed, just connection)
SELECT * FROM [dbo].[myEncryptedColumnTable]

Notes:
Make sure your connect to database server is with “Column Encryption Setting = Enabled”

Import/Export Encrypted Column Data with SSMS 2017

  1. Right click on DB Name and select Task, Export
  2. On Choose a Source, select “.NET Framework Data Provider for SqlServer”
  3. On the same screen, select Enabled next to “Column Encryption Seeting”, Select True next to “Intergrated Security” if you’re using that, enter [myServerName] next to “Data Source”, and [myDBName] next to “Initial Catalog”.
  4. On Choose a Destination, select “.NET Framework Data Provider for SqlServer”
  5. On the same screen, select Enabled next to “Column Encryption Seeting”, Select True next to “Intergrated Security” if you’re using that, enter [myOtherServerName] next to “Data Source”, and [myDBName] next to “Initial Catalog”.
  6. Check you destination encrypted column.
    Notes:

    • Make sure your connect to destination server is with “Column Encryption Setting = Enabled“
    • Make sure you created destination keys using the same keys you created on source database.

Permission Needed for User Other Then db_owner

  1. Create a database role and assign role to user 
    CREATE ROLE [ReadEncryptedColumn] AUTHORIZATION [dbo]
GO
GRANT SELECT ON [dbo].[myColumnEncryptedTable] TO [ReadEncryptedColumn]
GO
GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO [ReadEncryptedColumn]
GO
GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO [ReadEncryptedColumn]
GO
  2. Notes:
    • Make sure user connect to destination server with “Column Encryption Setting = Enabled“

Troubleshoot:

Testing Encrypted column with PowerShell

#seeing encrypted value
$serverName = "myServerName"
$databaseName = "myDBName"
$strConn = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
Invoke-Sqlcmd -Query "SELECT TOP(10) * FROM myColumnEncryptedTable" -ConnectionString $strConn| 
format-table -AutoSize

#seeing unencrypted value
$strConn = $strConn + "; Column Encryption Setting = Enabled"
Invoke-Sqlcmd -Query "SELECT TOP(10) * FROM myColumnEncryptedTable" -ConnectionString $strConn| 
format-table -AutoSize

Reference:

Always Encrypted

Posted on

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

Issue:

I have a MVC app that read Microsoft Access file (mdb) and update the file with latest data from SQL. Everything work on local machine until when I published to Web Server, I get following error:

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

Why I’m getting the error?

System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)at System.Data.OleDb.OleDbConnection.Open()at AppName.Web.Areas.Admin.Controllers.AdminController.Offline(Int32 AssessmentId) in A:\Temp\AppName.Web\Areas\Admin\Controllers\AdminController.cs:line 3258at lambda_method(Closure , ControllerBase , Object[] )at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState)at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d()at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult)at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult)at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult)at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<>c__DisplayClass2b.<BeginInvokeAction>b__1c()at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult)

Environment:

MVC, IIS, SQL, MDB Access File

Resolution:

Since The ‘Microsoft.ACE.OLEDB.12.0’ provider is not installed on web server, you won’t be able to use this dll. Instead, you should be using “Microsoft.Jet.OLEDB.4.0” provider instead. This is already preinstalled on IIS Web Server.

Step-by-step instructions:-

Change from: Microsoft.ACE.OLEDB.4.0

            OleDbConnection myConn;
            myConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" + targetPath +"\\"+ myFileName);
            myConn.Open();            
            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = myConn;

To this: Microsoft.Jet.OLEDB.4.0 

            OleDbConnection myConn;
            myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + targetPath +"\\"+ myFileName);
            myConn.Open();            
            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = myConn;