Windows Authentication to Sql Server Authentication

Question

I have an existing SQL Server 2005, currently I am using in Windows Authentication and Db name is Restaurant. I know how to change existing SQL Server from Windows Auth to SQL Server Auth, but it doesnt locks the DB. Is there any way I can change my current SQL Server to SQL Server Authentication so as if any one tries to open Restaurant(DB) it'l ask for authentication. And also please tell me what needs to be connection string.

Thanks

Answer 1

SQL Server recognizes two modes of security:

1. Windows Authentication
2. SQL Server AND Windows Authentication

If you want to enable SQL Server Authentication on already installed database to allow custom username and password then you have to -

1. Enable SQL Server Authentication
2. Create a new user account OR use existing sa user account
3. Set account password

Enable SQL Server Authentication Mode -

1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To Create New Login ID - Refer Create a Login

Disable Windows Authentication Login for BUILTIN Accounts -

Once you are logged in as sa (system administrator) with password (remember password as you are going to disable Windows Authentication for Default Logins), follow below steps -

1. Open SQL Server Management Studio
2. Expand the nodes to reveal your SQL Server instance
3. Expand the Security node and expand Logins
4. Right-click the BUILTIN\Administraors OR BUILTIN\Users and select Properties
5. Goto Status from left pane.
6. Check Disabled under Login

No one from the Administrators group will be able to access your SQL Server now.

Referred MSDN Thread.

EDIT:

To Use Existing Login account sa -

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO

Connection String -

Server=SERVER\SERVER_INSTANCE;Database=Restaurant;User Id=sa;
Password=password;

Refer - Connection Strings for Sql Server 2005

I would recommend to create a new account other than sa and set its password.