How to Enable SQL Server Authentication and Windows Authentication for SQL Server Databases

by Updated August 24, 2010

Here's how you can enable SQL Server Authentication in a SQL Server 2008 database, and then add a User login and password account for connecting to the database.

  1. Open SQL Server Management Studio
  2. In the Object Explorer sidebar, right-click on the top SQL Server node, then click Properties.
  3. In the Server Properties pop-up box, click on Security, then in the Server authentication section select "SQL Server and Windows Authentication mode" and click OK.
  4. Now in the Object Explorer, open the Security folder node, then right-click on the Logins folder, and select New Login.
  5. In the New - Login popup, enter in the Login name you want to create, then select SQL Server authentication.  Type in Password and Confirm Password  (Note: the password should have letters and numbers).  Then uncheck Enforce password expiration and uncheck User must change password at next login.  Then change the Default database drop-down box to the database you want to use.
  6. Then on the left-hand side click User Mapping.  In the Users mapped to this login: section check the Map checkbox next to the database you want the user mapped to.  Then in the Database role membership section, check db_owner. ("dbo" should be added to the Default Scheme automatically for the user login once saved).  Click OK to save changes. 
  7. The new User/login should be added to the database.  To check to make sure, go the Object Explorer, locate the database and click on the plus sign to open up the node, then open the Security folder node, and open the Users folder, you should see the User you just created is in the list of Users for the database.
  8. Now we need to assign the user to the "db_owner" scheme and role membership. Right-click on the User name, then in the Database User popup box you will want to make sure that "db_owner" is checked in both Schemes owned by this user and Database role membership.  The click OK.

Now you should be able to use both a Windows Authentication connection string to connect to your database like this:

connectionString="data source=localhost; Initial Catalog=MyAwesomeDatabase; Integrated Security=True;" 

Or use a SQL Server Authentication connection string like this:

connectionString="data source=MyServerAddress;Initial Catalog=MyDataBase;User Id=myUsername;Password=myPassword;"

 

Also see: How to Enable Remote Connection to SQL Server 2008 Express Database

 


0
2

2 Comments

anonymous by Julian on 8/24/2010
Thank you Doug, that was very clear.
anonymous by frenky sinaga on 2/23/2012
excellent..
i have been looking for this step over three days,
thanx Doug Kennard

Add your comment

by Anonymous - Already have an account? Login now!
Your Name:  

Comment:  
Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.

Related Posts


When using SQL Server 2008, you may receive a Save (Not Permitted Dialog) box pop-up when trying to save changes to an existing table. The dailog box reads: "Saving changes is not permitted. The changes you have made require the following tables to be...  more »

Here's a step by step process to enable a remote connection to your SQL Server 2008 Express database that is configured on Windows Server 2008 using an assigned port #. 1) First make sure the SQL Server Authentication is enabled and the User name that...  more »

Are you backing up your SQL Server 2008 databases daily? You should be, especially if you don't want to lose any of your precious data that you're storing. It's incredibly easy to setup a maintenance plan in SQL Server 2008 to automatically back up one or...  more »

Here's one way to setup your connection string for a SQL Server 2008 Express database for a live website using ASP.NET on Windows Server 2008. Open "Sql Server Configuration Manager" -> the click on "SQL Server Network Configuration" -> then click...  more »