Configuring Availability Groups to use Certificates

Views 21672


Prior to the release of SQL Server 2016, Database Mirroring was the only SQL Server Technology/feature at our fingertips that we could use to meet our High Availability (HA) / Disaster Recovery (DR) requirements for environments that were not residing in a Domain. Given with the release of SQL Server 2016 and Windows Server 2016, we now have the ability to create and support Non-Domain and Cross-Domain Windows Server Failover Clusters (WSFC’s). As such if we have built Non-Domain or Cross-Domain WSFC we need to undertake some prep-work differently to how we would setup and configure an Availability Group in a Domain Configuration. Just like Database mirroring we need to use certificates. In this post I will take you through how to configure an Availability Group using certificates.

Scenario

For this blog post we are following on from How to build a Non-Domain Cluster. So we have a 2 node Non-Domain cluster (SMC-WG1-SQL01 & SMC-WG1-SQL02) that we are going to configure with an Availability Group.

Build Steps:

  1. On [SMC-WG1-SQL01] open SQL Server Configuration Manager | in the properties of the SQL Server Instance | Navigate to AlwaysOn High Availability | Tick the Enable AlwaysOn Availability Groups | Restart the Instance for this feature to take effect
  2. On [SMC-WG1-SQL02] open SQL Server Configuration Manager | in the properties of the SQL Server Instance | Navigate to AlwaysOn High Availability | Tick the Enable AlwaysOn Availability Groups | Restart the Instance for this feature to take effect
  3. On [SMC-WG1-SQL01] open Management Studio to run the following script commands:
Use Master
Go
-- Create Master Key
-- Use your own Password and not this demo password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1R3@llyStr0ngP@ssw0rd!';
Go
-- Create Certificate to be used across on SMC-WG1-SQL02
-- Change the Certificate Name and Subject to meet your environment
CREATE CERTIFICATE [SMC-WG1-SQL01-Cert] WITH SUBJECT = 'SMC-WG1-SQL01-Cert';
Go
-- Create Login for SMC-WG1-SQL02 Cluster Node
-- Change the Login Name and Password for your environment
CREATE LOGIN [SMC-WG1-SQL02-Login]  WITH PASSWORD = 'D1ff3rentStr0ngP@ssw0rd!';
Go
-- Create the User Account for the newly created Login on server SMC-WG1-SQL01
-- Change the User Account and Login to match above and your environment
CREATE USER [SMC-WG1-SQL02-User] FOR LOGIN [SMC-WG1-SQL02-Login];
Go
-- Backup the Certificate to copy across to SMC-WG1-SQL02
-- Change the location to meet your environment
BACKUP CERTIFICATE [SMC-WG1-SQL01-Cert] To FILE = '\SMC-WG1-SQL01CertificatesSQL-SMC-WG1-SQL01.cer';
Go
  1. Manually copy the newly backed up certificate across to SMC-WG1-SQL02
  2. On [SMC-WG1-SQL02] open Management Studio to run the following script commands:
Use Master
Go
-- Create Master Key
-- Use your own Password and not this demo password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1R3@llyStr0ngP@ssw0rd!';
Go
-- Create Certificate to be used across on SMC-WG1-SQL01
-- Change the Certificate Name and Subject to meet your environment
CREATE CERTIFICATE [SMC-WG1-SQL02-Cert] WITH SUBJECT = 'SMC-WG1-SQL02-Cert';
Go
-- Create Login for SMC-WG1-SQL01 Cluster Node
-- Change the Login Name and Password for your environment
CREATE LOGIN [SMC-WG1-SQL01-Login]  WITH PASSWORD = 'D1ff3rentStr0ngP@ssw0rd!';
Go
-- Create the User Account for the newly created Login on server SMC-WG1-SQL02
-- Change the User Account and Login to match above and your environment
CREATE USER [SMC-WG1-SQL01-User] FOR LOGIN [SMC-WG1-SQL01-Login];
Go
-- Backup the Certificate to copy across to SMC-WG1-SQL01
-- Change the location to meet your environment
BACKUP CERTIFICATE [SMC-WG1-SQL02-Cert] To FILE = '\SMC-WG1-SQL02CertificatesSQL-SMC-WG1-SQL02.cer';
Go
CREATE CERTIFICATE [SMC-WG1-SQL01-Cert] AUTHORIZATION [SMC-WG1-SQL01-User] FROM FILE = '\SMC-WG1-SQL02CertificatesSQL-SMC-WG1-SQL01.cer';
Go
  1. Manually copy the newly backed up certificate across to SMC-WG1-SQL02
  2. Back On [SMC-WG1-SQL01] run the following:
CREATE CERTIFICATE [SMC-WG1-SQL02-Cert] AUTHORIZATION [SMC-WG1-SQL02-User] FROM FILE = '\SMC-WG1-SQL01CertificatesSQL-SMC-WG1-SQL02.cer';
Go
  1. We now need to create the Endpoints on all workgroup servers that will use the Certificates that we have just configured and allow for the communication to occur between the workgroup servers allowing for the configuration of the Availability Group.
CREATE ENDPOINT WGAG_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [SMC-WG1-SQL01-Cert] , ROLE = ALL);
Go
GRANT CONNECT ON ENDPOINT::WGAG_Endpoint TO [SMC-WG1-SQL02-Login];
  1. Back on [SMC-WG1-SQL02] we can create the endpoint with the following scripts
CREATE ENDPOINT WGAG_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [SMC-WG1-SQL02-Cert] , ROLE = ALL);
Go
GRANT CONNECT ON ENDPOINT::WGAG_Endpoint TO [SMC-WG1-SQL01-Login];

