A common question asked from super users of applications is, “What is the difference between Windows Authentication and SQL Server Authentication?” While there are many details under the covers of SQL Server and Windows Authentication, the differences can be summed up as follows.
Windows Authentication
Windows authentication means the account resides in Active Directory for the Domain. SQL Server knows to check AD 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 AD. 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 AD Group in SQL Server and as long as the AD account resides in the AD group, the permission to the SQL Server will be allowed. If someone leaves that company, that ID can be removed from the AD 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.
Can an account use both SQL and Windows Authentication?
No. An account cannot use both types of authentication. It has to be decided at the time of creation which authentication method will be used. Once decided then the account is created in AD or in SQL Server.
You can create a SQL Server account with the same name as the Windows account but this is not recommended. It adds to the confusion of which account is being used at which time and can make account management complex.
How can you change from using Windows Authentication to SQL Authentication for a given account?
You cannot change the authentication method of an account. The process is a bit more in depth. If you wish to use SQL Authentication instead of Windows, before making any changes, it is recommended to complete an audit to insure no windows processes are using the account. This would include any connections strings in the application. When you define connection strings to the database, you also define the authentication method to use.
Once it has been determined nothing is using the account, the windows account would need to be delete from the SQL Server instance. Note: Make sure to document all permissions settings to be sure the new account has the privileges needed. (Also, it is recommended to remove the account from AD to keep AD from cluttered with accounts that are no longer needed or wanted.) The creation of the new SQL Server Authentication account can be completed.
Summary
Windows Authentication uses AD to manage user account and passwords. The account can be part of an AD group. SQL Server uses AD to validate the account is active and then checks what permissions that account has in the SQL Server.
SQL Server Authentication manages the created account and password. This information is stored in the Master Database.
For further reading, learn about this SQL Server tip!