Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Making SA Work

By Vasant Raj,

Introduction

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.

Authentication Modes

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.

Enabling SA

To use SA account, follow the following steps:
  1. Login with windows authentication on the server.
  2. Right click the server name and select Properties [Figure1]


    (Figure 1)

  3. Select Security page and check the SQL Server and Windows Authentication mode option.


    (Figure2)

  4. 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]


    (Figure 3)

  5. Window pops up [Figure 4]. Select the Enabled option.


    (Figure 4)

  6. SA is enabled and ready to use.

Conclusion

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.

Total article views: 4107 | Views in the last 30 days: 4
 
Related Articles
FORUM

Installation - SQL Server Authentication for SSIS

Installation - SQL Server Authentication for SSIS

FORUM

Windows Authentication

Setting up Windows Authentication to access SQL SERVER 2005 DB

FORUM

Windows account

describe a windows account

FORUM

restrict windows authentication login

restrict windows authentication login

Tags
security    
sql server 2005    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones