Thanks to Graham Brown our resident SQL server guru for this - thought it is worth sharing.
This has
happened a few times where SQL instances have been built or cloned by ‘other’
people – who then forget about who is meant to be supporting the instances … or
just go on holiday 😊
https://www.sqlshack.com/recover-lost-sa-password/
Once you have gained access yourself please remember to add all the other DBA’s in the team… you know it makes sense!
If you have done the step above and still cannot log on – check the error log
(C:\Program
Files\Microsoft SQL Server\MSSQL13.DYNAMICS_CRM_SQL\MSSQL\Log\errorlog
for example) – in the case of the instance I could see this
just after the startup :
Basically whoever has created the instance has left it at ‘Windows only authentication’ - now although I can understand this depending on the application the instance is being used for it it’s a pain when we have the lack of login issue … and without the ability to use SSMS to change it to ‘SQL Server and Windows Authentication’ we have to hack the registry :
