SQL Server Security Basics | What is Authorization?

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!

Advertisements

SQL Server Security Basics | What is Authentication?

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