SQL Server Authentication

In the previous chapter, you learned about creating a login using Windows user account. Here, you will learn to create a login using SQL Server user.

At the time of installing SQL Server, if you select Mixed Mode, then you must provide a password for the built-in System Administrator or sa account. It is highly recommended to create a strong password for the sa account; otherwise disable this account as it is mapped to the sysadmin server role and has administrative rights on the whole server. Hence it is vulnerable to attack by hackers.

Create a New Login with SQL Server Authentication

The login created using SQL Server authentication mode is independent of Windows user accounts. Login and password are created and stored in the syslogins table in the master database. Passwords are not stored as plain text.

To create a new login using SQL Server authentication, follow the steps:

Open SQL Server Management Studio. In the object explorer, expand the Security node. and right-click on the Logins node and select New Login.

In the Login – New window, enter a new user name. Select SQL Server authentication radio button, enter a password, and re-enter the same password in the confirm password field, as shown below.

Select three optional password policies:

• Enforce password policy: The Windows password policies of the computer are enforced for SQL Server Logins.
• Enforce password expiration: The maximum password age policy of the computer is enforced.
• User must change password at next login: If this option is selected, the user is required to change their SQL Server login password the next time they login.

Select the Server Roles tab. In the new window, assign a server-level role to the login being created. By default, public is selected. You can add any role to the login by selecting the checkbox next to the role. In the figure below, sysadmin is selected along with public. With sysadmin server role, user can perform any activity on the server and has full control.

Click on User mapping tab. You can create a user to a database while creating a new login under user mapping tab. In the new window, a list of all databases in the server instance is displayed. Select the database(s) for which you want to map the login. You can select multiple databases. Just select the check box next to the database name. The login name is now displayed in the User column, next to the selected database, as shown below.

Click on the Securables tab. In the new window, select the Search button. In the pop-up, select The Server <Your Server name> and click OK. This is an optional step. You may skip this step and set the permissions later.

Finally, check the status screen. Grant and Enabled should be selected.

Click Ok to create the new login. This will add a new SQL Server login under the Logins node, as shown below.

You can now login to SQL Server SSMS with the new login:

Advantages of SQL Server Authentication:

• Allows SQL Server to support older applications and applications built on mixed OS.
• Allows access to web-based applications where users create their own identities.
• Allows users to connect from unknown domains.

Disadvantages of SQL Server Authentication

• Users using Windows must provide an additional set of login/password to connect to SQL Server.
• SQL Server authentication cannot use Kerberos security protocol.
• Windows offers additional password policies that are not available for SQL Server logins.
• For many applications, login and password have to be passed over the network during connection to the database server. Though encrypted, these are vulnerable to attacks.
• Some applications store the encrypted password in the client system. This is an added security risk.

Trouble shoot

If you get an error while using a new SQL Server login, then check the following:

1. Enable Mixed mode authentication (SQL Server authentication & Windows authentication) in SQL Server instance property.

Open Management Studio, right-click on the SQL Server instance & click properties. Go to Security tab.

Under Server Authentication, check SQL Server and Windows Authentication Mode radio button, as shown below.

2. Check if TCP/IP protocol is enabled. Restart SQL Server.

3. Sometimes having the password expiration check box selected gives an error when you try to login with the new login. You can try creating a login with Password Expiration unchecked.