Transport Security for Database Mirroring and AlwaysOn Availability Groups (SQL Server)
Transport security involves authentication and, optionally, encryption of messages exchanged between the databases. For database mirroring and Always On 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).
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 Always On 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.
Note
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 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.
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. Note: The RC4 algorithm is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you use AES. |
| 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
The RC4 algorithm is only supported for backward compatibility. New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100. (Not recommended.) Use a newer algorithm such as one of the AES algorithms instead. In SQL Server 2012and higher versions, material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.
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).
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)
See Also
Choose an Encryption Algorithm
ALTER ENDPOINT (Transact-SQL)
DROP ENDPOINT (Transact-SQL)
Security Center for SQL Server Database Engine and Azure SQL Database
Manage Metadata When Making a Database Available on Another Server Instance (SQL Server)
The Database Mirroring Endpoint (SQL Server)
sys.database_mirroring_endpoints (Transact-SQL)
sys.dm_db_mirroring_connections (Transact-SQL)
Troubleshoot Database Mirroring Configuration (SQL Server)
Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)deleted