Consider a particular scenario where sa account gets disabled (by any reason) and windows authentication doesn’t have the rights to connect to the database server; basically windows authentication is not available. In such a case, you will not be able to do much from Sql server management studio to enable sa account again because to do that, you will have to run a query and to run the query, you need to be connected first to the Database. **Deadlock** So what to do?
There is a pretty simple hack for this. You need to edit a particular registry key property’s value. Follow the below steps to enable mixed mode authentication when you’re locked out. However, please note that this could be a potential security risk (note the word ‘hack’ mentioned earlier). Make sure your sa account has a very strong password.
- Open Run (Windows + r) and type regedit. Hit enter. Click OK.
- This opens the Registry editor. Navigate to the registry key
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer\LoginMode
Note: For those who don’t know what HKLM is, HKLM refers to HKEY_LOCAL_MACHINE
—-The text highlighted above in red can change based on what instance name you’re using.
- Change the value of LoginMode to 0x00000002 (2). The value 2 refers to Mixed Mode authentication and value 0x00000001 (1) refers to Windows Authentication. Make sure it is now set to 0x00000002.
- Restart the SQL Server and pc (if required). Now you will be able to connect to the database using sa account.
Hope this helps!