AD failure DevOps db not accessible via SSMS

Question

A lightning strike killed our ISP including our rack of servers. Our AD and Backup servers got it bad.

For speed I just rebuilt AD from scratch, rejoined fixable servers, and I was able to get our DevOps 2019 vm working. Programmers can connect to our projects fine.

The issue is, I cant connect direct via SSMS to the Collection dbs any longer.

The SQL UIDs are the same but the SIDs have obviously changed. sa wasn't ever enabled by DevOps.

Looking at a new install, DevOps 2020 seems to make NT AUTHORITY\NETWORK SERVICE the db owner.

I loaded up a DevOps 2020 instance, but moving the Collection db how MS recommends fails because I still can't authenticate to the Collection db in SQL Express.

The Azure DevOps 2019 Server Administration Console can still make changes inside the db.

Can someone please recommend a few options?

~Thanks

namtrahc · Answer

https://live.sysinternals.com/PsExec64.exe

Below launched SSMS as NT AUTHORITY\SYSTEM so I could modify the SQL users.

PsExec64.exe -s -i "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"

Removed the old domain users and added them again fixed it.

https://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

Forum Discussion

AD failure DevOps db not accessible via SSMS

Share

Resources