SQL auth and windows auth Developer version

Authentication is the main component in the web application, Today we will discuss detail version of SQL auth and Windows auth in SQL server

Before moving into the SQL auth and Windows auth lets discuss some basics on SQL server authentication features

When a application wants to connect the Data base we need to establish a connection, that connection needs some basics information:

• SQL fully qualified domain name
• Port
• Instance Name
• SQL server credentials

SQL server consists of two types of authentication.

1. SQL server and windows auth

2. Windows auth

SQL server and Windows auth mode:

Usually in most of the web application are using the mode while establishing connection.

They allows both sql and windows auth

Database administrators create SQL logins and provide appropriate permissions for users to authenticate themselves to SQL Server. Users need to specify the login and password while connecting to SQL Server

The user’s credentials are validated through the information stored in the master database. You can enforce the following policies for SQL Server logins.

• Enforce password policy: The administrators can check this option to implement the Windows password policy for SQL Server logins. It includes specifying password length and complexity.
• Enforce password expiration: You can enforce the maximum age of a password. The password will be expired and needs to change as defined by the age criteria.
• User must change password at next login: The administrator assigns a password during SQL login creation. Once the user logs in with their credentials, they need to specify a new password, and the administrators will not be aware of this new password.

Note: All these configurations are at the individual SQL login level. Therefore, if you need to create multiple SQL logins, you must configure each account with the required policy.

We cannot enable only SQL authentication. To enable it, use the mixed authentication option which includes both Windows and SQL authentication.

Disadvantages:

There are quite a few limitations and disadvantages of using SQL Server authentication alone.

Users need to remember the SQL login credentials and provide them in the connection string each time they connect to SQL Server. If you have multiple SQL Servers, it might be difficult for the user to keep track of the passwords for each instance.

SQL Server stores the password in the master database in encrypted (hash) form. Hackers can steal the information by accessing the database. Since these encrypted credentials need to be passed over the network, this can increase the chances of user credentials being stolen.

You cannot implement additional (customized) account policies with the SQL Server authentication logins.

It increases the task of login management for database administrators. Database administrators do not have a central management console for managing logins across all instances.

Suppose you have 500+ SQL instances and a user requires access to all these instances. In this case, it would be a tedious task for the database administrator to connect to each instance and create user logins. Similarly, if a person left the organization, the database administrator needs to find out that individual’s SQL logins and remove them from all these instances. This can be a very time-consuming process.

You might get orphan user issues when moving a database to different instances, and it might happen due to a SID mismatch in the master and user database on the new instance.

You need to manage the security policies for each SQL login. You cannot define a universal policy for all accounts in your organization. For a large database footprint, it is an arduous task to define the policy for each individual login.

Windows Auth:

In Windows authentication, the user should first authenticate himself within Active Directory. SQL Server authenticates users through the Windows principal token in the OS. With that, SQL Server does not ask for a password for identity validation. Therefore, Windows confirms users’ identities for authentication. SQL Server does not store the credentials in the Windows authentication. The connection using Windows authentication is called a trusted or integrated connection.

Advantage:

Windows authentication is a secure way of connecting to SQL Server, and it uses the tokens and SPNs for authentication purposes using the Kerberos authentication protocol. Therefore, it does not send passwords across the network, and it safeguards stealing passwords across the network.

SQL Server does not store the user’s credentials.

It uses Kerberos security protocol, and you can implement password policies such as complex passwords, account lockouts and password expiration. This password policy can be implemented at the organization level across all servers. Therefore, you can control user security policies at the organization level instead of at the individual login level like with SQL Server authentication.

Windows authentication enables the separation of duties. The Active Directory (AD) team manages the AD users. Whereas, the DBA adds AD users in the SQL instances and provides appropriate permissions.

Active Directory helps to create Windows groups. The AD team can add multiple people that require equal access in an AD group. Later, you can add the group in the SQL instance and provide permissions at the group level. Therefore, if a new person joins, once he is part of the AD group, database access is automatically granted across the server where this AD group exists. Similarly, once a user moves from the organization and their ID is removed from these AD groups, they can no longer access the database.