Microsoft SQL Server
Configure your Microsoft SQL Server to use a Luna Cloud HSM Service to provide full key life-cycle management with FIPS-certified hardware and to reduce the cryptographic load on the host server CPU.
We recommend you use the Luna Cloud HSM for Microsoft SQL Server service for this integration.
The integration testing identified an issue in TDE when encrypting the DEK using an RSA_4096 key. The issue has been reported to Microsoft technical support and we are awaiting a resolution. At this time, we recommend using a maximum RSA key length of 3072-bit.
We recommend you familiarize yourself with SQL server operations and basic HSM concepts to make full use of the integration.
These integration guides use the following third party applications:
- Microsoft SQL
This integration is supported on the following operating systems:
- Windows Server 2016
About Microsoft SQL
Microsoft SQL Server is a database platform for large-scale online transaction processing (OLTP), data warehousing, and e-commerce applications; it is also a business intelligence platform for data integration, analysis, and reporting solutions.
The following diagram demonstrates the relationship between the database master key and the HSM devices in a Microsoft SQL configuration:
About Microsoft SQL Server High Availability (Always On)
The Always On Availability Groups feature is a high-availability and disaster recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, Always On Availability Groups maximize the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or backup operations.
The Luna Cloud HSM Service Client and Luna EKM must be setup on all nodes in the SQL server configuration that will be added to the Always On availability group. All nodes must be registered with the same Luna Cloud HSM Service Client on the Luna Cloud HSM Service.
Prerequisites
Before proceeding with the integration complete the following:
Microsoft SQL Server uses CKM_RSA_PKCS
mechanism for encryption. CKM_RSA_PKCS
is not available on Luna Cloud HSM Services operating in FIPS mode. Use a non FIPS Luna Cloud HSM Service for integrating with Microsoft SQL Server.
Provision Luna Cloud HSM Service
Configure the Luna Cloud HSM service for your application integration. See the section Luna Cloud HSM Service for detailed instructions on deploying and initializing a Luna Cloud HSM service partition and Luna Cloud HSM service client for your application integration.
Please take the following limitations into consideration when integrating your application with a Luna Cloud HSM service partition:
Non-FIPS algorithms: Luna Cloud HSM services operate in a FIPS and non-FIPS mode, which affects which algorithms are available on the partition. If your organization requires non-FIPS algorithms for your operations, ensure you enable the Remove FIPS restrictions check box when configuring your Luna Cloud HSM service. The FIPS mode is enabled by default.
Refer to the Supported Mechanisms in the SDK Reference Guide for more information about available FIPS and non-FIPS algorithms.
Verify Luna Cloud HSM <slot> value: LunaCM commands work on the current slot. If there is only one slot, then it is always the current slot. If you are completing an integration using Luna Cloud HSM services, you need to verify which slot on the Luna Cloud HSM service you send commands to.
If there is more than one slot, then use the slot set command to direct a command to a specified slot. You can use slot list to determine which slot numbers are in use by which Luna Cloud HSM service.
Set up Luna EKM
Luna EKM includes a command line configuration utility LunaEKMConfig that is used to register the Luna EKM for Microsoft SQL.
The Luna EKM utility is not included in the Luna Cloud HSM service client package. You need to contact Customer Support to gain access to the utility, or download the utility from the customer support portal.
EKM 1.5 KB0023244.
EKM 1.4 KB0016274
If you are using Luna EKM 1.2. or Luna EKM 1.3, see Configuring Luna EKM 1.2/1.3 for Luna Cloud HSM.
See Configuring Luna EKM 1.4 for Luna Cloud HSM for 1.4 configuration requirements.
Configuring Luna EKM 1.2/1.3 for Luna Cloud HSM
RegisterSlot
- register or edit the slot for the LunaEKM to use.ViewSlots
- View a list of the slots and the HSM configured with this client.LogSettings
- Configure log settings for LunaEKMLogLevel (NONE=0,INFO=1,DEBUG=2): <LogLevel> LogFile name: <Name and location of LogFile>
Configuring Luna EKM 1.4 for Luna Cloud HSM
Execute the
RegisterSlot
command. This command generates theLunaEKMConfigi.ini
file.-
Verify the Luna Cloud HSM service slot number in
lunacm
. Openlunacm
, on connection theSlot ID
for the service will be listed underAvailable HSMs:
. Alternatively, you can executeslot list
inside oflunacm
to list this information. You will receive output similar to the following:Available HSMs: Slot Id -> 3 Label -> Serial Number -> <HSM_serial_number> Model -> Cryptovisor7 Firmware Version -> 7.1.3 CV Firmware Version -> 1.1.0 Configuration -> Luna User Partition With SO (PW) Signing With Cloning Mode Slot Description -> User token slot Current Slot ID: 3
- Open the
LunaEKMConfig.init
file in a text editor. TheLunaEKMConfig.init
file is available in the LunaEKM installation directory. -
Update the
SlotID=
value under[Server]
to point to theSlot ID
output fromlunacm
. The following is an example[Server]
section in theLunaEKMConfig.init
file.[Server] SlotID=3 [Logger] LogLevel=2 LogFile=.\lunaekm.log
Set up SQL Server
SQL Server must be installed on the target machine to carry out the integration process. If you are configuring an Always On availability group all nodes in the configuration require WSFC.
If using a Windows Server 2019 you require the CU2 KB4536075. This patch is available in the Microsoft Knowledge Base.
For a detailed installation procedure of SQL Server, refer to the Microsoft SQL Server Online Documentation.
Integration
This document contains detailed instructions and procedures to integrate Microsoft SQL Server with an HSM on Demand service.
Extensible Key Management (EKM) is disabled by default.
Enabling the Luna EKM Provider Option
To use the Luna EKM provider you must enable the Luna EKM provider option on the SQL Server. The Luna EKM software grants access to the Luna EKM Provider software in the form of the EKM library (LunaEKM.dll) to integrate the Luna Cloud HSM Service with SQL Server. The Luna EKM provider can be used if the EKM provider option is enabled on the SQL server. This feature is only available on the Enterprise, Developer, and Evaluation editions of SQL server.
Use the sp_configure command to enable the Luna EKM provider option on both the SQL server nodes. The following is an example procedural set on enabling the Luna EKM provider option.
Open the SQL Server Management Studio.
Connect to the SQL Server.
Open a Query window and execute the following:
The sp_configure command is supported on Enterprise, Developer, and Evaluation editions of the SQL server. If you execute the command on an alternative version of SQL server, the command will fail and you will receive an error.
sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
sp_configure 'EKM provider enabled', 1
GO
RECONFIGURE
GO
The sp_configure command can only be run in an Enterprise, Developer or Evaluation edition of MS SQL.
Registering the Luna EKM Provider Option
To setup the Luna EKM provider, Luna EKM Software must be installed and needs to be registered with the SQL Server. Follow the steps below to register the provider on all SQL Server nodes.
Create a new cryptographic provider file that will use the Luna EKM provider.
Open the SQL Server Management Studio.
Connect to the SQL Server.
Open a Query window and execute the following:
CREATE CRYPTOGRAPHIC PROVIDER <name_of_cryptographic_provider>
FROM FILE = '<location_of_LUNA_EKM_Provider_Library>'
If you encounter the error Cannot initialize cryptographic provider. Provider error code: 1. (Failure - Consult EKM provider for details)
, you need to switch the Server Authentication type to Windows Authentication. Right click on the DB server and select Properties and click the Security tab. Change the Server Authentication type from SQL & Windows Authentication to Windows Authentication.
View the list of EKM providers:
SELECT [provider_id]
[name]
,[guid]
,[version]
,[dll_path]
,[is_enabled]
FROM [model].[sys].[cryptographic_providers]
View the provider properties:
SELECT [provider_id],[guid],[provider_version]
,[sqlcrypt_version]
,[friendly_name]
,[authentication_type]
,[symmetric_key_support]
,[symmetric_key_persistance]
,[symmetric_key_export]
,[symmetric_key_import]
,[asymmetric_key_support]
,[asymmetric_key_persistance]
,[asymmetric_key_export]
,[asymmetric_key_import]
FROM [master].[sys].[dm_cryptographic_provider_properties]
Setting up the Credential for Luna EKM Provider
You must create a CREDENTIAL for the Luna EKM provider. You must then map that CREDENTIAL to the SQL User or login to use the Luna EKM provider option. Follow the steps below to create the CREDENTIAL and map it to the SQL User on all SQL Server nodes.
A CREDENTIAL is used to access external SQL Server resources such as the HSM on Demand service. In Luna EKM 5.1, there is added support for the crypto user of the partition. The IDENTITY value must use the prefix CU_
for crypto user. If no prefix is specified the crypto officer role will be used by default for login to the HSM partition.
Create and map the CREDENTIAL to the Luna EKM Provider.
Open a Query window and execute the following to create the CREDENTIAL:
The IDENTITY
value must use the prefix CU_
for the crypto user role.
CREATE CREDENTIAL <name_of_credential>
WITH IDENTITY='<EKM_user>', SECRET='<service_crypto_officer_password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider
Create a credential for the crypto officer (CO).
CREATE CREDENTIAL EKMCredential
WITH IDENTITY='EKMUser', SECRET='userpin1'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider
Create a credential for the crypto user (CU).
CREATE CREDENTIAL EKMCredential
WITH IDENTITY='CU_EKMUser', SECRET='userpin2'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider
Map the Luna EKM Credential with SQL User or Login.
ALTER LOGIN [Domain\Login Name]
ADD CREDENTIAL <name_of_credential>
The EKM session must be reopened if the user changes the Luna Cloud HSM Service, the client machine is deleted from the Luna Cloud HSM Service, or the machine suffers a network disconnection.
Using the Luna EKM Provider Option
The Luna EKM provider can be used to create/drop symmetric and asymmetric keys to/from the service and can perform encryption/decryption using these keys. The following types of encryption keys can be created on the Luna Cloud HSM Service from the SQL server:
- AES_128
- AES_192
- AES_256
Create symmetric keys using the Luna EKM Provider
You can create symmetric keys by executing the following command from the SQL Query window. In the following example the AES algorithm is used to generate a symmetric key.
CREATE SYMMETRIC KEY SQL_EKM_AES_256_Key
FROM Provider LunaEKMProvider
WITH ALGORITHM = AES_256,
PROVIDER_KEY_NAME = 'EKM_AES_256_Key',
CREATION_DISPOSITION=CREATE_NEW
Once a key is created on the Luna Cloud HSM Service, it can be accessed, by name, from the SQL Server. In the above example, SQL_EKM_AES_256_Key
is the unique name of the key in the SQL Server. Using this key name will use the key on the HSM for encrypt and decrypt operations.
View symmetric keys using the Luna EKM Provider
You can view symmetric keys by executing the following command from the SQL Query window.
SELECT * FROM [master].[sys].[symmetric_keys]
Encrypt database tables with symmetric keys using the Luna EKM Provider
You can encrypt tables in the database using the Luna EKM Provider key stored on the HSM on Demand service.
Create a test table in the database with fields. For example:
Create Table test(
id numeric(10),
name varchar (50),
data varchar (max),)
Execute the following command to insert encrypted content into the table.
INSERT INTO dbo.test
values( 1,'some text',
EncryptByKey(Key_GUID('SQL_EKM_AES_256_Key'), <text_to_be_encrypted>))
Decrypt with symmetric keys using the Luna EKM Provider
You can decrypt tables in the database using the Luna EKM Provider key stored on the HSM on Demand service. To decrypt the table contents that were encrypted in the last procedural set, open an SQL Query window and execute the following:
SELECT id,name,CONVERT(varchar(MAX),
DecryptByKey(data))
FROM dbo.test where id =1
If you are configuring an Always On availability group, execute the above command on the secondary node and verify the output is the same as the output received on the primary node.
Drop symmetric keys using the Luna EKM Provider
You can drop symmetric keys using the Luna EKM Provider. The following example command will drop the keys from both the SQL Server and the Luna Cloud HSM Service.
DROP SYMMETRIC KEY SQL_EKM_AES_256_Key REMOVE PROVIDER KEY
We do not recommend dropping keys when operating in an Always On availability group or when running Always Encrypted
Asymmetric Keys on a Luna Cloud HSM Service
The following types of asymmetric keys can be generated on the HSM on Demand service using the SQL Server:
- RSA_2048
- RSA_3072
- RSA_4096
Create asymmetric keys using the Luna EKM Provider
You can create asymmetric keys by executing the following command from the SQL Query window. In the following example the RSA_2048 algorithm is used to generate an asymmetric key.
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key',
CREATION_DISPOSITION=CREATE_NEW
Once a key is created on the HSM on Demand service, it can be accessed, by name, from the SQL Server. In the above example, SQL_EKM_RSA_2048_Key is the unique name of the key in the SQL Server. Using this key name will use the key on the HSM for encrypt and decrypt operations.
View asymmetric keys using the Luna EKM Provider
You can view asymmetric keys by executing the following command from the SQL Query window.
SELECT \* FROM [master].[sys].[asymmetric_keys]
Encrypt with asymmetric keys using the Luna EKM Provider
You can encrypt tables in the database using the Luna EKM Provider key stored on the HSM on Demand service.
Create a test table in the MASTER database with fields. For example:
Create Table test(
id numeric(10),
name varchar (50),
data varchar (max),)
Execute the following command to encrypt content and insert it into the table.
INSERT INTO dbo.test
values( 1,'some text',
EncryptByAsymKey(AsymKey_ID('SQL_EKM_RSA_2048_Key'), '<text_to_be_encrypted>'))
Decrypt with asymmetric keys using the Luna EKM Provider
You can decrypt tables in the database using the Luna EKM Provider key stored on the HSM on Demand service. To decrypt the table contents that were encrypted in the last procedural set, open an SQL Query window and execute the following:
SELECT id,name,CONVERT (varchar (MAX),
DecryptByAsymKey (AsymKey_Id ('SQL_EKM_RSA_2048_Key'), data))
FROM dbo.test where id =1
If you are configuring an Always On availability group, execute the above command on the secondary node and verify the output is the same as the output received on the primary node.
Drop asymmetric keys using the Luna EKM Provider
You can drop symmetric keys using the Luna EKM Provider. The following example command will drop the keys from both the SQL Server and the HSM on Demand service.
DROP ASYMMETRIC KEY SQL_EKM_RSA_2048_Key REMOVE PROVIDER KEY
We do not recommend dropping keys when operating in an Always On availability group or when running Always Encrypted.
Creating Symmetric Key Encrypted by Asymmetric Key on a Luna Cloud HSM Service
You can encrypt the symmetric keys shared by the nodes in the standalone or cluster configuration using an asymmetric key. This increases the security of the symmetric key.
Execute the following command from an SQL Query window.
Create SYMMETRIC KEY key1
WITH ALGORITHM = AES_256
ENCRYPTION BY Asymmetric Key SQL_EKM_RSA_2048_Key;
SQL_EKM_RSA_2048_Key is an existing asymmetric key on the HSM on Demand service. For more information about generating the asymmetric key see Asymmetric Keys on a Luna Cloud HSM Service.
Open the symmetric key.
OPEN SYMMETRIC KEY key1 DECRYPTION BY Asymmetric Key SQL_EKM_RSA_2048_key;
Create a test table in the MASTER database with fields.
Create Table test(
id numeric(10),
name varchar (50),
data varchar (max),)
Encrypt data using the symmetric key.
INSERT INTO dbo.test
values ( 1,'some text',
Encryptbykey(KEY_GUID('key1'),'<text_to_be_encrypted>'))
Decrypt the data using the symmetric key.
SELECT id,name,CONVERT(varchar(MAX),
DecryptByKey(data))
FROM dbo.test where id =1
Close the symmetric key.
CLOSE SYMMETRIC KEY key1
If you are configuring an Always On availability group, execute steps [2]-[5] on the secondary replica and verify that the output is the same as the output on the primary replica.
Enabling Transparent Data Encryption using Asymmetric Key on Luna Cloud HSM Service
You can enable Transparent Data Encryption(TDE) using an asymmetric key stored on the HSM on Demand service.
Create an asymmetric key using the LunaEKM provider.
Use master;
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE_Rot',
CREATION_DISPOSITION=CREATE_NEW
Create a CREDENTIAL for Luna EKM provider
CREATE CREDENTIAL <name_of_credential>
WITH IDENTITY='<EKM_user>', SECRET='<service_password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider
Create a login based on the recently created asymmetric key.
CREATE LOGIN <Name of login>
FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;
Map the recently created credential to the recently created login.
ALTER LOGIN <Name of Login>
ADD CREDENTIAL <Name of credential>;
Create a database encryption key.
Database encryption operations cannot be executed on master, model, tempdb, msdb, or resource databases.
CREATE DATABASE TDE;
Use tde;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_@)$*_KEY_TDE;
Enable Transparent Data Encryption
ALTER DATABASE TDE
SET ENCRYPTION ON;
Query the status of database encryption and the completion percentage.
SELECT DB_NAME (e.database_id) AS DatabaseName,
e.database_id,
e.encryption_state,
CASE e.encryption_state
WHEN 0 THEN 'No database encryption is present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc,
c.name,
e.percent_complete
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.asymmetric_keys AS c
ON e.encryptor _thumbprint = c.thumbprint
Rotating Keys for Transparent Data Encryption
We recommend updating your Transparent Data Encryption security keys regularly.
Generate an asymmetric key using the Luna EKM provider.
Use master;
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE_Rot',
CREATION_DISPOSITION=CREATE_NEW
If you are using configuring an Always On availability group, create the same asymmetric key using Luna EKM provider on the secondary node. If you are not configuring an Always On availability group, skip this step.
Use master;
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot
FROM Provider LunaEKMProvider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE_Rot',
CREATION_DISPOSITION=OPEN_EXISTING
Create a credential for the Luna EKM provider.
CREATE CREDENTIAL <name_of_credential>
WITH IDENTITY='<EKM_user>', SECRET='<service_password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider
Create a login based on the recently created asymmetric key.
CREATE LOGIN <Name of login>
FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;
Map the credential to the login.
ALTER LOGIN <Name of Login>
ADD CREDENTIAL <Name of credential>;
If you are configuring an Always On availability group, repeat steps [2]-[5] for all secondary nodes in the configuration. You need to create the same key, credential and login in the master database of all the secondary nodes to access the encrypted tables created in the database on the primary.
Enable Transparent Database Encryption Key Rotation.
Use tde;
ALTER DATABASE ENCRYPTION KEY
REGENERATE
WITH ALGORITHM = AES_128
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;
go
SELECT * FROM sys.dm_database_encryption_keys
go
Execute the following command to query the status of database encryption, the status of the TDE key change, and the table space encryptions percentage of completion.
SELECT DB_NAME (e.database_id) AS DatabaseName,
e.database_id,
e.encryption_state,
CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc,
c.name,
e.percent_complete
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.asymmetric_keys AS c
ON e.encryptor_thumbprint = c.thumbprint
Migrating from SQL EKM to Luna EKM
If you enable Transparent Data Encryption for any database using the SQL EKM and desire to migrate TDE from SQL EKM to Luna EKM. Previously, the database master key is generated in SQL and encrypted by a certificate or asymmetric key. Now, you can generate a new database master key encrypted by an asymmetric key generated on an HSM on Demand service.
Decrypt the database.
USE master;
ALTER DATABASE <database_name>
SET ENCRYPTION OFF;
GO
Take a backup of the database and transaction logs. When the backup is completed, restart the SQL database.
Generate an asymmetric key using the Luna EKM provider.
Use master;
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_AW
FROM Provider LunaEKMProvider
WITH ALGORITHM = RSA_2048,
PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_AW',
CREATION_DISPOSITION=CREATE_NEW
Create a credential for the Luna EKM provider.
CREATE CREDENTIAL <credential_name>
WITH IDENTITY='<EKM_user>', SECRET='<service_crypto_officer_password>'
FOR CRYPTOGRAPHIC PROVIDER LunaEKMProvider
Created a login based on the recently created asymmetric key.
CREATE LOGIN <login_name>
FROM ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_AW;
Map the credential to the login.
ALTER LOGIN <login_name>
ADD CREDENTIAL <credential_name>;
Create a database encryption key.
USE <database_name>
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_AW;
Enable Transparent Data Encryption.
ALTER DATABASE <database_name>
SET ENCRYPTION ON;
Query the status of database encryption.
SELECT DB_NAME(e.database_id) AS DatabaseName,
e.database_id,
e.encryption_state,
CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc,
c.name,
e.percent_complete
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.asymmetric_keys AS c
ON e.encryptor_thumbprint = c.thumbprint
Adding an Encrypted Database to the Availability Group
If you are configuring an Always On availability group, you need to complete this procedural set for each secondary node in the availability group.
We recommend taking a complete backup of the already encrypted database. Copy the file to a shared location that is accessible by all the secondary nodes.
Open the SMS on the primary node.
Add the database to the availability group.
use master;
ALTER AVAILABILITY GROUP <availability_group> ADD DATABASE <database_name>;
GO
The above command adds the database to the availability group, but it is still unavailable on the secondary replica. To synchronize the database you must restore the database on the secondary replica.
On the secondary replica, restore the database from the backup location using the 'RESTORE WITH NORECOVERY' option.
Add the database to the secondary replica.
use master;
ALTER DATABASE <database_name> SET HADR AVAILABILITY GROUP = <availability_group>;
Execute the following command to query the status of database encryption
SELECT DB_NAME (e.database_id) AS DatabaseName,
e.database_id,
e.encryption_state,
CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc,
c.name,
e.percent_complete
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.asymmetric_keys AS c
ON e.encryptor_thumbprint = c.thumbprint