We received some support cases when customers encounter following error “SameKeyMaterialNotFoundOnRemoteServer” while trying to copy an Azure SQL Database to a different server when TDE is enabled using customer Managed Key.
First of all, let me explain how the Database copy works. A database copy is a transactionally consistent snapshot of the source database as of a point in time after the copy request is initiated.
As the copy is created using the geo-replication technology. Once replica seeding is complete, the geo-replication link is automatically terminated. All the requirements for using geo-replication apply to the database copy operation. Therefore, all servers linked by GeoDr should contain the same key material or key uri as the primary encryption protector of the partner server. For more information, please refer to this document.
To copy the database to a different server with TDE enabled using customer-managed key you can check the below steps:
1-The system-assigned Identity or user-assigned identity must be assigned to the target server. As when the target server does not have system-assigned identity you will be expected to receive the below error:
code": "AzureKeyVaultNoServerIdentity",
"message": "The server identity is not correctly configured on server 'serevername'."
Note: If you are using portal to enable TDE the system Assigned Identity will be automatically created but if you are using other methods to enable TDE you can run the below PowerShell command to assign an identity for your Azure SQL Server:
Set-AzSqlServer -ResourceGroupName <SQLDatabaseResourceGroupName> -ServerName <LogicalServerName> -AssignIdentity
Or you can use the portal to create a system or user assigned identity for Azure SQL Server:
2-The TDE must be enabled on the target server as the target server must have the key material of the primary server's encryption protector. As if this is not fulfilled you will receive an error similar to:
status": "Failed",
"error": {
"code": "SameKeyMaterialNotFoundOnRemoteServer",
"message": "All servers linked by Geo replication should have the same key material as the encryption protector of the primary server. Please add the key 'https://testkeyvault.vault.azure.net/keys/CMKAuto3/48c980e5c30e4f34987f3ad7b240cf5b' with the same key material to the secondary server 'target_server_name'."}}
I hope this article was helpful for you, please feel free to share your feedback in the comments section.
Microsoft
