Mixed Mode and Windows Authentication Considerations

One question that comes up when you are installing and configuring a SQL Server instance is whether to use Windows Authentication or Mixed Mode Authentication. Mixed Mode Authentication allows the use of both SQL Server and Windows Authentication. In SQL Azure there is only SQL Server authentication.

In Windows Authentication the Windows local or domain user account is authenticated by the Windows OS or Domain Controller. In the case of a local account that account is effectively a domain account with the domain being the local machine. Windows Authentication can be further differentiated into service accounts and user accounts. Service accounts should have an SPN and either be trusted for delegation or a managed service account. Fortunately, those decisions and some of the more complex scenarios involving cross domain authentication and Kerberos are externalized to Windows. That is to say that those concerns are removed from the database and put into Windows. The instance trusts the domain to vouch for the user accounts.

Each Windows Login has a SID associated with it. In the event that the Windows user is dropped from the domain the login will persist in the SQL Server instance. If the domain user is recreated from the tombstone object it will re-associate based on its SID. In the event that the tombstone is lost we can use Sidwalk migration components to map the SID. If you lost the SID from Windows you can still use Aaron Bertrand’s conversion function “GetWindowsSID” to get the Windows SID from the server_principals table for the login.

SQL Server logins authenticate at the SQL Server instance. This means that the same user and password combination won’t work for each and every database instance, unless they have been specifically configured to. SQL Logins can also be created for credentials and certificates. Credentials can be used to allow SQL Server logins to have specific permissions outside of instance the login lives on. Certificates are used to stored encryption keys which can be used to authenticate as well but are primarily for encrypting and decrypting messages. SQL Server logins can be recreated using a SID to enable access to databases in the event that the login had been dropped.

Like the Windows login each SQL User has a SID associated with it. And like the Windows login the SQL Login can be dropped and recreated in a manner that will disassociate the name from the SID. This happens at a different level than with the Windows login. The users associated with the SQL Login in the databases will persist, similar to when a domain user is dropped from Windows and the login persists. That means that if we recreate the login by name, the new login will not match the SID of the dropped login unless we specify it. We can usually retrieve that SID from the database. Greg Low points out that you can also update the database user SID, though he advises recreating the login with the user SID.