Transport Security for Database Mirroring and AlwaysOn Availability Groups (SQL Server)

Transport security in SQL Server 2005 and later versions involves authentication and, optionally, encryption of messages exchanged between the databases. For database mirroring and AlwaysOn Availability Groups, authentication and encryption are configured on the database mirroring endpoint. For an introduction to database mirroring endpoints, see The Database Mirroring Endpoint (SQL Server).

In this Topic:

• Authentication

• Data Encryption

• Related Tasks

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 for database mirroring or AlwaysOn Availability Groups is a property of the database mirroring endpoint. Two types of transport security are available for database mirroring endpoints: Windows Authentication (the Security Support Provider Interface (SSPI)) and 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. Windows Authentication might require some manual configuration of login accounts, as follows:

• If the instances of SQL Server run as services under the same domain account, no extra configuration is required.

• If the instances of SQL Server run as services under different domain accounts (in the same or trusted domains), the login of each account must be created in master on each of the other server instances, and that login must be granted CONNECT permissions on the endpoint.

• If the instances of SQL Server run as the Network Service account, the login of the each host computer account (DomainName**\**ComputerName$) must be created in master on each of the other servers, and that login must be granted CONNECT permissions on the endpoint. This is because a server instance running under the Network Service account authenticates using the domain account of the host computer.

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 Allow a Database Mirroring Endpoint 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.

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.

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:

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

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

Related Tasks

To configure transport security for a database mirroring endpoint

• Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL)

• Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio)

• Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL)

• Allow a Database Mirroring Endpoint to Use Certificates for Outbound Connections (Transact-SQL)

[Top]

See Also

Reference

ALTER ENDPOINT (Transact-SQL)

DROP ENDPOINT (Transact-SQL)

sys.database_mirroring_endpoints (Transact-SQL)

sys.dm_db_mirroring_connections (Transact-SQL)

Concepts

Choose an Encryption Algorithm

Security and Protection (Database Engine)

Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)

The Database Mirroring Endpoint (SQL Server)

Troubleshoot Database Mirroring Configuration (SQL Server)

Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)