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

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