A recent question from a user asked about moving data that is encrypted in a SQL Server database to an Azure SQL Database. In this case, data was encrypted with a symmetric key in a SQL Server database and needs to be moved to Azure SQL Database, where the key then needs to be created.

This is possible, and it is easy, with some caveats. The main issue is that the algorithm used to hash the passphrase in SQL Server 2017+, which includes Azure SQL Database, was changed from SHA1 to SHA2. This means that a different symmetric key is being created in SQL Server 2017 and Azure SQL Database (ASD). As a result, this article will look at moving from SQL Server 2017 to ASD. To move from SQL Server 2016-, another article is available that describes a technique.

The Basic Setup

I'll start with an on premises SQL Server 2017 database. In this database, I'll add a table with some data:

CREATE TABLE EncryptedData
(   DataKey       INT IDENTITY(1, 1) CONSTRAINT EncryptedDataKey PRIMARY KEY
  , PlainText     VARCHAR(100)
  , EncryptedText VARBINARY(MAX));
GO
INSERT dbo.EncryptedData
    (PlainText, EncryptedText)
VALUES
    ('Denver Broncos', NULL)
  , ('Denver Nuggets', NULL)
  , ('Colorado Avalanche', NULL)
  , ('Colorado Rockies', NULL);
GO

I want to encrypt this data, so I can set up a key for encryption:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AR3@llyStr0ngP@ssW0rd!'
GO
CREATE SYMMETRIC KEY MySymmetricKey
WITH ALGORITHM = AES_256,
IDENTITY_VALUE = 'MySpecial Key', KEY_SOURCE = 'Colorado Sports'
ENCRYPTION BY PASSWORD = 'Another!Password'
GO

Let's now encrypt our data. I need to open the key, and then use it to encrypt data.

OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY PASSWORD = 'Another!Password'
GO
UPDATE dbo.EncryptedData
 SET EncryptedText = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), PlainText)
 GO
SELECT top 10
ed.DataKey, ed.PlainText, ed.EncryptedText
, CAST(DECRYPTBYKEY(ed.EncryptedText) AS VARCHAR(100)) AS DecryptedText
 FROM dbo.EncryptedData AS ed
GO

Once this is done, I get these results:

I have data encrypted, so let's move it.

Uploading Data

When we move encrypted data between systems, we typically want to move it in an encrypted form. To ensure this, I'll bcp out the data, but only a portion of it. I'll skip the plaintext data and export the DataKey and EncryptedText fields. I will use this bcp command:

bcp "select DataKey, EncryptedText from dbo.EncryptedData" queryout EncryptedData.bcp -n -S Plato\SQL2017 -d Sandbox -T

This will copy out the four rows into a file. I can then use a similar command to upload this data to an Azure SQL Database. I'll first set up a table on the server:

CREATE TABLE EncryptedData
(   DataKey       INT IDENTITY(1, 1) CONSTRAINT EncryptedDataKey PRIMARY KEY
  , EncryptedText VARBINARY(MAX));
GO

Now I'll bcp in the data.

bcp dbo.EncryptedData in EncryptedData.bcp -n -S dkranchapps.database.windows.net -d ssbuilds -U sjones

This worked, as you can see. I didn't put my password in the command, but just typed it in.

I have my data, as you can see. This is the result from a query.

Now, let's see if we can read this data.

Encryption in Azure SQL Database

The process for setting up encryption is the same in Azure SQL Database, with minor caveats. We don't have a file system, so we can't create certificates or asymmetric keys from the file system. We can send in a binary, so that's not a big problem, but it is something to be aware of. In this case, we aren't using the

For symmetric keys, the process is the same, so let's run the same command:

CREATE SYMMETRIC KEY MySymmetricKey
WITH ALGORITHM = AES_256,
IDENTITY_VALUE = 'MySpecial Key', KEY_SOURCE = 'Colorado Sports'
ENCRYPTION BY PASSWORD = 'Another!Password'

This works and creates the same symmetric key that I had on my on-premises SQL Server 2017 database. This should be exactly the same key, as these are deterministic with the same parameters passed into the key. Let's test that.

I'll now run this query, which is the one I ran for my local database (minus the plaintext column).

SELECT TOP 10
       ed.DataKey
     , ed.EncryptedText
     , CAST(DECRYPTBYKEY(ed.EncryptedText) AS VARCHAR(100)) AS DecryptedText
FROM   dbo.EncryptedData AS ed;

That returns NULL for the text, but that's expected. I haven't opened the key.

I can fix that. I'll run this code:

OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY PASSWORD = 'Another!Password'
GO
SELECT TOP 10
       ed.DataKey
     , ed.EncryptedText
     , CAST(DECRYPTBYKEY(ed.EncryptedText) AS VARCHAR(100)) AS DecryptedText
FROM   dbo.EncryptedData AS ed;

Now we see the results.

This works.

Summary

This does show you that the encryption for on premises SQL Server 2017/2019+ is the same as Azure SQL Database. I can move encrypted data through BCP, SSIS, or any way, securely, and then decrypt and use this in my Azure SQL Database. Note that I didn't show encrypting the symmetric key with an asymmetric key or certificate, but those don't change this process at all. I would just need to create those objects and use those to decrypt my symmetric key.

However, this says SQL 2017/2019 for a reason. The algorithm for hashing the symmetric key parameters changed in SQL Server 2017. This is a known issue, and this means that I can make 2017 backwards compatible with a trace flag. I cannot do this in Azure SQL Database. This means I'd either need to move this data to a SQL 2017+ instance and decrypt and re-encrypt the data, or find another way.

We have an article coming that gives you one solution.