Skip to content

Unable to generate bacpac/dacpac for Managed Instance databases which include External Tables. #199

@CliveChallinor

Description

@CliveChallinor
  • SqlPackage or DacFx Version: 15.0.5120.2

  • .NET Framework (Windows-only) or .NET Core:

  • Currently installed "classic" .NET Versions in the system:
    2.0.50727.4927 Service Pack 2
    3.0.30729.4926 Service Pack 2
    3.5.30729.4926 Service Pack 1
    4.0.0.0
    4.8.09032

  • Environment (local platform and source/target platforms):

Steps to Reproduce:

  1. Create a Managed Instance in Azure

  2. Create a database in the managed instance

  3. Use the following script to generate objects within the database
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'dfgsdfgshrhhnfg_344f';
    GO
    /* specify credentials to external data source

    • IDENTITY: user name for external source.
    • SECRET: password for external source.
      */
      CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
      WITH IDENTITY = 'Clive', Secret = '';
      GO
    /* Make sure Polybase is enabled */
    
    sp_configure 'POLYBASE enable',1
    reconfigure
    go
    

    CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH (
    LOCATION = 'sqlserver://we-server.database.windows.net',
    DATABASE_NAME = N'***_NDW_TEST',
    -- PUSHDOWN = ON | OFF,
    CREDENTIAL = SQLServerCredentials,
    TYPE = RDBMS
    );
    GO

CREATE EXTERNAL TABLE [dbo].[all_dm_exec_requests]([session_id] smallint NOT NULL,
[request_id] int NOT NULL,
[start_time] datetime NOT NULL,
[status] nvarchar(30) NOT NULL,
[command] nvarchar(32) NOT NULL,
[sql_handle] varbinary(64),
[statement_start_offset] int,
[statement_end_offset] int,
[cpu_time] int NOT NULL)
WITH
(
DATA_SOURCE = SQLServerInstance,
SCHEMA_NAME = 'sys',
OBJECT_NAME = 'dm_exec_requests'
)

  1. Attempt to Extract Data Tier Application from SSMS
  2. Fails with the following TITLE: Microsoft SQL Server Management Studio

Could not extract package from specified database.
Error 0: An error occurred while attempting to reverse engineer elements of type Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlExternalDataSource: Unsupported external data source type '1'.
(Microsoft.SqlServer.Dac)


ADDITIONAL INFORMATION:

Unsupported external data source type '1'. (Microsoft.Data.Tools.Schema.Sql)


BUTTONS:

OK

Did this occur in prior versions? Yes

(DacFx/SqlPackage/SSMS/Azure Data Studio)

Metadata

Metadata

Assignees

Labels

Type

No type

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions