I know not a lot of DBAs out there have to change the authentication mode after they have installed SQL Server, but do you know that if you installed SQL Server using the Windows Authentication that you change the mode without hacking the registry like we had to do in SQL Server 2000.
Changing the authentication mode is actually very easy in SQL Server 2005 and 2008. Just bring Object Explorer in SQL Server Management Studio, right-click on the server name you need to change, choose Properties, choose Security, and then change the mode. If you are changing the mode from Windows Authentication to Mixed Authentication which is the change we usually make, you will need to know a few things.
In SQL Server 2000, changing the mode required a registry key change and the only thing you need to be aware of after making the Windows Authentication to Mixed Authentication mode change was that the change left the sa account without a password. The same happens today when you change the mode in SQL Server 2005/2008 but now that you can safely disable the sa account, installing SQL Server under Windows Authentication will disable the sa account. This means that once you change the mode over to Mixed, you will not only need to give the sa account a strong password, you will also need to enable the account as well.
Note: You will need to restart SQL Server in order for the authentication mode change to take effect.
So how do you enable the sa account? A simple ALTER LOGIN will do, ALTER LOGIN will also be used to create the password as well.
ALTER LOGIN sa ENABLE
GO
ALTER LOGIN sa WITH PASSWORD = ‘VeryStrongPasswordHere’
GO
Another short and simple posting about a feature not a lot of database administrators know about. SQL Server Management Studio is slowly adding new features that should make our jobs as administrators easier and easier without having to go to the registry to make what should be simple changes.
