• 
  The migration process is split into several parts and requires the use of a staging SQL Server instance, this is currently FRAASAZUREU1.DE.DB.COM

• The first step is to grab a backup of the database you want to migrate and restore it into the Staging server.

• We next run the DMA tool against it to see if it is compatible for migration into an Azure Managed SQL Instance, if not we look at what needs to be done to make it compatible

• (OPTIONAL) Before we upload the database(s) into the Azure cloud they all need to be encrypted using TDE encryption and below are the steps needed to do this:

If its not already been done then you need to do parts 1-3 in the following guide, https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault?view=sql-server-2017. All these steps should be done but if this is anew staging server then we need to download and install SQL Server Connector for Azure Key Vault https://www.microsoft.com/en-us/download/details.aspx?id=45344

Part 4 of the above guide may also have been done you can check for this by having a look for the credential sysadmin_ekm_cred, if its not present then please do the following steps:

1. 1. Configure SQL Server to use EKMExecute the following Transact-SQL script to configure the Database Engine to use an EKM provider.– Enable advanced options.  USE master; GO 
      sp_configure ‘show advanced options’, 1; GO RECONFIGURE; GO 
      — Enable EKM provider sp_configure ‘EKM provider enabled’, 1; GO RECONFIGURE;
   2. Register (create) the SQL Server Connector as an EKM provider with SQL Server— Create a cryptographic provider, using the SQL Server Connector, which is an EKM provider for the Azure Key Vault.
      This example uses the name AzureKeyVault_EKM_Prov.
      CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll'; GO  
   3. Setup a SQL Server credential for a SQL Server login to use the key vaultA credential must be added to each login that will be performing encryption using a key from the Key Vault. This might include:
      • A SQL Server administrator login who will use key vault in order to setup and manage SQL Server encryption scenarios.
      • Other SQL Server logins who might enable Transparent Data Encryption (TDE), or other SQL Server encryption features.
      There is one-to-one mapping between credentials and logins. That is, each login must have a unique credential.Modify the Transact-SQL script below in the following ways:
      • Edit the IDENTITY argument (ContosoDevKeyVault) to point to your Azure Key Vault.
        • If you’re using global Azure, replace the IDENTITY argument with the name of your Azure Key Vault from Part II.
        • If you’re using a private Azure cloud (ex. Azure Government, Azure China, or Azure Germany), replace the IDENTITYargument with the Vault URI that is returned in Part II, step 3. Do not include “https://” in the Vault URI.
      • Replace the first part of the SECRET argument with the Azure Active Directory Client ID from Part I. In this example, the Client ID is EF5C8E094D2A4A769998D93440D8115D. ImportantYou must remove the hyphens from the Client ID.
      • Complete the second part of the SECRET argument with Client Secret from Part I. In this example the Client Secret from Part 1 is Replace-With-AAD-Client-Secret. The final string for the SECRET argument will be a long sequence of letters and numbers, with no hyphens.
      
      USE master; CREATE CREDENTIAL sysadmin_ekm_cred WITH IDENTITY = 'dev-kv-dbaas-db-0001', SECRET = 'EF5C8E094D2A4A769998D93440D8115DReplace-With-AAD-Client-Secret' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov; -- Add the credential to the SQL Server administrator's domain login ALTER LOGIN [dbg\olaftor-a] ADD CREDENTIAL sysadmin_ekm_cred;
   4. Open your Azure Key Vault key in SQL ServerIf you imported an asymmetric key as described in Part II, open the key by providing your key name in the following Transact-SQL script.
      • Replace CONTOSO_KEY with the name you’d like the key to have in SQL Server.
      • Replace ContosoRSAKey0 with the name of your key in Azure Key Vault.
      
      CREATE ASYMMETRIC KEY CONTOSO_KEY  FROM PROVIDER [AzureKeyVault_EKM_Prov] WITH PROVIDER_KEY_NAME = 'ContosoRSAKey0', CREATION_DISPOSITION = OPEN_EXISTING;
   5. Create a SQL ServerSQL Server login for the Datenbank-EngineDatabase Engine for TDECreate a SQL Server login and add the credential from Step 1 to it.
      USE master; -- Create a SQL Server login associated with the asymmetric key -- for the Database engine to use when it loads a database -- encrypted by TDE. CREATE LOGIN TDE_Login FROM ASYMMETRIC KEY CONTOSO_KEY; GO -- Alter the TDE Login to add the credential for use by the -- Database Engine to access the key vault ALTER LOGIN TDE_Login ADD CREDENTIAL Azure_EKM_TDE_cred ; GO

