Installation for SQL Server 2005 has been made simpler and
more secure. Months back, when I was working with the evaluation edition of
SQL Server 2005, it did not take much time to find the first major change SQL
Server 2005. It was related to the default login account SA.
This text is for all who wondered where the default account SA has gone. After you have installed SQL 2005, you will be surprised to know that, by default, SA account is disabled [if at the installation time, windows authentication mode is selected]. To be specific, while installing SQL Server 2005, you can select the authentication mode. But first time after installation, you need to connect to the server using the windows authentication mode, if the installation is done using the windows authentication. If you try to connect with SA, an error will be shown.
It is an ongoing debate, whether to use SA account or not for the applications but it is not a concern for this text. I will go through the steps that are required to enable the SA account for the server.
Windows Authentication Mode
When connecting to the SQL Server, the windows login details are used for authentication. This is the preferred mode of authentication. Using this mode while installing SQL Server 2005 will be keeping SA disabled.
Mixed Mode (Windows and SQL Server Authentication)
Allows both windows and SQL Server logins to connect to the Server. Using this mode
while installing SQL Server 2005 will enable SA.
To use SA account, follow the following steps:
- Login with windows authentication on the server.
- Right click the server name and select Properties [Figure1]
- Select Security page and check the SQL Server and Windows Authentication mode option.
- Select OK. This will enable the mixed mode authentication. Now, for enabling the SA account, open the Logins option under the Security option for the server. Select SA and right click on it and select Properties. [Figure 3]
- Window pops up [Figure 4]. Select the Enabled option.
- SA is enabled and ready to use.
Strong passwords are also introduced in SQL 2005. Earlier, it was possible to give the blank password or password same as the user-name. Now, giving passwords same as the account name or blank
password is not allowed.