How To: Migrate Encrypted Procedures To Windows Azure SQL Database (WASD)


08 May How To: Migrate Encrypted Procedures To Windows Azure SQL Database (WASD)

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

WASD does not support the WITH ENCRYPTION option for objects such as stored procedures, user defined functions, triggers, or views. So the idea of migrating an object that was compiled with that option is simply not feasible.

If you have a system with encrypted objects and you try migrating to WASD you will get “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 proc 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.

I would also like to remind you that WITH ENCRYPTION does not encrypt anything. It obfuscates the object code, nothing more. It really should be named 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 WASD. At the very least, you’ll get past that error message!

One Pingback/Trackback

  • Pingback: How To: Migrate Encrypted Procedures To Windows Azure SQL Database (WASD) - SQL Server - SQL Server - Toad World()

  • Feodor Georgiev

    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.


    • ThomasLaRock


      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.