Page 102 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes validation and encryption of passwords, auditing, password expiration, minimum
password length, and account lockout after multiple invalid login requests.

SQL Server Authentication is provided for backward compatibility because applications
written for SQL Server version 7.0 or earlier may require the use of SQL Server logins and
passwords. Additionally, SQL Server Authentication is required when an instance of SQL
Server is running on Windows 98 because Windows Authentication Mode is not supported
on Windows 98. Therefore, SQL Server uses Mixed Mode when running on Windows 98
(but supports only SQL Server Authentication).
After a user has been authenticated and allowed to log in to an instance of Microsoft® SQL
Server™, a separate user account is required in each database the user must access. Requiring
a user account in each database prevents users from connecting to an instance of SQL
Server and accessing all the databases on a server.
Encryption is the process of obfuscating data by the use of a key or password. This can
make the data useless without the corresponding decryption key or password. Encryption
does not solve access control problems. However, it enhances security by limiting data
loss even if access controls are bypassed. For You can use encryption in SQL Server for
connections, data, and stored procedures.
SQL Server encrypts data with a hierarchical encryption and key management infrastructure.
Each layer encrypts the layer below it by using a combination of certificates, asymmetric
keys, and symmetric keys. Asymmetric keys and symmetric keys can be stored outside of
SQL Server in an Extensible Key Management (EKM) module.

6.7 Keywords

Mixed Mode (Windows Authentication and SQL Server Authentication): Mixed Mode allows
users to connect to an instance of SQL Server using either Windows Authentication or SQL
Server Authentication.
SQL Server Authentication: The authentication stage identifies the user using a login account
and verifies only the ability to connect to an instance of SQL Server. If authentication is successful,
the user connects to an instance of SQL Server.

SQL Server Encryption: Encryption is the process of obfuscating data by the use of a key or password.
Windows Authentication Mode (Windows Authentication): Windows Authentication mode
allows a user to connect through a Microsoft Windows NT® 4.0 or Windows® 2000 user account.

6.8 Review Questions

1. Describe the two main types of authentication modes in SQL server.
2. Differentiate between SQL server authentication and windows authentication
3. Discuss the permission validation process along with a suitable diagram.
4. Explain the advantages and disadvantages of SQL server authentication.
5. Define: SQL server encryption. Also explain its hierarchy along with the diagram.

6. Describe the various encryption mechanisms.
7. Comment: Although encryption is a valuable tool to help ensure security, it should not be
considered for all data or connections.

96 LOVELY PROFESSIONAL UNIVERSITY