I have implemented Custom data provider class library project for customizing the connection string for Azure AD Managed Identity with Authentication keyword connection string as below in web.config in CMSWebAPP as well as in .Net core webApp live site application in appsetting.json and referencing class librabry project to both Admin and Live solutions
Web.config
<add name="CMSConnectionString" connectionString="Data Source=<ServerName>;Initial Catalog=<Database>;Encrypt=True;Authentication='Active Directory Default'" />
Appsetting.json
"ConnectionStrings": {
"CMSConnectionString": "Data Source=<servername>;Initial Catalog=<Database>;Encrypt=True;Authentication='Active Directory Default'"
}
This is issue i am getting when i am trying to insert for create user from User Module in Kentico, Below is the event log stach trace for reference
EventDescription: Message: Exception of type 'System.Web.HttpUnhandledException' was thrown.
Exception type: System.Web.HttpUnhandledException
Stack trace:
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.cmsmodules_membership_pages_users_user_new_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
Message:
[DataConnection.HandleError]:
Query:
INSERT INTO CMS_User ([UserName], [FirstName], [MiddleName], [LastName], [FullName], [Email], [UserPassword], [PreferredCultureCode], [PreferredUICultureCode], [UserEnabled], [UserIsExternal], [UserPasswordFormat], [UserCreated], [LastLogon], [UserStartingAliasPath], [UserGUID], [UserLastModified], [UserLastLogonInfo], [UserIsHidden], [UserIsDomain], [UserHasAllowedCultures], [UserMFRequired], [UserPrivilegeLevel], [UserSecurityStamp], [UserMFSecret], [UserMFTimestep])
VALUES (@UserName, @FirstName, @MiddleName, @LastName, @FullName, @Email, @UserPassword, @PreferredCultureCode, @PreferredUICultureCode, @UserEnabled, @UserIsExternal, @UserPasswordFormat, @UserCreated, @LastLogon, @UserStartingAliasPath, @UserGUID, @UserLastModified, @UserLastLogonInfo, @UserIsHidden, @UserIsDomain, @UserHasAllowedCultures, @UserMFRequired, @UserPrivilegeLevel, @UserSecurityStamp, @UserMFSecret, @UserMFTimestep);
SELECT SCOPE_IDENTITY() AS [ID]
Caused exception:
Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
Exception type: System.Exception
Stack trace:
at CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex)
at CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction)
at CMS.DataEngine.GeneralConnection.RunQuery(QueryParameters query)
at CMS.DataEngine.SqlExecutionHelper.ExecuteWithRetryCore[TResult](Func`1 executionFunc, Int32 retryCount, Func`2 exceptionRetryPredicate)
at CMS.DataEngine.SqlExecutionHelper.ExecuteWithDeadlockRetryInternal[TResult](Func`1 executionFunc, Int32 retryCount)
at CMS.DataEngine.GeneralConnection.ExecuteQuery(QueryParameters query, Int32& totalRecords)
at CMS.DataEngine.DataQueryBase`1.GetDataFromDBInternal()
at CMS.DataEngine.DataQueryBase`1.GetDataFromDB()
at CMS.DataEngine.DataQueryBase`1.GetData()
at CMS.DataEngine.DataQueryBase`1.Execute()
at CMS.DataEngine.DataClassDataSqlStore.Insert(IDataClass dataClass, Boolean initializeId)
at CMS.DataEngine.AbstractInfoBase`1.InsertData()
at CMS.DataEngine.AbstractInfoProvider`3.SetInfo(TInfo info)
at CMS.Membership.UserInfoProvider.SetUserInfoInternal(UserInfo user)
at CMS.Membership.UserInfoProvider.SetPassword(UserInfo ui, String userPassword, Boolean saveObject)
at CMSModules_Membership_Pages_Users_User_New.SaveNewUser() in C:\Users\mitla\Desktop\Kentico\CMS\CMSModules\Membership\Pages\Users\User_New.aspx.cs:line 215
at CMSModules_Membership_Pages_Users_User_New.btnSave_Click(Object sender, EventArgs e) in C:\Users\mitla\Desktop\Kentico\CMS\CMSModules\Membership\Pages\Users\User_New.aspx.cs:line 127
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at CMS.FormEngine.Web.UI.FormSubmitButton.b__17_0(Object s, EventArgs args)
at CMS.Base.AbstractHandler.CallEventHandler[TArgs](EventHandler`1 h, TArgs e)
at CMS.Base.AbstractHandler.Raise[TArgs](String partName, List`1 list, TArgs e, Boolean important)
at CMS.Base.SimpleHandler`2.RaiseExecute(TArgs e)
at CMS.Base.SimpleHandler`2.StartEvent(TArgs e)
at CMS.Base.EventList.RaiseEvent(Object sender, EventArgs e, String eventName, String actionName)
at CMS.Base.Web.UI.HeaderActions.RaiseActionPerformed(Object sender, CommandEventArgs e)
at CMSAdminControls_UI_PageElements_HeaderActions.RaisePostBackEvent(String eventArgument) in C:\Users\mitla\Desktop\Kentico\CMS\CMSAdminControls\UI\PageElements\HeaderActions.ascx.cs:line 162
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Message: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
Exception type: Microsoft.Data.SqlClient.SqlException
Stack trace:
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 2363
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1835
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 0
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDataReader.cs:line 1349
at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDataReader.cs:line 281
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 5787
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 5555
at Microsoft.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) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 5159
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 5011
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 2507
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at CMS.DataEngine.AbstractDataConnection.ExecuteQuery(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, Boolean requiresTransaction)