IF (SELECT state FROM sys.endpoints WHERE name = N'WGAG_Endpoint') <> 0
BEGIN
 ALTER ENDPOINT [WGAG_Endpoint] STATE = STARTED
END


GO

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO
  1. On [SMC-WG1-SQL01] run the following script commands:
IF (SELECT state FROM sys.endpoints WHERE name = N'WGAG_Endpoint') <> 0
BEGIN
ALTER ENDPOINT [WGAG_Endpoint] STATE = STARTED
END

GO

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

Create Database SMC_WG1_AG_DB1;

-- Undertake an Initial Backup to allow the newly created Database to be ready to be added to an Availability Group.
BACKUP DATABASE [SMC_WG1_AG_DB1] 
TO  DISK = N'\SMC-WG1-SQL01BackupSMC_WG1_AG_DB1.bak' 
WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

BACKUP LOG [SMC_WG1_AG_DB1] 
TO  DISK = N'\SMC-WG1-SQL01BackupSMC_WG1_AG_DB1_20161008213659.trn'
WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

-- Manually copy the full backup and TLog backup across to \SMC-WG1-SQL02Backup


GO -- Create our Workgroup Availability Group
CREATE AVAILABILITY GROUP [SMC_WG1_AG1]
WITH (
 AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
 DB_FAILOVER = ON,
 DTC_SUPPORT = NONE
)
FOR DATABASE [SMC_WG1_AG_DB1]
REPLICA ON N'SMC-WG1-SQL01' WITH (
 ENDPOINT_URL = N'TCP://SMC-WG1-SQL01.SMC.local:5022', 
 FAILOVER_MODE = AUTOMATIC, 
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
 BACKUP_PRIORITY = 50, 
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
 N'SMC-WG1-SQL02' WITH (
 ENDPOINT_URL = N'TCP://SMC-WG1-SQL02.SMC.local:5022', 
 FAILOVER_MODE = AUTOMATIC, 
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
 BACKUP_PRIORITY = 50, 
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
);

GO
  1. On [SMC-WG1-SQL02] run the following script commands:
-- Join SMC-WG1-SQL02 to the AG
ALTER AVAILABILITY GROUP [SMC_WG1_AG1] JOIN;

GO
-- We are now ready to add a database to the AG

-- Start the Restoration of the the database to be added to the Availability Group

RESTORE DATABASE [SMC_WG1_AG_DB1] 
FROM  DISK = N'\SMC-WG1-SQL02BackupSMC_WG1_AG_DB1.bak' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

RESTORE LOG [SMC_WG1_AG_DB1] 
FROM  DISK = N'\SMC-WG1-SQL02BackupSMC_WG1_AG_DB1_20161008213659.trn' 
WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO
-- Bring the AG online
ALTER DATABASE [SMC_WG1_AG_DB1] SET HADR AVAILABILITY GROUP = [SMC_WG1_AG1];
GO
  1. On [SMC-WG1-SQL01] run the following script commands:
ALTER AVAILABILITY GROUP [SMC_WG1_AG1]
ADD LISTENER N'SMC_WG1_AGL1' (
WITH IP
((N'10.10.10.20', N'255.0.0.0')
)
, PORT=1433);

GO

Congratulations you have now successfully created a Non-Domain Availability Group utilizing certificates to allow the instances of SQL Server to communicate with each other. All steps have been put together based on my demonstration environment. Ensure that you undertake this in a non-production environment before going near production.

7 responses to “Configuring Availability Groups to use Certificates”

  1. John says:

    In step 3 you have the comment:
    “Create Login for SMC-WG1-SQL02 Cluster Node”
    The subsequent command indeed creates a login called SMC-WG1-SQL02-Login. Understood. The SQL02 node will need this to log in to the SQL01 node. I think I get it.

    This is then followed by the comment:
    “Create the User Account for the newly created Login for SMC-WG1-SQL01”

    This confuses me now, because firstly the newly created login was for SQL02 not SQL01, and secondly the CREATE USER statement is creating a user for SMC-WG1-SQL02-Login, which is what I would expect, but it’s not what the comment is saying.

    Step 4 also has the same but in reverse.

    • Warwick Rudd says:

      Hi John, Great question. I definitely can understand your confusion. In Step 3 we are working on the Cluster Node – SMC-WG1-SQL01 and we are creating a Login called SMC-WG1-SQL02-Login on SMC-WG1-SQL01. We are then creating the subsequent user – SMC-WG1-SQL02-User for the SMC-WG1-SQL02-Login. And vice versa in Step 4. I have updated the comment to change it from “Login for SMC-WG1-SQLxx” to “Login on Server SMC-WG1-SQLxx”. Thanks

  2. Marc Dammers says:

    Hi,

    Thank you for this post. One question: can I use the same login and password for all replicas involved in a distributed AG? We are about to standup two 3 node clusters and that would mean 5 logins for each instance.

  3. Nicolas says:

    Hi Warwick,
    great article!
    Do you know any article for the scenario where your SQL Server 2016 AlwaysOn cluster is part of the domain and you want to use a wildcard certificate (e.g. *.mydomain.com)

  4. Nicolas says:

    Hi Warwick,
    great article!
    Do you know any article for the scenario where your SQL Server 2016 AlwaysOn cluster is part of the domain and you want to use a wildcard certificate (e.g. *.mydomain.com)
    Best regards

  5. Nagesh says:

    Thanks for the Article, Can you please let me know how to configure Distributed AG between two different Domains?

Leave a Reply

Your email address will not be published. Required fields are marked *

Warwick

I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.

Search