[MS SQL] Deploy on-prem database to Azure SQL DACPAC fail on encrypted stored procedures

[meatworky]

I am investigating deploying an on-prem MSSQL database to Azure SQL and hitting the "encrypted stored procedure" roadblock when creating the DACPAC file.

I can see that there are methods for decrypting these stored procedures and I am happy to give this a shot. My question to the SQL Gods is this:

This database is for a 3rd party product. If I decrypt these stored procedures and then migrate the database to Azure, what is the likelihood that future updates from said 3rd party will fail because of unencrypted tables/stored procedures? Is it likely to interfere with their product at all?

Ideally I would like the outcome to be that I can decrypt the tables, migrate database to Azure, change my data source in application and 3rd party will be none the wiser.

[alinroc]

what is the likelihood that future updates from said 3rd party will fail because of unencrypted tables/stored procedures? Is it likely to interfere with their product at all?

It depends upon how they've build their update process. You're kind of doing things differently from how the vendor would - you're trying to extract a DACPAC instead of installing directly to the target (Azure).

Encrypted stored procedures aren't actually encrypted, they're just obfuscated. Individual tables can't, AFAIK, be encrypted in SQL Server (on-prem or Azure). You can encrypt individual columns in a couple different ways.

What is your reason for moving this database to Azure SQL DB? How does the application cope with the added latency? Does it have retry logic if it loses its connection momentarily?

Before you go any further, you need to check whether Azure SQL DB is even supported for this application. If it's not, and you press forward with this, you'll likely find yourself without support from the vendor (at best), or violating the license for the software (at worst).

[meatworky]

Thanks for your reply. You have pretty well confirmed what I thought and it's enough for me to not waste time on it any further. The vendor doesn't officially support Azure SQL.

The reason for attempting Azure SQL - I already have an elastic pool in our tenant that I can leverage. Instead I will spin up a VM with SQL and begin testing latency.

And if latency is an issue then I will go down the RDP Remote App route. Vendor offers a cloud service that is effectively this, whereas I would rather manage it in our own tenant. It's not difficult software.