sql-server-linux-create-ag-endpoints.md

title	Create availability group endpoints and certificates on Linux | Microsoft Docs
description	How to use Transact-SQL to create endpoints and certificates for availability groups on Linux.
author	MikeRayMSFT
ms.author	mikeray
manager	jhubbard
ms.date	12/4/2017
ms.topic	article
ms.prod	sql-non-specified
ms.prod_service	database-engine
ms.service
ms.component	linux
ms.suite	sql
ms.custom
ms.technology	database-engine
ms.workload	On Demand

Create availability group endpoints and certificates for Linux

[!INCLUDEtsql-appliesto-sslinux-only]

An availability group (AG) uses TCP endpoints for communication. Under Linux, endpoints for an AG are only supported if certificates are used for authentication. This means that the certificate from one instance must be restored on all other instances that will be replicas participating in the same AG. The certificate process is required even for a configuration-only replica.

Creating endpoints and restoring certificates can only be done via Transact-SQL. You can use non-SQL Server-generated certificates as well. You will also need a process to manage and replace any certificates that expire.

Important

If you plan to use the SQL Server Management Studio wizard to create the AG, you still need to create and restore the certificates by using Transact-SQL on Linux.

For full syntax on the options available for the various commands (such as additional security), consult:

• BACKUP CERTIFICATE
• CREATE CERTIFICATE
• CREATE ENDPOINT

Note

Although you will be creating an availability group, the type of endpoint uses FOR DATABASE_MIRRORING, because some underlying aspects were once shared with that now-deprecated feature.

This example will create certificates for a three-node configuration. The instance names are LinAGN1, LinAGN2, and LinAGN3.

1. Execute the following on LinAGN1 to create the master key, certificate, and endpoint, as well as back up the certificate. For this example, the typical TCP port of 5022 is used for the endpoint.

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPassword>';
   
   GO
   
   CREATE CERTIFICATE LinAGN1_Cert
   WITH SUBJECT = 'LinAGN1 AG Certificate';
   
   GO
   
   BACKUP CERTIFICATE LinAGN1_Cert
   TO FILE = '/var/opt/mssql/data/LinAGN1_Cert.cer';
   
   GO
   
   CREATE ENDPOINT AGEP
   STATE = STARTED
   AS TCP (
       LISTENER_PORT = 5022,
       LISTENER_IP = ALL)
   FOR DATABASE_MIRRORING (
       AUTHENTICATION = CERTIFICATE LinAGN1_Cert,
       ROLE = ALL);
   
   GO

2. Do the same on LinAGN2:

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPassword>';
   
   GO
   
   CREATE CERTIFICATE LinAGN2_Cert
   WITH SUBJECT = 'LinAGN2 AG Certificate';
   
   GO
   
   BACKUP CERTIFICATE LinAGN2_Cert
   TO FILE = '/var/opt/mssql/data/LinAGN2_Cert.cer';
   
   GO
   
   CREATE ENDPOINT AGEP
   STATE = STARTED
   AS TCP (
       LISTENER_PORT = 5022,
       LISTENER_IP = ALL)
   FOR DATABASE_MIRRORING (
       AUTHENTICATION = CERTIFICATE LinAGN2_Cert,
       ROLE = ALL);
   
   GO

3. Finally, perform the same sequence on LinAGN3:

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongPassword>';
   
   GO
   
   CREATE CERTIFICATE LinAGN3_Cert
   WITH SUBJECT = 'LinAGN3 AG Certificate';
   
   GO
   
   BACKUP CERTIFICATE LinAGN3_Cert
   TO FILE = '/var/opt/mssql/data/LinAGN3_Cert.cer';
   
   GO
   
   CREATE ENDPOINT AGEP
   STATE = STARTED
   AS TCP (
       LISTENER_PORT = 5022,
       LISTENER_IP = ALL)
   FOR DATABASE_MIRRORING (
       AUTHENTICATION = CERTIFICATE LinAGN3_Cert,
       ROLE = ALL);
   
   GO

4. Using scp or another utility, copy the backups of the certificate to each node that will be part of the AG.

   For this example:

   • Copy LinAGN1_Cert.cer to LinAGN2 and LinAGN3
   • Copy LinAGN2_Cert.cer to LinAGN1 and LinAGN3.
   • Copy LinAGN3_Cert.cer to LinAGN1 and LinAGN2.

