Unable to switch Database config to SQL account.

Unable to switch Database config to SQL account.

book

Article ID: 245490

calendar_today

Updated On:

Products

IT Management Suite

Issue/Introduction

When running through the SIM database configuration and changing Database Configuration, you are attempting to change the current Application Identity account to use a SQL Authentication. When it gets to Verifying database connection you get an error:

Failed to impersonate user 'example\svc-itmsappid' for database validation. Try again or contact support if this problem persists.

Message 1:

Connection closed: Data Source=sqlserver-01;Initial Catalog=master;Integrated Security=False;User ID=svc-itmssql;Pooling=True;Max Pool Size=400;Connect Timeout=30;Application Name=SymantecInstallationManager.exe
-----------------------------------------------------------------------------------------------------
Date: 7/7/2022 9:41:26 AM, Tick Count: 58364671 (16:12:44.6710000), Size: 518 B
Process: SymantecInstallationManager (4828), Thread ID: 8, Module: SymantecInstallationManager.exe
Priority: 8, Source: Symantec.Installation.InstallClasses.DatabaseContext.Dispose

 

Message 2:

Exception occurred while impersonating user
Failed to open database connection.
   [Symantec.Installation.Helpers.DatabaseNotAccessibleException @ SymantecInstallationManager]
   at Symantec.Installation.Helpers.DatabaseManager.OpenConnection(String serverName, String catalog, String loginUserName, SecureString loginPassword, Boolean useIntegratedSecurity, Int32 retries)
   at Symantec.Installation.InstallClasses.SQLAuth.VerifyDBPermissions(String dbname, Boolean createNew, Int32 timeout, Boolean& bTempdbAutogrow)

Cannot open user default database. Login failed.
Login failed for user 'svc-itmssql'.
   [System.Data.SqlClient.SqlException @ .Net SqlClient Data Provider]
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   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.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Symantec.Installation.Helpers.DatabaseManager.OpenConnection(String serverName, String catalog, String loginUserName, SecureString loginPassword, Boolean useIntegratedSecurity, Int32 retries)

SQL Exception details: code=4064, line=65536

Exception logged from: 
   at Symantec.Installation.Logging.LogActivity.ReportException(Int32 severity, String strMessage, String category, Exception exception, String footer)
   at Symantec.Installation.Logging.LogActivity.ReportException(String strMessage, String category, Exception exception)
   at Symantec.Installation.InstallClasses.SQLAuth.VerifyDBPermissions(String dbname, Boolean createNew, Int32 timeout, Boolean& bTempdbAutogrow)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at Symantec.Installation.WPF.CancelProxy.Invoke(IMessage msg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Symantec.Installation.InstallClasses.SQLAuth.VerifyDBPermissions(String dbname, Boolean createNew, Int32 timeout, Boolean& bTempdbAutogrow)
   at Symantec.Installation.Model.DBSettingsManager.VerifyDatabaseSettings(SettingsToValidate settings, ServerValidationInfo& info)
   at Symantec.Installation.Model.DBSettingsManager.VerifySettings(SettingsToValidate settings, Version& existingDBVersion, Boolean& emptyDB, List`1& licenses)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at Symantec.Installation.WPF.CancelProxy.Invoke(IMessage msg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Symantec.Installation.Model.DBSettingsManager.VerifySettings(SettingsToValidate settings, Version& existingDBVersion, Boolean& emptyDB, List`1& licenses)
   at Symantec.Installation.ViewModel.DBSettingsViewModel.<>c__DisplayClass111_0.<Next>b__1()
   at System.Threading.Tasks.Task.Execute()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
   at System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecution)
   at System.Threading.ThreadPoolWorkQueue.Dispatch()


-----------------------------------------------------------------------------------------------------
Date: 7/7/2022 9:42:13 AM, Tick Count: 58412234 (16:13:32.2340000), Size: 6.65 KB
Process: SymantecInstallationManager (4828), Thread ID: 15, Module: SymantecInstallationManager.exe
Priority: 1, Source: Symantec.Installation.InstallClasses.SQLAuth.VerifyDBPermissions

 

Message 3:

VerifyDatabaseSettings - AuthenticateCredentials/VerifyDBPermissions failed: Failed to impersonate user 'example\svc-itmsappid' for database validation. Try again or contact support if this problem persists.
-----------------------------------------------------------------------------------------------------
Date: 7/7/2022 9:42:13 AM, Tick Count: 58412234 (16:13:32.2340000), Size: 520 B
Process: SymantecInstallationManager (4828), Thread ID: 15, Module: SymantecInstallationManager.exe
Priority: 1, Source: Symantec.Installation.Model.DBSettingsManager.VerifyDatabaseSettings

 

'example\svc-itmsappid' is the account used for the Application Identity and was used to install the product/create the database and not the one that the customer is trying to switch to.

Environment

ITMS 8.6, 8.7

Cause

The new account was not set properly in the SQL Server to point to a default database.

Resolution

Check under SQL Management Studio that the desired account has actually a designated default database. 

 

Usually "Master" is the default one but if it is blank, select the one for your SMP server.

Powered by Wolken Software