Basics of Database Administration in SQL Server: Part 2

In this article, you will learn the basics of Database Administration in SQL Server. Learn SQL Server Authentication Modes.

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

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.
 
SQL Server Management Studio 
Figure 1: 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.

    server type in SQL Server
    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.
 
Windows Authentication in SQL Server 
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:
 
Windows Authentication in SQL Server 
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:
  1. Use Master  
  2. GO  
  3. SELECT   
  4. CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  
  5. WHEN 0 THEN 'Mixed Mode - Allows Both SQL Server or Windows Authentication Mode'   
  6. WHEN 1 THEN 'Allows Only Windows Authentication Mode'   
  7. END AS [Current Authentication Mode]  
  8. GO  
Authentication Mode  
Figure 5: Current Authentication Mode Output
 
Authentication Mode in SQL Server 
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.
 
Creating new login in SQL Server 
Figure 7: Creating a new login from security tab (Step 1)
 
Mixed Mode Authentication 
Figure 8: Choosing SQL Server Authentication/Mixed Mode Authentication for new login “yashrox” (Step 2)
 
yashrox 
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.
 
SQL Server Authentication mode 
Figure 10: Trying to login with SQL Server Authentication (Mixed Mode)
 
SQL Server Authentication error 
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 Method

 
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:
 
enable Mixed Mode/SQL Server Authentication mode 
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.
 
Enabling Mixed Mode Authentication 
Figure 13: Enabling Mixed Mode Authentication
 
Step 4
 
Restart SQL Server and try to login with SQL Server Authentication.
 
Restarting SQL Server after Enabling Mixed Mode Authentication 
Figure 14: Restarting SQL Server after Enabling Mixed Mode Authentication
 
Checking Authentication mode with query 
Figure 15: Checking Authentication mode with query
 
Checking Authentication mode with other 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 Method

 
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.
 
Enabling Mixed Mode Authentication with Registry 
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: