How To: Migrate Encrypted Procedures To Azure SQL Database

Here’s the easy answer: you don’t.

Azure SQL Database does not support the WITH ENCRYPTION option for migrating objects such as stored procedures, user defined functions, triggers, or views. Therefore, migrating objects compiled with that option is not possible. You will need to remove the WITH ENCRYPTION option.

Migrate Encrypted Procedures Error Message

If your database has encrypted objects and you try to migrate encrypted procedures to Azure SQL Database you will see this error message:

“Validation of the schema model for data package failed. Error SQL71564: The element cannot be deployed as the script body is encrypted.”

Migrate Encrypted Procedures To Azure SQL Database

To unencrypt this procedure you have three options: you can use a 3rd party tool, find various scripts through a quick internet search, or take the source code and issue an ALTER command without the encryption option.

I prefer the latter option. Just take the source code and remove the WITH ENCRYPTION statement. Doing so will allow for you to migrate the objects to Azure SQL Database without error.

Summary

I would also like to remind you that WITH ENCRYPTION does not encrypt anything. It obfuscates the object code, nothing more. A better name would be WITH OBFUSCATION, but that probably wouldn’t be clear for most end users. If you have a requirement to encrypt your source code you should go back and ask for more information as to why it is necessary. Chances are the WITH ENCRYPTION option is not meeting the original requirements.

After you update the object you’ll be ready to complete your migration to Azure SQL Database. You can then apply the WITH ENCRYPTION option to your objects.

At the very least, you’ll get past that error message!

4 thoughts on “How To: Migrate Encrypted Procedures To Azure SQL Database”

  1. Actually, there is a perfectly fine way of de-crypting the stored procedures for free by unsing the DAC (dedicated admin connection) in SQL Server.

    IMHO, encrypting stored procedures in SQL Server was a very silly idea to begin with, since it does not help anyone, especially people who do performance tuning. The only good reason for WITH ENCRYPTION I can think of is when a company releases a product based on SQL Server and when they want to ensure that the average user does not tamper with the code.

    Feodor

    Reply
    • Feoder,

      Yes, there are scripts available that use the DAC to decrypt, I mentioned that in my post. No one needs to purchase a tool, but purchasing a tool is also an option.

      I agree that the WITH ENCRYPTION option is not very useful.

      Tom

      Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.