There are two types of authentication in SQL server as
- Windows Authentication
- SQL Authentication
Windows Authentication :
- These are created for Active Directory User.
- By the use of Active Directory user both windows and SQL server login are possible.
- This option is selected at the time of installation.
- 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 :
- For non active directory we create the SQL Authentication.
- By default for every instance there is a user as "sa".
- 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 :
Create active directory user from windows Control Panel and add the user at the time of installation or use the following query as
Create Login <Login Name> from windows.
In Object Explorer expand the security -> select login folder -> Add new login and follow the password policy.
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
The solution of the problem is, create the active directory user.