Authentication Modes in SQL Server

This article explains things in the form of Questions & Answers to get a clear idea of authentication modes in SQL Server.

Note: OS is Windows 7 Professional Edition, SQL Server is SQL Server 2012 Enterprise Evaluation Edition in screenshots. 

 

Question 1: Can we configure SQL Server authentication mode at the time of installation also?

Answer:

• Yes, we can configure the authentication mode at the time of installation also.
  
• At the time of installation in Database Engine Configuration we can enable or choose Mixed Mode authentication.
  
  
  Figure 1. Illustrating authentication modes
  
  We have already discussed how we enable Mixed mode authentication after SQL Server installation in part 2.

Question 2: What is the default mode of authentication in SQL Server?

Answer:

Default mode of authentication is Windows Authentication Mode (refer to Figure 1).

Question 3: Which authentication mode is more secure and why?

Answer:

Windows Authentication uses the domain user's credential token provided as part of the connection against an authorization list stored on the SQL Server. Since Windows Authentication does not credentials over the connection, it is inherently more secure than SQL authentication. [Reference: Microsoft SharePoint Technologies: Planning, Design and Implementation By Kevin Laahs, Emer McKenna, Veli-Matti Vanamo].

In Windows Authentication mode a single sign-on at login is enough to provide access to all the services that support Windows Authentication. Hence it is easier to use and implement in any environment.

Question 4: How is Windows Authentication different from the policy point of view?

Answer:

From the policy point of view Windows Authentication follows Active Directory Group Policy Management.

We can open Group policy Management by navigating to All Programs >> Administrative tools >> Group Policy management.

Or in the Run command by typing gpmc.msc as in the following:


Figure 2: Opening Group Policy Management via Program Files


Figure 3: Group Policy Management Window

Note: Group Policy Management is a windows feature in Control Panel, we must turn on this feature to work with Group Policy Management. If it is not there then we have to install Remote Server Administration Tools (RSAT).

You can install RSAT from the following link:


Figure 4: Enabling Group Policy Management Tools from Windows feature turn on or off.

On the other hand Mixed Mode or SQL Server Authentication mode follows the Local word Policy of the system.

We can open Local Security Policy by navigating to All Programs >> Administrative Tools >> Local Security Policy.

Or by typing secpol.msc in the Run command as in the following:


Figure 5: Opening of Local Security Policy


Figure 6: Local Security Policy Window

Here two things should be noted, the maximum word age & the minimum word age.

To understand the difference between these two it is very important although it is managed by system admins or network admins but a DBA should be aware of this.

Maximum Password Age

This specifies how long a user can use the same word for his/her Windows account or we can say the account is expired after 30 days for provided screenshot (Figure 6).

The default value for maximum word age is 42.

Minimum Password Age

The default value for this is 0, in other words the user can change the word whenever they want or we can say the user can change the word "n" number of times.

But if we set this value to 1 then it means the user can change the word only one time in 24 hours. If he/she wants to change the word two times in a day then it will not be allowed because the value of minimum word age is 1 and it will be in effect for one day.

This is what I learned more about authentication and served you what I learned from my experience.

I hope you like this a little bit and it will create interest.