Error SQL71627 exporting BACPAC file in Azure SQL Database

  • Thread starter Thread starter RobertoYonekawa
  • Start date Start date
R

RobertoYonekawa

We had a support request where the customer was getting an error when trying to export his Azure SQL Database to a bacpac file using the SqlPackage command-line utility.

Error message:

Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2024-08-21T16:10:56 : Microsoft.SqlServer.Dac.DacServicesException: One or more unsupported elements were found in the schema used as part of a data package.​

Error SQL71627: The element Permission has property Permission set to a value that is not supported in Microsoft Azure SQL Database v12.​


The root cause was the existence of a "RECEIVE" type of permission that existed in the database on object QueryNotificationErrorsQueue. The presence of this permission was causing the error during database export.

This permission can be found querying sys.database_permissions. For example, using a query like this:

Code: 
SELECT pr.principal_id
    ,pr.name [PrincipalName]
    ,pr.type_desc
    ,pr.authentication_type_desc
    ,pe.state_desc
    ,pe.permission_name
    ,s.name + '.' + o.name AS ObjectName
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects AS o ON pe.major_id = o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE PE.permission_name='RECEIVE'

RobertoYonekawa_0-1728449244269.png



Solution:
To fix the issue, this permission needs to be removed: REVOKE RECEIVE ON QueryNotificationErrorsQueue TO [UserName]
After removing that permission, bacpac export was successful.

In this case, the customer did not know where this permission originated from. It is possible that the database was being used with query notification and Service Broker on an on-premises environment sometime in the past. This permission is mentioned in this link: Query Notification Permissions

The same error happens for database exports to bacpac file via SSMS and also in the Azure Portal import/export service.
Olders versions of SSMS (version 17.9.1 for example) seem to not be affected and do not generate this error.

Continue reading...
 
Back
Top