Thursday, December 12, 2024

Troubleshoot missing rights for Skype for Business SQL DBs

Abstract: If you setup a new Skype for Business persistent chat service (or other Skype for Business / Lync 2013 services) the required DBs miss the needed rights.

If you setup a new Skype for Business persistent chat service (or other Skype for Business / Lync 2013 services) you might find out that the required SQL DB is created by the topology builder, however if you check the permission, none of the Skype for Business / Lync groups (ROOT\RTCComponentUniversalServices;ROOT\RTCHSUniversalServices;ROOT\RTCUniversalConfigReplicator;ROOT\RTCUniversalReadOnlyAdmins;ROOT\RTCUniversalServerAdmins;…) do have rights on the new DBs.

You will see the following (similar) error on the SQL Server:

Login failed for user 'CONTOSO\LYPERC01$'. Reason: Failed to open the explicitly specified database 'mgc'.

and the following (similar) on your Skype for Business / Lync Server:

Skype for Business Server 2015, Persistent Chat could not start due to the following exception:
 at
Microsoft.Rtc.Internal.Chat.Server.ServerCommon.Exceptions.StopServerException: CmdID: 0ccb0928-9c6a-4ea9-a7c3-1ccab48ba81f The server could not restore db connection within the allowed time (00:10:00) using connection string: Data Source=sql.contoso.local;Initial Catalog=mgc;Integrated Security=SSPI. ---> System.Data.SqlClient.SqlException: Cannot open database "mgc" requested by the login. The login failed.
Login failed for user 'CONTOSO\LYPERC01$'.
   at System.Data.SqlClient.SqlInternalConnection.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.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
   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 Microsoft.Rtc.Internal.Chat.Server.ServerCommon.Database.DbCommand.prepareWithRetry(RetryInfo retryInfo)
   --- End of inner exception stack trace ---
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.Database.DbCommand.prepareWithRetry(RetryInfo retryInfo)
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.Database.DbCommand.prepare(RetryInfo retryInfo)
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.Database.DbCommand.executeUntilSuccessOrTimeout[TR](Fun`2 executeDelegate, RetryInfo retryInfo)
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.Database.DbCommand.executeImp[TR](Fun`2 executeDelegate, Int32 retryTimeoutInMs)
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.Database.DbCommand.ExecuteScalar(Int32 retryTimeoutInMs)
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.Database.SqlContext.ExistsInDatabase(String databaseObjectName)
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.Database.DataAccess.SetInstance(IRepositoryContext repositoryContext)
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.ServerServiceLocator.<loadLocateStrategies>b__1()
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.ServerServiceLocator.GetThe[T]()
   at Microsoft.Rtc.Internal.Chat.Server.Channel.Server.ChannelServer.DatabaseIsExpectedVersion()
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.TransportServerBase.Initialize()
   at Microsoft.Rtc.Internal.Chat.Server.Channel.Server.ChannelServer.Initialize()
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.MgcServiceBase.startServer()
   at Microsoft.Rtc.Internal.Chat.Server.ServerCommon.MgcServiceBase.createAndStartServer().

To fix that you can try to re-initialize the DBs via Install-CsDatabase.

Example:

Install-CsDatabase -ConfiguredDatabases -SqlServerFqdn sqlsrv.contoso.local -DatabasePaths “G:\T-Logs\SkypeForBusiness”,”H:\Databases\SkypeForBusiness” -update

Which will try to re-set the needed rights. If you do that you might see the following error in the powershell console:

Setting owner for database mgc to sa.
Failed to setup database mgc due to an unexpected error. Detail: Microsoft.SqlServer.Management.Smo.FailedOperationException: Set owner failed for Database 'mgc '.  ---> Microsoft.SqlServer.Management.Smo.SmoException: An exception occurred in SMO. ---> System.ArgumentException: The login 'sa' does not exist on this server.
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.Database.SetOwnerImpl(String loginname, Boolean overrideIfAlreadyUser)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.Database.SetOwnerImpl(String loginname, Boolean overrideIfAlreadyUser)
   at Microsoft.Rtc.Common.Data.DatabaseManager.GrantSqlLoginsAndDbAccess(String owner, String[] accountsToAdd)
   at Microsoft.Rtc.Common.Data.DbSetupBase.UpdateDbRoles()
   at Microsoft.Rtc.Common.Data.DbSetupBase.SetupDatabase(Boolean create)
   at Microsoft.Rtc.Common.Data.DbSetupBase.CreateOrUpdateDatabase()
Microsoft.SqlServer.Management.Smo.SmoException: An exception occurred in SMO. ---> System.ArgumentException: The login 'sa' does not exist on this server.
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.Database.SetOwnerImpl(String loginname, Boolean overrideIfAlreadyUser) System.ArgumentException: The login 'sa' does not exist on this server.

Database mgc is set to multi user mode.
Exception Stack:
Type: Microsoft.SqlServer.Management.Smo.FailedOperationException, Message: Set owner failed for Database 'mgc'.
Type: Microsoft.SqlServer.Management.Smo.SmoException, Message: An exception occurred in SMO.
Type: System.ArgumentException, Message: The login 'sa' does not exist on this server.

This will happen if the SA account on the SQL cluster is renamed to something else due to some SQL hardening. To fix that issue, rename the SA account back to the normal name (which is “sa”) and retry the powershell action.

 

Other useful resources:

 

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay Connected

35FollowersFollow
- Advertisement -

Latest Articles