By definition Authentication means the process of verifying the identity of a user or process. If a user wants to talk to the database, SQL Server asks “Who you are?”, and authenticates you. There are three types of authentication modes available:
- Windows Authentication
- SQL Server Authentication
- Azure Active Directory
Windows Authentication
- This is the default authentication mode and the more secure as compared to SQL Server Authentication.
- Microsoft BOL recommends to use Windows Authentication over SQL Server Authentication.
- This mode is available both on SQL Server running on-premises and Azure Virtual Machine.
- It uses Kerberos security protocol.
- Connection made under this mode is also called “Trusted Connection” as SQL Server trusts Windows credentials.
- Has additional password policy, such as strong password validation, support for account lock and password expiration.
SQL Server Authentication
- Logins here are validated which are created and managed by SQL Server.
- Unlike Windows Authentication, user should provide credentials every time while connecting to SQL Server.
- There are few (optional) password policies also available.
- This mode can be used where there is a requirement to support mixed operating system for applications and users cannot be validated using Windows domain.
- Can be useful with web-applications where users have the provision to create their own identities.
- It does not use Kerberos security protocol, and there is also a risk for applications that connect automatically with the SQL Server may save the password in file in clear text.
Azure Active Directory
- This authentication mechanism validates an identity based on Azure Active Directory (Azure AD).
- It supports token-based authentication, ADFS (domain federation) and/or built-in vanilla authentication without domain synchronization.
- It can also support mechanism of password rotation in a single place.
- Allows management of identities centrally (Central ID), which helps in simplifying user and permission management.
I am going to write a series of blogs on security basics, this one is first one in the row. Stay tuned.
SQLpathy 