Skype for Business Mirroring setup fails with “An exception occurred while executing a Transact-SQL statement or batch”

Error setting up mirroring or witness for database rtcxds: Microsoft.Rtc.Management.Deployment.MirrorDatabaseException: Cannot set up witness for database “rtcxds”. Exception: Microsoft.SqlServer.Management.Smo.FailedOperationException: Alter failed for Database ‘rtcxds’. —> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. —> System.Data.SqlClient.SqlException: The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://CDCSQL01:7022’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

— End of inner exception stack trace —

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)

at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)

at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)

at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingPreferences sp)

at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()

— End of inner exception stack trace —

While investigating I have found that Primary and Secondary SQL Server where running with AES encryption but SQL Express Witness with RC2 and that is why Deployment wizard was giving error.

Steps I have taken to resolve this issue…

Match the SQL Database encryption with following query

select * from sys.database_mirroring_endpoints

select * from sys.sysusers

select * from sys.server_principals

select * from sys.symmetric_keys

select * from sys.certificates

in the results you need to check Column encryption_algorithm_desc the AES version on SQL Primary & Secondary

Modify the Security DB with your running encryption I would suggest to modify on Witness node since there would be minimal/no impact or modify on your own risk.

Make sure ROLE=Witness updated here..

USE [master]GO
ALTER ENDPOINT [mirroring_endpoint]  STATE=STARTED AS TCP (LISTENER_PORT = 7022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = Witness, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES)GO

One above steps completed, I was able to configure and deploy Sql 2012 mirroring for Skype for Business Server 2015.