5. Change ownership and the group associated with the copied certificate files to mssql.

   sudo chown mssql:mssql <CertFileName>

6. Create the instance-level logins and users associated with LinAGN2 and LinAGN3 on LinAGN1.

   CREATE LOGIN LinAGN2_Login WITH PASSWORD = '<StrongPassword>';
   CREATE USER LinAGN2_User FOR LOGIN LinAGN2_Login;
   
   GO
   
   CREATE LOGIN LinAGN3_Login WITH PASSWORD = '<StrongPassword>';
   CREATE USER LinAGN3_User FOR LOGIN LinAGN3_Login;
   
   GO

7. Restore LinAGN2_Cert and LinAGN3_Cert on LinAGN1. Having the other replicas’ certificates is an important aspect of AG communication and security.

   CREATE CERTIFICATE LinAGN2_Cert
   AUTHORIZATION LinAGN2_User
   FROM FILE = '/var/opt/mssql/data/LinAGN2_Cert.cer';
   
   GO
   
   CREATE CERTIFICATE LinAGN3_Cert
   AUTHORIZATION LinAGN3_User
   FROM FILE = '/var/opt/mssql/data/LinAGN3_Cert.cer';
   
   GO
   

8. Grant the logins associated with LinAG2 and LinAGN3 permission to connect to the endpoint on LinAGN1.

   GRANT CONNECT ON ENDPOINT::AGEP TO LinAGN2_Login;
   
   GO
   
   GRANT CONNECT ON ENDPOINT::AGEP TO LinAGN3_Login;
   
   GO

9. Create the instance-level logins and users associated with LinAGN1 and LinAGN3 on LinAGN2.

   CREATE LOGIN LinAGN1_Login WITH PASSWORD = '<StrongPassword>';
   CREATE USER LinAGN1_User FOR LOGIN LinAGN1_Login;
   
   GO
   
   CREATE LOGIN LinAGN3_Login WITH PASSWORD = '<StrongPassword>';
   CREATE USER LinAGN3_User FOR LOGIN LinAGN3_Login;
   
   GO

10. Restore LinAGN1_Cert and LinAGN3_Cert on LinAGN2.

```SQLl
CREATE CERTIFICATE LinAGN1_Cert
AUTHORIZATION LinAGN1_User
FROM FILE = '/var/opt/mssql/data/LinAGN1_Cert.cer';

GO

CREATE CERTIFICATE LinAGN3_Cert
AUTHORIZATION LinAGN3_User
FROM FILE = '/var/opt/mssql/data/LinAGN3_Cert.cer';

GO

11. Grant the logins associated with LinAG1 and LinAGN3 permission to connect to the endpoint on LinAGN2.

```SQL
GRANT CONNECT ON ENDPOINT::AGEP TO LinAGN1_Login;

GO

GRANT CONNECT ON ENDPOINT::AGEP TO LinAGN3_Login;

GO
```

12. Create the instance-level logins and users associated with LinAGN1 and LinAGN2 on LinAGN3.

```SQL
CREATE LOGIN LinAGN1_Login WITH PASSWORD = '<StrongPassword>';
CREATE USER LinAGN1_User FOR LOGIN LinAGN1_Login;

GO

CREATE LOGIN LinAGN2_Login WITH PASSWORD = '<StrongPassword>';
CREATE USER LinAGN2_User FOR LOGIN LinAGN2_Login;

GO
```

13. Restore LinAGN1_Cert and LinAGN2_Cert on LinAGN3.

```SQL
CREATE CERTIFICATE LinAGN1_Cert
AUTHORIZATION LinAGN1_User
FROM FILE = '/var/opt/mssql/data/LinAGN1_Cert.cer';

GO

CREATE CERTIFICATE LinAGN2_Cert
AUTHORIZATION LinAGN2_User
FROM FILE = '/var/opt/mssql/data/LinAGN2_Cert.cer';

GO

14. Grant the logins associated with LinAG1 and LinAGN2 permission to connect to the endpoint on LinAGN3.

```SQL
GRANT CONNECT ON ENDPOINT::AGEP TO LinAGN1_Login;

GO

GRANT CONNECT ON ENDPOINT::AGEP TO LinAGN2_Login;

GO
```