Think of this:

• Whether a stranger is authorized to enter your house without your permission?
• Whether you authorize your friend to take your car whenever he wants to go on a drive?
• Whether a co-employee is authorized to access your confidential information stored with Human Resource department?

You might be getting a hang of it… Authorization is all about “What can a person (or identity in digital world) do?”; have they so called “access rights/privileges” to the desired “resources”.

That being said, Authorization takes a form of access policies that an organization sets forth for the resources being used. These access policies are created and/or controlled by an authority (usually a senior employee or department head).  These policies are formulated based on “principle of least privilege” – which says that a user/identity should only have minimum set of privileges to get their work done.

In SQL Server, Authorization is enforced with Permissions, and we have a freedom to club common permissions into Roles. These permissions are hierarchical in nature and exist both at database and server level.

I will talk more about Authorizations, Permission Hierarchy, and principle of least privilege in upcoming blogs. So stay tuned!

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.

References:

Choosing Authentication Mode

Azure AD Authentication