Thursday, May 03, 2007

Sql Server 2005 - How to change sqlserver authentication mode - windows authentication / sql server and windows authentication mode

Sql Server 2005 - How to change sqlserver authentication mode - windows authentication / sql server and windows authentication mode:

When we install Sql server 2005 during the installation it would prompt us for what kind of authentication mode to use (windows authentication or mixed mode[both sql and windows]), but at any point of time we can change this (similar to how we do it in sql2000) but with the new sql server management studio, the way we do it has slightly changed, this blog entry explains how to do the same.

1) Launch Sql Server Management Studio (SSMS), usually found in start > program files > Microsoft sql server 2005 > Sql Server Management Studio,

2) SSMS is nothing but a single IDE which combines both sql server 2000 enterprise manager and sql server 2000 query analyzer.

3) Since the mode is right now windows authentication, select the server type as "database engine" and then server name: the name of the sql server 2005 instance you are trying to connect to and authentication as "windows authentication" and click connect,

Note: If sql server 2005 is not yet started you may get an error msg (but in sql 2000 it used to start the sql server if it was not yet started but in 2005 it does not start it automatically).

4) Now the ssms will be connected, start the object explorer, several ways to start it, easiest is to press F8 or click on the icon in the menu which has the tool tip object explorer, (3rd from the right), object explorer is nothing but an tree view of all existing sql server instances, the databases and other agents (similar to sql 2000 enterprise manager left hand tree).

5) Right click on the sql server 2005 instance name and select properties to open the server properties popup window, in this window click on "security page" in the left hand side tree menu, and this page shows the server authentication, change it from windows authentication to sql server and windows authentication.

6) Now we need to perform one more additional step, configuring "sa" login or some other login and enabling it.

7) In the object explorer (F8) expand security, click on logins, the existing logins are shown in the right hand side, right click "sa" the default system administration login and select properties to show the properties window for "sa" login, here change the password of the login.

8) The main step is click on status page in the left hand side and change the status of the login to enabled (if not you will not be able to connect back using sa login)

Thus you can change the authentication modes in sql server 2005, but this may require a restart of the instance.

After restarting sql server 2005 try launching ssms and this time select mode as "sql server authentication" and enter login - "sa" and the password which you had changed in previous step and click "connect" and it should log you in.

Tags: , ,

No comments: