During installation, you can set the SQL Server Database Engine to accept either Windows Authentication mode or SQL Server and Windows Authentication mode. AS a rule of thumb, people will normally use Windows Authentication mode. However, if you need to connect remotely or need to use a different user (other than the authenticated Windows user) you will run into trouble.
In this entry I discuss how to change the authentication method for a SQL Server.If you choose Windows Authentication mode during installation, then the sa login will be disabled. If you later wnat to change authentication mode to SQL Server and Windows Authentication mode, the sa login will remain disabled. In order to change the sa login states, you should follow these steps:
1. Open the SQL Server Management Studio and authenticate using the Windows Authentication mode;
2. Under the Object Explorer, right-click and choose Properties from the dropdown menu;
3. Under Select a page you must select Security;
4. Under the Server Authentication group you must choose SQL Server and Windows Authentication mode;
5. Click OK to finish.
Notice that you are required to restart the server. If you have any other service running alongside your SQL Server, you will also need to restart the service.
Finally, you will still need to enable the sa account. In order to sort that out, follow these steps:
1. Expand the server instance;
2. Expand the Security folder;
3. Expand the Logins folder;
4. Double click on the sa user (or any user you wish to enable);
5. Under Select a page, click on Status;
6. Under Login select the Enabled option
An alternative to the above is to execute the following statement:
|
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<sa_password_goes_here>';
GO
|