In this step by step SQL Tutorial I will show you how to Add a new user login to SQL Server with SQL Server Authentication mode. For the purpose of this tutorial I will use SQL Server 2012.
Prior knowledge: You should have some knowledge about security and difference between windows authentication vs SQL Server Authentication. We plan on writing blog posts about it and once we have it we will provide links in this tutorial. (If we forget use the search bar at the top).
Once SSMS is open I go to Object Explorer, Security, Logins folder.
NOTE: There are two kinds of Security folders. Database level Security folder that exists in specific database under Databases Folder that we won't be using and Server Level Security folder that is located directly below "Databases" folder (see below).
To Add new user login right click Logins folder and click New Login...
In the Login window I select SQL Server authentication.
I type Login name and password and press OK.
I get error message saying "Password validation failed. The password does not meet Windows policy requirements because it is not complex enough".
NOTE: Password should be strong and there are a number of conditions you need to meet. One of them might be the need to use special characters like $. Avoid using generic passwords like Pa$$w0rd that unfortunatelly is used way too often! but when you don't have password management software or processes that often seems to be easiest option for some people...... until something bad happens ;). This practice is sometimes forgivable if you use it locally or on development box and the database is not exposed externally and in our case we will use "weak" password.
To create "weak" password and avoid error message I untick Enforce password policy and click ok
NOTE: Very often you will set some kind of permissions under Server Roles (data reader is popular), User Mapping or Securables. Once we write more tutorial we will provide links with more information (if we forget use the search bar).
Now my new login is added and visible in the Logins folder.
You can now try to connect to the server using SQL Server Authentication method and give it to users that require remote connection.
Problems with logging in are often related error 18456 for more information visit SQL Server Error 18456