Rds Import Bacpac

Have you ever tried to import a BACPAC file into an RDS Microsoft Sql Server instance? Than you might experienced the same issue, that the import process was aborted and the added database was inaccessible.

To understand what causes this issue, we have to understand what a BACPAC is.

In the documentation from Microsoft they explain it like this:

A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from the database.¹

The root cause for this situation is, that during the export of the BACPAC in the metadata the name of the database owner is included.

During the import into an RDS instance, it tries to assign to the original user. But there might not exist a user named the same. This leads to the situation where the database was created and assigned to the rdsa user and your import process is aborted.

As a result you are not able to access the newly created (empty) database.

To overcome this issue, you have two options.

1. Add a user with the same name to the RDS instance.
2. Import the database locally and assign at least one of your users of your RDS instance in the permissions section of the database. Than create a BACPAC again to be imported to RDS.

TIP: When you have such a non-deletable database it is possible to get rid of it.

By resetting the RDS MSSQL master password (even if it is the same) you are able to drop the database afterwards.

1EXECUTE msdb.dbo.rds_drop_database N'<database_name>'

But remember, you might expect some unavailability of the instances during the reset process.