Drop Database Scoped Credential

Question

I have a database in Azure that I need to move to a local database.

I use the SSMS to Export a .bacpak file, and then connect to my local version of Sql Server and attempt to import it.

The import begins, but then fails on this line

Create database scoped credential...

This feature is available in Azure, but not in Sql Server 2014 (It will be in 2016).

To resolve, I went to the Azure Database and deleted the credential with SSMS.

So the credential is no longer visible in SSMS.

I then created a new export, and tried to import it. But I am getting the same error message. In other words, the script is still trying to create the credential even though it was removed from the database.

Is there something else I need to do to fully remove the Credential? I have tried the export/import several times just to make sure I wasn't importing the original file, but I am sure that is not the case. Is there a compact command or something that needs to be run to fully flush the changes to the database?

Answer 1

Check the sys.database_credentials view in Azure SQL database and remove rest of credentials (if exists) with DROP DATABASE SCOPED CREDENTIAL command.

Answer 2

For short term mitigation we provide you PowerShell script that do the following:

1. Remove the master key object from the bacpac
2. Remove the credential object from the bacpac
3. After running this PS script on the bacpac you will have new bacpac file with "patched" suffix.

Download it here

example:

C:\PS> .\RemoveMasterKey.ps1 -bacpacPath "C:\BacPacs\Test.bacpac"

Original Post: https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/03/30/exported-database-from-azure-sql-failed-to-be-imported-to-azure-sql-or-to-local-sql-server/