microsoft / microsoft/DacFx

Database Master Key not included as part of dacpac created using Extract functionality in SqlPackage

Open
#484 0 comments 0 reactions 1 assignee Claimed by @abhikum View on GitHub
bug
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

Customer incident regarding usage of SqlPackage to create a dacpac using Extract functionality with missing Master Key(MK)

- SqlPackage or DacFx Version: 162.3.566
- .NET Framework (Windows-only) or .NET Core:
- Environment (local platform and source/target platforms): azure sql db

**Steps to Reproduce:**

1. Within a database, create a table and input some data to it.

> CREATE TABLE dbo.CustomerInfo
> (CustID INT PRIMARY KEY,
> CustName VARCHAR(30) NOT NULL,
> BankACCNumber VARCHAR(10) NOT NULL
> );
> GO
>
> Insert into CustomerInfo (CustID,CustName,BankACCNumber)
> Select 6,'AAAA',111122222 UNION ALL
> Select 7, 'BBBB',222223333 UNION ALL
> Select 8, 'CCCC',3333444 UNION ALL
> Select 9,'DDDD',44444555 UNION ALL
> Select 10, 'EEEE',55556666
> select * from CustomerInfo;

3. Create a Master Key (MK), Symmetric Key and a Certificate.

> CREATE MASTER KEY ENCRYPTION BY PASSWORD = ;
>
> SELECT * FROM sys.symmetric_keys
>
> CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Column Encryption Certificate';
>
> SELECT * FROM sys.certificates;
>
> CREATE SYMMETRIC KEY SymKey_test WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCertificate;
>
> SELECT * FROM sys.symmetric_keys;

5. Use Extract command (below) with ExtractAllTableData property to create a dacpac containing table, data, Symmetric Key and the Certificate, but it doesn't include the Master Key from the source database.

> SqlPackage.exe /Action:extract /UniversalAuthentication:True /SourceConnectionString:"Server=; Initial Catalog=; MultipleActiveResultSets=False; Encrypt=True; TrustServerCertificate=False; Connection Timeout=30;" /TargetFile: /p:ExtractAllTableData=True

**Did this occur in prior versions? If not - which version(s) did it work in?**
- repros in the latest SqlPackage 162.3.566

(DacFx/SqlPackage/SSMS/Azure Data Studio)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.