Database Mirroring Transport Security

Transport security in SQL Server 2005 involves authentication and, optionally, encryption of messages exchanged between the databases.

For database mirroring, authentication and encryption are configured on the endpoint. For an introduction to mirroring endpoints, see Database Mirroring Endpoint.

Authentication

Authentication is the process of verifying that a user is who the user claims to be. Connections between database mirroring endpoints require authentication. Connection requests from a partner or witness, if any, must be authenticated.

The type of authentication used by a server instance is a property of its database mirroring endpoint. Two types of transport security are available for database mirroring: Windows Authentication and certificate-based authentication.

Windows Authentication supports two authentication protocols: NT LAN Manager (NTLM) and Kerberos. A database mirroring endpoint can be configured to use only one protocol or to negotiate between them. By default, negotiation is used. The default value, NEGOTIATE, causes the endpoint to use the Windows negotiation protocol to choose either NTLM or Kerberos. If a specific authorization method (NTLM or Kerberos) is specified on an endpoint, it can use only that method. If the opposite endpoint is configured to use only the other method, the endpoints cannot connect with each other. For more information about these methods, see Endpoint Authentication Types.

Note

For information about this endpoint, see Database Mirroring Endpoint.

A database mirroring connection uses either Windows Authentication (the Security Support Provider Interface (SSPI)) or certificate-based authentication.

Windows Authentication

Under Windows Authentication, each server instance logs in to the other side using the Windows credentials of the Windows user account under which the process is running. For this reason, Windows Authentication requires that SQL Server services must run as domain users in trusted domains or as network services.

To authenticate both ends of a connection, Windows Authentication uses the credentials of the Windows user account on which the SQL Server instances are running. Therefore, the user account of each server instance must have the permissions needed to log in and send messages to each of the other server instances.

For an example of setting up a database mirroring session using Windows Authentication, see Example: Setting Up Database Mirroring Using Windows Authentication (Transact-SQL).

Certificates

In some situations, such as when server instances are not in trusted domains or when SQL Server is running as a local service, Windows Authentication is unavailable. In such cases, instead of user credentials, certificates are required to authenticate connection requests. The mirroring endpoint of each server instance must be configured with its own locally created certificate.

The encryption method is established when the certificate is created. For more information, see How to: Allow Database Mirroring to Use Certificates for Outbound Connections (Transact-SQL). Carefully manage the certificates that you use.

A server instance uses the private key of its own certificate to establish its identity when setting up a connection. The server instance that receives the connection request uses the public key of the sender's certificate to authenticate the sender's identity. For example, consider two server instances, Server_A and Server_B. Server_A uses its private key to encrypt the connection header before sending a connection request to Server_B. Server_B uses the public key of Server_A's certificate to decrypt the connection header. If the decrypted header is correct, Server_B knows that the header was encrypted by Server_A, and the connection is authenticated. If the decrypted header is incorrect, Server_B knows that the connection request is inauthentic and refuses the connection.

Security Note:
Only install certificates from trusted sources.

SQL Server 2005 does not provide any automated method for configuring database mirroring security using certificates. Using Transact-SQL is required. For an example of using certificate-based authentication for setting up a database mirroring session, see Example: Setting Up Database Mirroring Using Certificates (Transact-SQL).

Data Encryption

By default, a database mirroring endpoint requires encryption of data sent over mirroring connections. In this case, the endpoint can connect only to endpoints that also use encryption. Unless you can guarantee that your network is secure, we recommend that you require encryption for your database mirroring connections. However, you can disable encryption or make it supported, but not required. If encryption is disabled, data is never encrypted and the endpoint cannot connect to an endpoint that requires encryption. If encryption is supported, data is encrypted only if the opposite endpoint either supports or requires encryption.

Note

Mirroring endpoints created by SQL Server Management Studio are created with encryption either required or disabled. To change the encryption setting to SUPPORTED, use the ALTER ENDPOINT Transact-SQL statement. For more information, see ALTER ENDPOINT (Transact-SQL).

Optionally, you can control the encryption algorithms that can be used by an endpoint, by specifying one of the following values for the ALGORITHM option in a CREATE ENDPOINT statement or ALTER ENDPOINT statement:

ALGORITHM value	Description
RC4	Specifies that the endpoint must use the RC4 algorithm. This is the default.
AES	Specifies that the endpoint must use the AES algorithm.
AES RC4	Specifies that the two endpoints will negotiate for an encryption algorithm with this endpoint giving preference to the AES algorithm.
RC4 AES	Specifies that the two endpoints will negotiate for an encryption algorithm with this endpoint giving preference to the RC4 algorithm.

If connecting endpoints specify both algorithms but in different orders, the endpoint accepting the connection wins.

Note

Though considerably faster than AES, RC4 is a relatively weak algorithm, while AES is a relatively strong algorithm. Therefore, we recommend that you use the AES algorithm.

For information about the Transact-SQL syntax for specifying encryption, see CREATE ENDPOINT (Transact-SQL).

To configure transport security for a database mirroring server instance

• How to: Configure a Database Mirroring Session (SQL Server Management Studio)
• How to: Create a Mirroring Endpoint for Windows Authentication (Transact-SQL)
• How to: Allow Database Mirroring to Use Certificates for Outbound Connections (Transact-SQL)

See Also

Concepts

Managing Metadata When Making a Database Available on Another Server Instance
Database Mirroring Endpoint
Troubleshooting Database Mirroring Setup

Other Resources

Choosing an Encryption Algorithm
ALTER ENDPOINT (Transact-SQL)
DROP ENDPOINT (Transact-SQL)
Security Considerations for SQL Server
sys.database_mirroring_endpoints (Transact-SQL)
sys.dm_db_mirroring_connections

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
5 December 2005	New content: Recommended using the AES algorithm for data encryption..