Basics of Database Administration in SQL Server: Part 2

In case you have not had a look at our first article, go through the following link:

• Basics of Database Administration in SQL Server : Part 1

SQL Server Authentication Modes

What is Authentication

Authentication is a process in which we need credentials, in other words username and word, to access the SQL Server.


Figure 1: The very first screen that appears when we open SQL Server Management Studio (SSMS).

When you open SQL Server Management Studio (SSMS) for very first time you will get the following three things:

1. Server Type
2. Server Name
3. Authentication

1. Server Type

There are the following four types of servers:

1. Database Engine: Used for storing, processing and securing data.
   
2. Analysis Services: Used for Online Analytical Processing and data mining functionality.
   
3. Reporting Services: Used for creating interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources.
   
4. Integration Services: Used to do a broad range of data migration tasks. It is a platform for data integration and workflow applications.
   
   
   Figure 2: Illustrating server type in SQL Server

2. Server Name

It can be any name of server by which a server can be identified.

3. Authentication

As we already discussed, it is a process in which we need credentials, in other words username and word, to access the SQL Server that is clearly visible in Figure 3.


Figure 3: Illustrating Windows Authentication and Login

Types of Authentication in SQL Server

There are basically the following two types of authentication in SQL Server:

1. Windows authentication
2. Mixed mode Authentication/SQL Server Authentication

a. Windows Authentication

Requires a valid Windows username and word to access the SQL Server.

b. Mixed mode Authentication

A user can login either via SQL Server authentication or Windows authentication mode to connect to SQL Server.

Example of Windows Authentication Mode:


Figure 4: Illustrating Windows Authentication in SQL Server

The following describes how to check taht we are logged in with Windows Authentication Mode or Mixed Mode.

For this simply execute the following query in SSMS:

Use Master
GO
SELECT
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 0 THEN 'Mixed Mode - Allows Both SQL Server or Windows Authentication Mode'
WHEN 1 THEN 'Allows Only Windows Authentication Mode'
END AS [Current Authentication Mode]
GO

Figure 5: Current Authentication Mode Output


Figure 6: Current Authentication Mode Output

Remember

Remember that when only Windows Authentication is enabled, you can't login with SQL Server Authentication or Mixed Mode.

Let's make it clear with an example.

I have created a new login “yashrox” with SQL Authentication.


Figure 7: Creating a new login from security tab (Step 1)


Figure 8: Choosing SQL Server Authentication/Mixed Mode Authentication for new login “yashrox” (Step 2)


Figure 9: New login “yashrox” created with Mixed mode authetication (Step 3)

Now to check Mixed Mode / SQL Server Authentication we will log into SQL Server with the login "yashrox" that was created with SQL Server / Mixed Mode Authentication.


Figure 10: Trying to login with SQL Server Authentication (Mixed Mode)


Figure 11: Getting error when trying to login with SQL Server Authentication

An error occurred when we tried to login with SQL Server Authentication / Mixed Mode Authentication. Why this happened is because Mixed Mode / SQL Server Authentication is not enabled by default as we saw in Figure 5 and Figure 6 also.

Resolution

The resolution for this problem is to enable the Mixed Mode / SQL Server Authentication, so let's move ahead to enable the Mixed Mode/SQL Server Authentication.

Enabling Mixed Mode/SQL Server Authentication

There are two ways to enable Mixed Mode/SQL Server Authentication mode.

First Way

Step 1

Log into SQL Server with Windows authentication mode with the login name “XYZ\yashwant.kumar" (refer to Figure 4).

Step 2

Right-click on the Server and then click on properties as in the following:


Figure 12: Configuring SQL Server Properties for Mixed Mode Authentication

Step 3

Click on security in the left pane and select SQL Server and Windows Authentication Mode and click OK to save.


Figure 13: Enabling Mixed Mode Authentication

Step 4

Restart SQL Server and try to login with SQL Server Authentication.


Figure 14: Restarting SQL Server after Enabling Mixed Mode Authentication


Figure 15: Checking Authentication mode with query


Figure 16: Checking Authentication mode with other query

Wow! Now this time we are able to login with SQL Server Authentication / Mixed Mode and with both queries we are getting the login mode as Mixed Mode / SQL Server Authentication.

Second Way

Enabling Mixed from Regedit/Registry.

Step 1

Press the Windows key + R to open the Run box. Type regedit and press Enter.

Step 2

Navigate to the registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer.

In the right panel, change the LoginMode from 1 to 2.

1. Windows authentication Only.
2. Mixed mode.

Step 3

Restart your SQL Server instance and you can then connect to the server using SQL Server Authentication.


Figure 17: Enabling Mixed Mode Authentication with Registry

This is something about SQL Server Authentication Modes. Click here to continue with more information regarding SQL Server Authentication, to know more about SQL Server Database Administration follow below links:

• Basics of Database Administration in SQL Server: Part 3
• Basics of Database Administration in SQL Server: Part 4
• Basics of Database Administration in SQL Server: Part 5
• Basics of Database Administration in SQL Server: Part 6
  

Have a great day ahead, and keep sharing your knowledge. :)