Microsoft SQL Server Authentication Types & Modes

#DecodeITeS
1. What is Authentication?
Authentication, The process of identity verification of the user. In Laymen terms, Attention is the process to check “Who are you?”. Authentication can be using user-id & password or token or certificate-based. Authentication is the key to allow only authorized users can connect to access the data and system.
2. Type of authentication in SQL Server (SQL & Windows)
Microsoft supports 2 types of authentications, SQL, and Windows authentication. For each login created on SQL Server, it should be part of either authentication.
3. Windows Authentication
Windows Authentication depends on Active Directory (AD) to authenticate users before they connect to SQL. Windows auth uses a series of encryption to ensure no leak of sensitive data. Microsoft recommends window authentication mode because AD is the best way to manage password policies and user and group access to applications in your organization. User Id and password stored in Active Directory.
You can run SQL Server only on Windows Authentication called Windows Authentication Mode.
Advantages of Windows Authentication:
 All in One Domain: When your environment is on one domain, both DB and application are hosting on the same domain. Windows authentication is the best and secure way of DB communication.
 Domain Controller: Here, we had Domain Controller to manage logins creation \ modification and authentications.
 Password Policies: Windows logins must bear with password policies. Windows logins can not skip it. This makes the user password more secure and updated.
 DBA relief: DBA no more needed to manage users and passwords. This task is completely managed by domain admins in windows auth.
 Windows Group: Windows give functionality to create groups and that group can be added to SQL server to give access to all users in the group in one go.
Disadvantages of Windows Authentication:
 No Control: DBA has no control over logins, it completely managed by domain admins.
 Windows Group: Windows groups has a disadvantage as well because membership of the group is hidden to DBA. DBA will not be aware of who is part of groups or when users added or removed from the group.
How to Create a Windows Login?
4. SQL Authentication
SQL Server Authentication works by storing usernames and passwords in the “Master” database server. It can be used in situations where Active Directory is not available., but, whenever possible, use Windows Authentication exclusively. You can use SQL Server and Windows Authentication at the same time called Mixed Mode.
You can create multiple users under SQL authentication to provided different users different access as per their requirement.
Disadvantages of SQL Server Authentication:
 Multiple User Name and Password for Users: Let’s consider a user who needs access to multiple database instances. In SQL authentication, the User will have separate login and password for each instance. This will be difficult to manage and annoying.
 Multiple User Name and Password for DBAs to Manage: Let consider, You are a team of 5 DBAs and using 50 SQL Server instances. This lead to 250 user ID & Password management for DBA.
 Enforcing Password Policy: SQL Server logins can be skipped from enforcing password policy. These lead to week passwords and no regular change of password.
 Application or user pass SQL login and password to connect. This communication can be hacked and user id & passwords can be compromised.
Advantages of SQL Server Authentication
 Legacy Application Support: You can keep using your legacy application with support of SQL authentication.
 Vendor Application Support: Vendor or 3rd Party application which are not built specifically to sue SQL Server can be used with SQL Server using SQL authentication
 Cross Operating System: SQL authentication is best to use when the environment had multiple types of operating systems like Windows, Linux, etc.
 No Domain \ Workgroup \ Non-Trusted Domain Environment: SQL authentication is best to use while working in No Domain, Workgroup, Non-Trusted Domain Environment where users cannot be authenticated using Windows domain controller.
How to Create SQL Logins
6. SQL Server Authentication Modes
Based on available authentication types, SQL Server supports 2 types of authentication modes.
 Windows Authentication Mode:- This is the default one and preferred from Microsoft.
 Mixed Mode: Mixed mode supported Windows authentication along with SQL authentication. Both types (windows & SQL) logins can work under this model.
How to check the current authentication mode?
Right Click on Server Instance Name in Object Explorer , go to Properties , Security Tab
To change authentication mode, you can change the selection from this screen and click ok. This change needs SQL Service to restart to take into effect.