Differences Between Windows and SQL Server Authentication — MsSQL | Code Factory

Reference Link : Link

Donate : Link

Windows Authentication :

Windows authentication means the account resides in Active Directory for the Domain. SQL Server knows to check Active Directory to see if the account is active, password works, and then checks what level of permissions are granted to the single SQL server instance when using this account. This helps with account management since the account and password only need to be defined once. Plus, you can enforce your company’s security policies on the account (Password complexity, password expiration, etc.).

Another handy feature is you can grant access to an instance of SQL Server using a group defined in Active Directory. Take, for example, you have a team of DBAs. Instead of having to create individual accounts and grant permissions to the individual account, you can grant access to the Active Directory Group in SQL Server and as long as the Active Directory account resides in the Active Directory group, the permission to the SQL Server will be allowed. If someone leaves that company, that ID can be removed from the Active Directory Group and that user no longer has access to the server. No extra steps to remove those individually granted accounts.

SQL Server Authentication :

SQL Server Authentication means the account resides in the SQL server master database but nowhere on the Domain. The username and password are stored in the master database. If this account needs to access more than 1 SQL Server instance, then it has to be created on each instance. If the password needs to be changed, it needs to be changed on each instance individually to keep them all in sync. Using SQL Server Authentication gives you the ability to override the Domain Security policies when it comes to password complexity and password expiration. But this can lead to less secure accounts with elevated privileges in your database.