Page 101 - DCAP508_DATABASE_ADMINISTRATION

Unit 6: SQL Server Authentication

The issuer can revoke a certificate before it expires. Revocation cancels the binding of a public Notes
key to an identity that is asserted in the certificate. Each issuer maintains a certificate revocation
list that can be used by programs when they are checking the validity of any given certificate.
The self-signed certificates created by SQL Server follow the X.509 standard and support the
X.509 v1 fields.

Asymmetric Keys

An asymmetric key is made up of a private key and the corresponding public key. Each key can
decrypt data encrypted by the other. Asymmetric encryption and decryption are relatively
resource-intensive, but they provide a higher level of security than symmetric encryption. An
asymmetric key can be used to encrypt a symmetric key for storage in a database.

Symmetric Keys

A symmetric key is one key that is used for both encryption and decryption. Encryption and
decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in
the database.

Transparent Data Encryption

Transparent Data Encryption (TDE) is a special case of encryption using a symmetric key. TDE
encrypts an entire database using that symmetric key called the database encryption key. The
database encryption key is protected by other keys or certificates which are protected either by
the database master key or by an asymmetric key stored in an EKM module.

Task List the various types of SQL server encryption mechanisms and differentiate
between them.

6.6 Summary

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.
Microsoft SQL Server can operate in one of two security (authentication) modes:
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.

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. Users who connect through a Windows
NT 4.0 or Windows 2000 user account can make use of trusted connections in either
Windows Authentication Mode or Mixed Mode.
Windows Authentication has certain benefits over SQL Server Authentication, primarily
due to its integration with the Windows NT 4.0 and Windows 2000 security system.
Windows NT 4.0 and Windows 2000 security provides more features, such as secure

LOVELY PROFESSIONAL UNIVERSITY 95