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.”
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!
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
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
Hi, can you elaborate how to take the source code and remove with encryption? Thank you