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
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:
/* Script to check error log entries using <a href="/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/" target="_blank" rel="noopener">xp_readerrorlog</a> */
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
- Keep in mind that the concept of database mirroring in SQL Server will be deprecated in a future release, so consider migrating to Always On Availability Groups
- Make sure you have all scripts written and ready so that the time required to perform all actions will be minimal
- Review the information on Database Mirroring and Database Mirroring Transport Security
- Read about the different options in the statements that pauses and resumes database mirroring, as well as how to remove it
- Review the T-SQL statements used to CREATE, ALTER, and DROP endpoints
- Find more Database Mirroring tips
Tim has been working in the IT industry since 2003. After spending a few years as an “IT Generalist”, his career focus turned to SQL Server and application development. He currently works as an IT Specialist for a Federal agency in Maryland, where he focuses on SQL Server administration and providing guidance to teams on SQL Server performance issues. When not working, he enjoys playing golf, playing bass guitar, and exercising.
- MSSQLTips Awards: Trendsetter (25+ tips)