M.App Enterprise Discussions

Discuss topics with other M.App Enterprise Product pioneers and experts to get the most out of it.
Showing results for 
Search instead for 
Do you mean 
Reply
Highlighted
Frequent Contributor
Posts: 123
Registered: ‎07-25-2018

Always Encrypted Encryption in MApp enterprise

Hello Community,

 

 

Does MApp enterprise has the client driver that supports always encrypted encryption?

What would be the data provider used by MApp enterprise to connect to SQL server.

 

 

I tried editing the connection string in the database to add 'column encryption setting= enabled' to associate workflow form to encrypted columns , but I am getting some erros.

 

 

If anybody has ever done the always encrypted on MApp enterprise,I would appreciate if you could suggest the ways to implement it .

 

 

Regards,

Sreedevi

 

 

Highlighted
Staff
Posts: 1,055
Registered: ‎10-18-2015

Re: Always Encrypted Encryption in MApp enterprise

hi Sreedevi,

 

it woud be beneficial if you post the details of the errors.

 

M.App Enterprise gets connected to the DB using native .NET components, so it supports Always Encrypted mechanism. What I'm not sure about is if there is anything else that must be considered working in this environment.

 

Is this a strong requirement in your use case? Since the connection is done at the server level I've never encounter such a requirement so far...

 

Stefano

Stefano Turcato
Presale Engineer
Hexagon Geospatial
Highlighted
Frequent Contributor
Posts: 123
Registered: ‎07-25-2018

Re: Always Encrypted Encryption in MApp enterprise

Hi Stefano,

 

Yes. It is a strong requirement.

 

Error:

exec sp_executesql N'INSERT INTO MAPP_LOGMESSAGE (Id, LOGMESSAGE, Stacktrace, LOGTIME) VALUES (@Id, @LOGMESSAGE, @Stacktrace, @LOGTIME); SELECT scope_identity();',N'@Id nvarchar(36),@LOGMESSAGE nvarchar(511),@Stacktrace nvarchar(max) ,@LOGTIME datetime',@Id=N'2dd289d8-8be5-4559-b0ee-cf13e34c3361',@LOGMESSAGE=N'Operand type clash: nvarchar is incompatible with nvarchar(3) encrypted with (encryption_type = ''DETERMINISTIC'', encryption_algorithm_name = ''AEAD_AES_256_CBC_HMAC_SHA_256'', column_encryption_key_name = ''CEK_Auto1'', column_encryption_key_database_name = ''DC'')
Incorrect parameter encryption metadata was received from the client. The error occurred during the invocation of the batch and therefore the client can refresh the parameter encryption metadata by calling sp_describe_parameter_encryption and retry.',@Stacktrace=N'System.Data.SqlClient.SqlException: Operand type clash: nvarchar is incompatible with nvarchar(3) encrypted with (encryption_type = ''DETERMINISTIC'', encryption_algorithm_name = ''AEAD_AES_256_CBC_HMAC_SHA_256'', column_encryption_key_name = ''CEK_Auto1'', column_encryption_key_database_name = ''DC4'')
Incorrect parameter encryption metadata was received from the client. The error occurred during the invocation of the batch and therefore the client can refresh the parameter encryption metadata by calling sp_describe_parameter_encryption and retry.
[Details]
HelpLink.ProdName: Microsoft SQL Server
HelpLink.ProdVer: 14.00.2002
HelpLink.EvtSrc: MSSQLServer
HelpLink.EvtID: 206
HelpLink.BaseHelpUrl: http://go.microsoft.com/fwlink
HelpLink.LinkId: 20476
Href: http://localhost/Workflows/Form/save?workflow=EditWorkFlow&lang=en-GB&sessionid=8907a0a8-d809-4c41-be92-a01f829a202a&tenant=DC4_Tenant&nodeid=CreateWorkflow
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at MApp.Data.SqlServerProvider.Insert(String tableName, IEnumerable`1 parameters, DbConnection connection, DbTransaction transaction, String autoIncrementColumn, String sequenceName)
at MApp.Data.Database.Insert(DomainObject domainObject, DbConnection connection, DbTransaction transaction)
at MApp.Data.Database.Insert[T](T value, DbConnection connection, DbTransaction transaction)
at MApp.Data.DatabaseContext.InsertOrUpdate[T](T value)
at MApp.Workflows.FormController.Save(DomainObject domainObject)
at lambda_method(Closure , ControllerBase , Object[] )
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c.<BeginInvokeSynchronousActionMethod>b__9_0(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__11_0()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass11_1.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass11_1.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass11_1.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass11_1.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass11_1.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass11_1.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult)
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass3_6.<BeginInvokeAction>b__3()
at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass3_1.<BeginInvokeAction>b__5(IAsyncResult asyncResult)
',@LOGTIME='2019-01-09 08:40:17.400'

 

 

Regards,

Sreedevi

Highlighted
Frequent Contributor
Posts: 123
Registered: ‎07-25-2018

Re: Always Encrypted Encryption in MApp enterprise

Hi Stefano,

 

The encryption key is stored in windows certificate store.

Both M App enterprise and sql server are installed in same machine.

Will Mapp enterprise client have access to the windows certificate store?

 

Also the  application must use SqlParameter objects when passing plaintext data to the server with Always Encrypted column.

I would like to know if MApp enterprise uses sqlparameter to pass data.

 

 

Regards,

Sreedevi

Highlighted
Staff
Posts: 1,055
Registered: ‎10-18-2015

Re: Always Encrypted Encryption in MApp enterprise

you have to check the warehouse user is trusted in the certificate store.

 

MAE does use SqlParameter objects.

 

 

Stefano Turcato
Presale Engineer
Hexagon Geospatial
Highlighted
Frequent Contributor
Posts: 123
Registered: ‎07-25-2018

Re: Always Encrypted Encryption in MApp enterprise

Hi,

 

 

Did you mean the certificate used for MAE installation in IIS?

 

Its not present in Trusted root certification authorities.

Its found under  Intermediate certification authorities.

 

Would this make any difference?

 

Regards,

Sreedevi

 

Highlighted
Staff
Posts: 1,055
Registered: ‎10-18-2015

Re: Always Encrypted Encryption in MApp enterprise

no, the app itself on the server side must be able to access the secret key in the certificate store. The app pool identity should be set using credentials provided for "Warehouse User" while running the configuration wizard.

Stefano Turcato
Presale Engineer
Hexagon Geospatial
Highlighted
Frequent Contributor
Posts: 123
Registered: ‎07-25-2018

Re: Always Encrypted Encryption in MApp enterprise

Hi,

 

 

Both app pool identity credentials and M App service credentials are same.

 

I am able to run the workflow and get data saved into the table without encryption.

 

Regards,

Sreedevi

 

Highlighted
Staff
Posts: 1,055
Registered: ‎10-18-2015

Re: Always Encrypted Encryption in MApp enterprise

can you please check if this post is relevant?

 

https://stackoverflow.com/questions/40266502/operand-type-clash-varchar-is-incompatible-with-varchar...

Stefano Turcato
Presale Engineer
Hexagon Geospatial