3

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

Improve this question

2 Answers 2

Reset to default
9

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

As I have not installed SQL Server in SQL SERVER Auth, then how would get the password ?
You wrote that - ` I know how to change existing SQL Server from Windows Auth to SQL Server Auth. You have to login with Windows Authentication and enable the sa` account or create a new account and reset the password.
@user1737641 - Follow above steps.
mesharam earlier you have mentioned something about removing admin account so that SQL logins only in SQL Auth mode.<br> how ever was able to login via SQL Auth , but if i establish conection through Windows Auh was able to login, which is not wanted, so please guide.
First of all, you can not remove Windows Auth completely. Because admin users of machine on which SQL Server is installed are owner of the SQL DB. But you can access your db with Windows Auth or SQL Auth if the later one is enabled. I believe you want to use SQL Auth with already installed SQL Server. In this case you have to - (1) Enable SQL Server Authentication. (2) Create a new user account w/ pwd OR use existing sa user account. Follow these steps.
|
0 
use [master]
GO

ALTER LOGIN [username] WITH PASSWORD='yourpassword', CHECK_POLICY=OFF
GO

ALTER LOGIN [username] ENABLE
GO
Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.