A comparison of Windows and database authentication in SQL Server
Windows authentication is a method for identifying a login with credentials supplied by the Windows operating system (OS) of the connecting computer.
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.
- When using domain accounts, management of passwords and accounts is centralized; the domain administrator manages all logins that are used throughout the organization, and the database administrator does not need to manage separate accounts.
- 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 enterprise geodatabases are as follows:
- You cannot connect to the geodatabase as a Windows user different from your present login. For example, 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 when you use an ArcSDE service and the ArcSDE application server is installed on a different server than SQL Server. If you want to use Windows-authenticated users, you can either use direct connections to the geodatabase or install the ArcSDE application server and SQL Server on the same machine.