Azure AD Connect DB corrupt

ADSync SQL connection problem

I've got in a situation that our Azure AD Connect Synchonisation Server Manager was giving the status "stopped-database-connection-lost". Only we did't mention this message, because every object was synced successful.

After some problems with Azure Active Directory self-service password reset, I've decide to check the event logs.

Event logs

I found differend events errors, most of them mentioned the following error "The server encountered an error because the connection to SQL Server failed.". When I opened Azure AD Connect tool, the configuration wizard was not giving me any errors of database problems.

Event 6322, ADSync
The server encountered an error because the connection to SQL Server failed.

Event 6075, ADSync
The management agent "domain.local" failed on run profile "Delta Import" because the connection to the server database was lost.

Event 905, Directory Synchronization
Scheduler::SchedulerThreadMain : An error occured and scheduler run failed to perform all operation.
System.Management.Automation.CmdletInvocationException: A connection to SQL Server could not be established.

Event 6306, ADSync
The server encountered an unexpected error while performing an operation for the client.
"ERR_: MMS(3168): ..\sql.cpp(5618): Cannot continue the execution because the session is in the kill state.
"ERR_: MMS(3168): ..\sql.cpp(5618): Cannot continue the execution because the session is in the kill state.
ERR_: MMS(3168): ..\sql.cpp(5618): Unable to find index entry in index ID 0, of table 453576654, in database 'ADSync'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.
BAIL: MMS(3168): ..\sql.cpp(7371): 0x80004005 (Unspecified error)
BAIL: MMS(3168): ..\sproc.cpp(4585): 0x80004005 (Unspecified error)
BAIL: MMS(3168): ..\exechist.cpp(1346): 0x80004005 (Unspecified error)
BAIL: MMS(3168): ..\server.cpp(5932): 0x80004005 (Unspecified error)

Azure AD Sync 1.5.45.0"

Event 33001, PasswordResetService
TrackingId: c07e3d76-55b4-43bb-80a3-faf18213e1ca, Reason: Synchronization Engine returned an error hr=80230621, message=A connection to SQL Server could not be established.

Solution

It looks like there is something wrong with the database. In Eventlog 6303 I read that i need to check my database on corrupted tables. Eventually i had 2 consistency errors in database 'ADSync'. I have made a back-up of my DB and did a repair on the database. I will explain what i did.

1. Open powershell and to tho the SQL Binn folder
PS C:\Temp > Set-Location 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn'

2. Search for the instance pipe name of your ADSync DB
PS C:\Program Files\Microsoft SQL Server\110\Tools\Binn> .\SqlLocalDB.exe info .\ADSync
Name:               ADSync
Shared name:        ADSync
Owner:              COMPUTER\AAD_a85a9683e52b
Instance pipe name: np:\\.\pipe\LOCALDB#SH5DE36E\tsql\query

3. Connect to LocalDB ADSync
PS C:\Program Files\Microsoft SQL Server\110\Tools\Binn> .\SQLCMD.EXE -S "np:\\.\pipe\LOCALDB#SH5DE36E\tsql\query"

4. Run a CheckDB (Note: nothing will change on your DB)
1> DBCC CHECKDB(ADSync);
2> GO

I found the following error:
CHECKDB found 0 allocation errors and 2 consistency errors in table 'mms_watermark_history' (object ID 453576654).

For fixing this issue, i need to put the DB in Single user first and repair the database.

5. Put DB in Single user
1> ALTER DATABASE ADSync SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2> GO
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

6. Repair database
1> DBCC CHECKDB('ADSync', REPAIR_ALLOW_DATA_LOSS)
2> GO
Repair: The Nonclustered index successfully rebuilt for the object "dbo.mms_watermark_history, IX_mms_watermark_historytimestamp" in database "ADSync".
The error has been repaired.

7. Put back the DB in Multi user
1> ALTER DATABASE ADSync SET MULTI_USER
2> GO

Finaly you could check if the error still exist in your DB
1> DBCC CHECKDB(ADSync);
2> GO

8. Close your CLI and reboot the server.