SOL Server Login and Authentication and how it works ?

There are two types of authentication in SQL server as

1. Windows Authentication
2. SQL Authentication

Windows Authentication :

1. These are created for Active Directory User.
2. By the use of Active Directory user both windows and SQL server login are possible.
3. This option is selected at the time of installation.
4. It follow the windows password policy.

How It works :

When the user is connected by the Active Directory user, then windows verify the username and password and allocate a unique that is called TGT (Ticket Granting Ticket). Now when the user is going to login with the TGT, it will be allowed by the SQL server.

SQL Authentication :

1. For non active directory we create the SQL Authentication.
2. By default for every instance there is a user as "sa".
3. The information about SQL usernames and passwords are mentions in SQL server.

How it works :

At the time of login SQL server check and verify the give credentials.

SQL Server users creations :

Windows:

Create active directory user from windows Control Panel and add the user at the time of installation or use the following query as

use master

Go

Create Login <Login Name> from windows.

SQL:

In Object Explorer expand the security -> select login folder -> Add new login and follow the password policy.

NOTE :

Orphan Login:

When active user is deleted, the mapping login in SQL server is called Orphan Login.

To check the orphan login, use the following query as

sp_validatelogins

The solution of the problem is, create the active directory user.