Change SQL Server Database Mirroring Endpoint Encryption Algorithm


By:   |   Updated: 2020-06-15   |   Comments   |   Related: More > Database Mirroring

Problem

Our agency has a number of SQL Server databases for which mirroring was implemented a couple of years ago. When I looked at the endpoints, I noticed that they are using the RC4 encryption algorithm. While reading about endpoints I found out that RC4 is being deprecated in future versions of SQL Server and has a number of vulnerabilities. I would like to modify the algorithm being used, but cannot afford much downtime to drop and re-create mirroring and am unfamiliar with the Transact-SQL statements needed to make the change. Can you help me out with this?

Solution

Thankfully, there is a way of modifying the algorithm without having to drop it all and start over. In recent years, a number of vulnerabilities have been uncovered, making the RC4 algorithm a sub-optimal choice for encryption. This is particularly true if your institution is dealing with healthcare or other data that requires as strong of an encryption algorithm as possible during transit. At the time database mirroring was originally implemented, information about the vulnerabilities associated with the RC4 algorithm were not known. The Advanced Encryption Standard (AES), also called Rijndael, is the strongest algorithm available in the SQL Server encryption suite for transport security and should be used instead of RC4. The first thing that should be done is to confirm which endpoint(s) are using the RC4 algorithm:

USE MASTER;

SET NOCOUNT ON;

SELECT
 endpoint_id AS EndpointID
 , [name] AS EndpointName
 , protocol_desc AS ProtocolUsed
 , REPLACE([type_desc], '_', ' ') AS EndpointType
 , role_desc AS RoleType
 , is_encryption_enabled AS IsEncryptionEnabled
 , connection_auth_desc AS ConnectionAuthentication
 , encryption_algorithm_desc AS EncryptionAlgorithm
FROM sys.database_mirroring_endpoints WITH (NOLOCK)
WHERE type = 4 --Database_Mirroring
Sample output of query to retrieve information on database mirroring endpoints

Once you find the endpoint(s) you would like to modify, the next step is to pause mirroring. How you do this depends on the number of databases being mirrored. If there is just one database then you can issue a one-line SQL statement:

USE MASTER;

ALTER DATABASE [DatabaseName] SET PARTNER SUSPEND

If, however, you have multiple databases being mirrored, you will need to loop through the list of databases being mirrored by querying the sys.database_mirroring system catalog view. Included in that view is the database_id, so the query to obtain them is:

SELECT
  DB_NAME(database_id) AS DatabaseName
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL

You should store this list of databases for when you have to resume mirroring (either in the master database or, preferably, in a DBA database). Before the endpoint is modified a script can be executed to loop through the entries and suspend mirroring on each database. Once mirroring has been suspended on all databases you can drop and re-create the endpoint on each instance involved in mirroring, using AES instead of RC-4 for the encryption algorithm. Fortunately, the endpoint can be dropped and re-created without affecting mirroring, provided mirroring has been paused:

USE [master]
GO
 
DROP ENDPOINT [DatabaseMirroring]
GO
 
/* FOR PRINCIPAL AND MIRROR */
CREATE ENDPOINT [DatabaseMirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
 
/* FOR WITNESS */
CREATE ENDPOINT [DatabaseMirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 7022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

Once the new endpoint is created on the PRIMARY, MIRROR, and WITNESS (if applicable but preferable) instances, you can resume mirroring by issuing the following statement:

ALTER DATABASE [DatabaseName] SET PARTNER RESUME

To confirm that the database mirroring has resumed and that no errors occurred check SQL Server Management Studio and the SQL Server error logs:

Database Mirroring confirmation using SQL Server Management Studio
/* Script to check error log entries using xp_readerrorlog */
SET NOCOUNT ON;

DECLARE @Log TABLE
(EntryID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, LogDate DATETIME, ProcessInfo VARCHAR(50), LogText VARCHAR(4000))

INSERT INTO @Log(LogDate, ProcessInfo, LogText)
EXEC xp_readerrorlog 0;

INSERT INTO @Log(LogDate, ProcessInfo, LogText)
EXEC xp_readerrorlog 1;

SELECT LogDate, ProcessInfo, LogText FROM @Log
WHERE LogDate >= DATEADD(MINUTE, -30, CURRENT_TIMESTAMP)
ORDER BY LogDate DESC
Next Steps


Last Updated: 2020-06-15


get scripts

next tip button



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

Configure SQL Server Database Mirroring Using SSMS

SQL Server Database Mirroring Status Check and Manual Failover T-SQL Scripts

Steps to Apply a Service Pack or Patch to Mirrored SQL Server Databases

Change operating modes for SQL Server Database Mirroring

SQL Server Database Mirroring Inventory and Monitoring Scripts





get free sql tips
agree to terms


Learn more about SQL Server tools