!!!!There is another guide, https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/enable-tde-on-sql-server-using-ekm?view=sql-server-2017, which might also be useful if the above doesn’t work!!!!

• Now that you have completed the basic configuration in the previous step, see how to Use SQL Server Connector with SQL Encryption Features to enable encryption on the databases

Create the Database Encryption Key (DEK)

The DEK will encrypt your data and log files in the database instance, and in turn be encrypted by the Azure Key Vault asymmetric key. The DEK can be created using any SQL Server supported algorithm or key length.

1. USE ContosoDatabase; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER ASYMMETRIC KEY CONTOSO_KEY; GO
2. Turn On TDE-- Alter the database to enable transparent data encryption. ALTER DATABASE ContosoDatabase SET ENCRYPTION ON; GO

• Once the databases are encrypted they can be backed up, uploaded  and restored where the same key in Azure should be able to decrypt them.

Alternate method using master key and certificates but it’s not supported by the bank:

The first thing to do is to create a master key and certificate if they don’t already exist, this can be tested with:

select * from sys.symmetric_keys where name like ‘%DatabaseMasterKey%’;

select * from sys.certificates where pvt_key_encryption_type = ‘MK’;

If both or either of these are blank then proceed with creating the master key and/or certificate. If they are already there then just use the existing ones.

Create Master Key

We must first create the master key. It must be created in the master database, so as a precautionary measure I like to begin this statement with the USE MASTER command.

USE Master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD=’InsertStrongPasswordHere’; GO

Create Certificate protected by master key

Once the master key is created along with the strong password (that you should remember or save in a secure location), we will go ahead and create the actual certificate.

CREATE CERTIFICATE TDE_Cert WITH SUBJECT=’Database_Encryption’; GO

The certificate’s name is “TDE_Cert” and I gave it a generic subject. Some Database Administrators like to put the name of the actual database that they are going to encrypt in there. It is totally up to you.

Create Database Encryption Key

Now, we must utilize our USE command to switch to the database that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database. Then we indicate the type of encryption algorithm we are going to use. In this case it will be AES_256 encryption.

USE <DB> GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_Cert; GO

Enable Encryption

Finally, we can enable encryption on our database by using the ALTER DATABASE command.

ALTER DATABASE <DB> SET ENCRYPTION ON; GO

Once the encryption is turned on, depending on the size of the database, it may take some time to complete. You can monitor the status by querying the sys.dm_database_encryption_keys DMV.

Backup Certificate

It’s important to backup the certificate you created and store it in a secure location. If the server ever goes down and you need to restore it elsewhere, you will have to import the certificate to the server. In certain environments, the DR servers are already stood up and on warm/hot standby, so it’s a good idea to just preemptively import the saved certificate to these servers.

BACKUP CERTIFICATE TDE_Cert TO FILE = ‘C:\temp\TDE_Cert’ WITH PRIVATE KEY (file=’C:\temp\TDE_CertKey.pvk’, ENCRYPTION BY PASSWORD=’InsertStrongPasswordHere’)

Unless the certificate is already in place we now need to upload this certificate to the AZURE Managed SQL Instance with the following steps:

First we convert the previous certificate backup to a Personal Information Exchange (.pfx) file, using Pvk2Pfx tool in Powershell:

.\pvk2pfx -pvk c:/full_path/TDE_CertKey.pvk  -pi “<SomeStrongPassword>” -spc c:/full_path/TDE_Cert -pfx c:/full_path/TDE_Cert.pfx

If pvk2pfx is not present on the staging server then we need to be installed along with the relevant SDK, https://docs.microsoft.com/de-de/azure/sql-database/sql-database-managed-instance-migrate-tde-certificate

Now take the new pfx file and upload it to a blob container.