SQL Server Authentication Modes: Back To Basics

  • Article

Note: All screenshots applied for SQL Server 2012 Enterprise Evaluation version

Introduction
Although this is very basic article regarding authentication modes but it is very helpful to fresher DBAs. I always have a thought in mind when I wrote an article and thought is "an article can be easily grasp by all whether they are experienced DBAs or fresher DBAs". So with this thought let's move ahead and start the topic.

What is an Authentication?
Authentication is a process in which we need credentials, in other words username and password required, 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 password required, to access the SQL Server which is clearly visible in Figure 3.


Figure 3: Illustrating Authentication

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 password 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 that 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/Mixed Mode 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 authentication (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? It is happened 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

Step 5
Check authentication mode with below query:

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 15: **Checking Authentication mode with query

Step 6
Another way of checking authentication mode using xp_loginconfig


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

**Open run box using Windows+R key. 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 for Windows authentication.
2 for Mixed mode/SQL Server authentication.

**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

Conclusion
This is all about SQL Server Authentication Modes, I hope every newbie will enjoy and take benefit from this. Your feedback and comments are welcome so that I can improve myself in future and serve you more better articles.