A comparison of Windows and database authentication in SQL Server

Windows authentication is a method for identifying a user with credentials supplied by the Windows operating system (OS) of the user's computer.

Tip:

Since Microsoft SQL Server databases only run on Windows operating systems, OS authentication for SQL Server is also referred to as Windows authentication.

Windows-authenticated logins are the default and, therefore, the recommended type of user for SQL Server databases. By default, when your SQL Server instance is created, this is the only type of login allowed.

Database logins are accounts created in the database management system. These accounts are separate from the login account you use to connect to the operating system.

Windows authentication offers some advantages over database authentication in SQL Server. These advantages are as follows:

• Windows authentication is generally more secure in SQL Server databases than database authentication since it uses a certificate-based security mechanism. Windows-authenticated logins pass an access token instead of a name and password to SQL Server. The access token is assigned by Windows (Active Directory Domain or local operating system) when the user logs in. It contains a unique security ID (SID) for that user and the SIDs of any local or domain Windows groups to which the user belongs. This token's SIDs are compared to all the SIDs in the sys.server_principals system view. Based on the results of this comparison, the login is either granted or denied access to SQL Server.
• It is usually easier to configure Windows authentication, because you add existing Windows logins to SQL Server rather than create separate users.
• When the user connects to the database, the user is not required to enter a user name and password. A single sign-on at login provides access to all services that support Windows authentication.

Some restrictions to consider if you use Windows authentication with ArcSDE geodatabases are as follows:

• You cannot connect to the geodatabase as a Windows user different from your present login. If you logged in as TERRA\Ian, you cannot make a Windows-authenticated connection as TERRA\Sylvia. If you use database authentication, you can log in to the computer as one user but connect to the geodatabase as a different user by providing a different user name and password.
• You cannot use Windows-authenticated users if ArcSDE and SQL Server are installed on separate servers and you use an ArcSDE service to connect to the geodatabase. If you want to use Windows-authenticated users, you can either use direct connections to the geodatabase or install ArcSDE and SQL Server on the same machine.