.Microsoft SQL Server can be accessed using logins created with Windows accounts/groups or logins created with SQL Server instance. Accessibility is determined with authentication mode set with the instance. SQL Server supports two types of authentication modes; Windows authentication and Mixed authentication modes. Windows authentication mode allows to create logins using Windows accounts or groups and allows users to login to SQL Server instance using their Windows accounts. Mixed authentication mode allows to create the same, in addition to that, it allows to create SQL Server specific logins supplying names and passwords. Only one mode can be set at a time, it can be initially set during the installation but can be changed after installation using Management Studio;
Can we change this setting only through Management Studio? No, it can be simply changed accessing the registry too. This will be useful if you cannot access the Management Studio for some reasons or you cannot access the the instance using your SQL login because the mode has been reset from Mixed to Windows but you have administrator rights to the server.
Here is the ways of accessing the registry node and changing the mode.
- Open the registry using regedit.exe.
- Go to HKEY_LOCAL_MACHINE -> SOFTWARE -> Microsoft -> Microsoft SQL Server -> Instance Name -> MSSQLServer.
- You should see a value called LoginMode. You can set it to 1 for Windows authentication or can set it to 2 for Mixed authentication mode.
- Once changed, restart the SQL Server service.