The Database Engine’s security system includes two different security subsystems:
- Windows security
- SQL Server security
Windows security specifies security at the operating system level—that is, the method by which users connect to Windows using their Windows user accounts. (Authentication using this subsystem is also called Windows authentication.)
SQL Server security specifies the additional security necessary at the system level—that is, how users who have already logged on to the operating system can subsequently connect to the database server. SQL Server security defines a SQL Server login (also called “login”) that is created within the system and is associated with a password. Some SQL Server logins are identical to the existing Windows user accounts. (Authentication using this subsystem is called SQL Server authentication.)
Based on these two security subsystems, the Database Engine can operate in one of the following authentication modes:
- Windows mode
- Mixed mode
Windows mode requires users to use Windows user accounts exclusively to log in to the system. The system accepts the user account, assuming it has already been validated at the operating system level. This kind of connection to a database system is called a trusted connection, because the system trusts that the operating system already validated the account and the corresponding password.
Mixed mode allows users to connect to the Database Engine using Windows authentication or SQL Server authentication. This means that some user accounts can be set up to use the Windows security subsystem, while others can be set up to use both the SQL Server security subsystem and the Windows security subsystem.
Implementing an Authentication Mode
You use SQL Server Management Studio to choose one of the existing authentication modes. (For in depth discussion see section “SQL Server Management Studio“) To set up Windows mode, right-click the server and click Properties. In the Server Properties dialog box, choose the Security page and click Windows Authentication Mode. To choose Mixed mode, the only difference is that you have to click SQL Server and Windows Authentication Mode in the Server Properties dialog box.
After a user successfully connects to the Database Engine, the user’s access to database objects is independent of whether Windows authentication or SQL Server authentication is used.
Before you learn how to set database server security, you need to understand encryption policies and mechanisms, discussed next.
