- sqlpackage version: 170.4.83.3
- .NET Framework (Windows-only) or .NET Core: 4.8.09221 (release 533509)
- Environment (local platform and source/target platforms): SQL Server 2019 (RTM) 15.0.2000.5 — Developer Edition (64-bit), Windows 11 Business (Build 26200)
Description
When importing a bacpac (exported from Azure SQL) to a local SQL Server instance, SqlExternalDataSource objects that depend on a SqlDatabaseCredential are silently dropped from the execution plan. This causes any stored procedure that references the external data source via OPENROWSET(BULK ..., DATA_SOURCE = ...) to fail with error 12703 ("Referenced external data source not found").
Root cause chain
Azure SQL bacpacs contain CREATE MASTER KEY without an ENCRYPTION BY PASSWORD clause, which is valid on Azure SQL (encrypted by the service master key) but fails on local SQL Server. sqlpackage's static plan analysis:
Sees that CREATE MASTER KEY (without password) will fail on local SQL Server
Predicts that CREATE DATABASE SCOPED CREDENTIAL will also fail (error 15530 — no master key)
Removes any SqlExternalDataSource that depends on that credential from the execution plan
The prediction in step 2 is incorrect for credentials using WITH IDENTITY = 'Managed Identity'. Managed Identity credentials have no secret to encrypt, so they do not require a master key and succeed at runtime regardless. However, because CurrencyBlobData was already dropped from the plan during static analysis, it is never created, and the procedure fails.
Steps to reproduce
Create a database on Azure SQL with:
A DATABASE MASTER KEY (no password — standard on Azure SQL)
A DATABASE SCOPED CREDENTIAL with IDENTITY = 'Managed Identity'
An EXTERNAL DATA SOURCE of type BLOB_STORAGE referencing the credential
A stored procedure using OPENROWSET(BULK ..., DATA_SOURCE = '') referencing the external data source
Export the database to a bacpac
Import the bacpac to a local SQL Server instance using sqlpackage /Action:Import
Observed behavior
Error SQL72014: Msg 12703, Level 16, State 1, Procedure , Line N
Referenced external data source "" not found.
The external data source object is never created. The credential is successfully created (confirming the 15530 prediction was wrong).
Expected behavior
The external data source should be created and the stored procedure import should succeed. Since Managed Identity credentials require no master key at runtime, the dependency chain master key → credential → external data source should not cause the external data source to be dropped from the plan.
Workaround
Pre-create the database, master key, credential, and external data source before running sqlpackage. sqlpackage's differential comparison detects the objects already exist and skips those steps, while still creating the stored procedure which now finds the external data source present:
CREATE DATABASE [TargetDb];
USE [TargetDb];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeLocalPassword';
CREATE DATABASE SCOPED CREDENTIAL [MyCred] WITH IDENTITY = 'Managed Identity';
CREATE EXTERNAL DATA SOURCE [MyDataSource] WITH (
TYPE = BLOB_STORAGE,
LOCATION = 'https://...',
CREDENTIAL = [MyCred]
);
Then run sqlpackage /Action:Import targeting the pre-created database.
(DacFx/SqlPackage/SSMS/Azure Data Studio)
Description
When importing a bacpac (exported from Azure SQL) to a local SQL Server instance, SqlExternalDataSource objects that depend on a SqlDatabaseCredential are silently dropped from the execution plan. This causes any stored procedure that references the external data source via OPENROWSET(BULK ..., DATA_SOURCE = ...) to fail with error 12703 ("Referenced external data source not found").
Root cause chain
Azure SQL bacpacs contain CREATE MASTER KEY without an ENCRYPTION BY PASSWORD clause, which is valid on Azure SQL (encrypted by the service master key) but fails on local SQL Server. sqlpackage's static plan analysis:
Sees that CREATE MASTER KEY (without password) will fail on local SQL Server
Predicts that CREATE DATABASE SCOPED CREDENTIAL will also fail (error 15530 — no master key)
Removes any SqlExternalDataSource that depends on that credential from the execution plan
The prediction in step 2 is incorrect for credentials using WITH IDENTITY = 'Managed Identity'. Managed Identity credentials have no secret to encrypt, so they do not require a master key and succeed at runtime regardless. However, because CurrencyBlobData was already dropped from the plan during static analysis, it is never created, and the procedure fails.
Steps to reproduce
Create a database on Azure SQL with:
A DATABASE MASTER KEY (no password — standard on Azure SQL)
A DATABASE SCOPED CREDENTIAL with IDENTITY = 'Managed Identity'
An EXTERNAL DATA SOURCE of type BLOB_STORAGE referencing the credential
A stored procedure using OPENROWSET(BULK ..., DATA_SOURCE = '') referencing the external data source
Export the database to a bacpac
Import the bacpac to a local SQL Server instance using sqlpackage /Action:Import
Observed behavior
Error SQL72014: Msg 12703, Level 16, State 1, Procedure , Line N
Referenced external data source "" not found.
The external data source object is never created. The credential is successfully created (confirming the 15530 prediction was wrong).
Expected behavior
The external data source should be created and the stored procedure import should succeed. Since Managed Identity credentials require no master key at runtime, the dependency chain master key → credential → external data source should not cause the external data source to be dropped from the plan.
Workaround
Pre-create the database, master key, credential, and external data source before running sqlpackage. sqlpackage's differential comparison detects the objects already exist and skips those steps, while still creating the stored procedure which now finds the external data source present:
CREATE DATABASE [TargetDb];
USE [TargetDb];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeLocalPassword';
CREATE DATABASE SCOPED CREDENTIAL [MyCred] WITH IDENTITY = 'Managed Identity';
CREATE EXTERNAL DATA SOURCE [MyDataSource] WITH (
TYPE = BLOB_STORAGE,
LOCATION = 'https://...',
CREDENTIAL = [MyCred]
);
Then run sqlpackage /Action:Import targeting the pre-created database.
(DacFx/SqlPackage/SSMS/Azure Data Studio)