Export fails with : The element [dbo].[xxxx] cannot be deployed as the script body is encrypted

[ssreerama]

• SqlPackage or DacFx Version: Both
• .NET Framework (Windows-only) or .NET Core: Both
• Environment (local platform and source/target platforms): Any

Steps to Reproduce:

1. Create a database
2. Add below stored procedure:
   CREATE OR ALTER PROCEDURE usp_GetData WITH ENCRYPTION as begin select 1 End
3. Exporting the database fails with " Error exporting database:One or more unsupported elements were found in the schema used as part of a data package. Error SQL71564: Error validating element [dbo].[usp_GetData]: The element [dbo].[usp_GetData] cannot be deployed as the script body is encrypted."
4. Workarounds:
   i. Use /p:verifyExtraction=false
   ii. Use /p:ExtractAllTableData=true with extract instead of export

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage/SSMS/Azure Data Studio) All

[ssreerama]

SqlPackage has no ability to interact with encrypted objects. this isn't a bug, this is by-design for the encryption feature of stored procedures.
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver16#encryption

from the docs: Users who have no access to system tables or database files can't retrieve the obfuscated text. However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files.

because it is still feasible to access the definition there are 3rd party solutions that are able to reverse engineer the definition.
one such is https://www.devart.com/dbforge/sql/sqldecryptor/ (this is not an endorsement, just an example)

[ssreerama]

The workaround (probably the solution) for the problem is to remove the error causing sql object from the database and perform export-import operations and manually add them back.