SQL Server Security
SQL Server validates users at two levels of security on database user accounts and roles.
- Login Authentication
- Permissions Validation
The authentication stage identifies the user using a login account and verifies only the ability to connect with SQL Server. If the authentication is successful, the user is connected to SQL Server. The user then needs permissions to access database on the server, which is done by using an account in each database, mapped to the user login.
The permission validation stage controls the activities the user is allowed to perform in the SQL Server database.
A user must have a login account to connect to SQL Server. SQL Server provides two types of Login Authentications.
- Windows NT Authentication
- SQL Server Authentication
Windows NT Authentication
When using Windows NT Authentication, the user is not required to specify a login ID or password to connect to SQL Server. The user’s access to SQL Server is controlled by the Windows NT account, which is authenticated when he/she logs on to the Windows Operating System.
SQL Server Authentication
When using SQL Server authentication, the users must supply the SQL Server login and password to connect to SQL Server. The users are identified in SQL Server by their SQL Server login.
SQL Server can operate in two security modes:
Windows NT Authentication Mode: Only Windows NT Authentication is allowed. Users cannot specify SQL Server Authentication.
Mixed Mode: It allows users to connect to SQL Server using Windows NT Authentication or SQL Server Authentication.