Skip to main content

 none
Import bacpac containing security policies and temporal-tables with schemabinding RRS feed

  • Question

  • I've created a bacpac backup of my database (both Azure and local with SSMS).
    Trying to import the bacpac to same or another machine (again both Azure and local) the import always fails with this error:

    ...
    Error SQL72014: .Net SqlClient Data Provider: Meldung 3729, Ebene 16, Status 4, Zeile 1 Cannot ALTER 'SecTable' because it is being referenced by object 'sec_SecTable'.
    Error SQL72045: Fehler bei der Skriptausführung. Ausgeführtes Skript:
    ALTER TABLE [dbo].[SecTable] SET (SYSTEM_VERSIONING = OFF);
    ...

    The smallest DB creation script that will reproduce this problem is:

    -- Table
    CREATE TABLE [dbo].[SecTable](
    [Id] [uniqueidentifier] NOT NULL DEFAULT (newid()),
    [SystemHistoryStart] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SystemHistoryEnd] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    [SecValue] [int] NOT NULL
     CONSTRAINT [PK_SecTable] PRIMARY KEY CLUSTERED 
    (
    [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([SystemHistoryStart], [SystemHistoryEnd])
    ) ON [PRIMARY]
    WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[SecTable_History]))
    GO

    -- Function
    CREATE FUNCTION [dbo].[sf_SchemaBoundTableFunction]
    (
        @secValue int
    )
    RETURNS TABLE
    WITH SCHEMABINDING
    RETURN
        SELECT 1 AS Result
    GO

    -- security policy
    CREATE SECURITY POLICY [dbo].[sec_SecTable] 
    ADD BLOCK PREDICATE [dbo].[sf_SchemaBoundTableFunction]([SecValue]) ON [dbo].[SecTable] BEFORE DELETE
    WITH (STATE = ON, SCHEMABINDING = ON)
    GO

    The used sql-server versions are: 14.0.3223.3 and 15.0.1800.32.

    Already tried different versions of SSMS  17.9.1 (14.0.17289.0)  and 18.2 (15.0.18142.0) and via SqlPackage.exe (Visual Studio 19 and SSMS 17/18) to execute the import/export of the bacpac.

    Any suggestions or workaround how to solve this problem or how to configure the import/export of the bacpac?
    Is it maybe a bug or am I doing something wrong?
    • Edited by AKL_prosoft Tuesday, September 17, 2019 6:54 AM Added more detailed versions of SSMS
    Wednesday, September 11, 2019 11:01 AM

All replies

  • Hi,

    I have seen similar to this.  Upgrade the version of SSMS that you use to connect to the source DB instance before taking the bacpac.  Using SSMS 18.2 should get around this issue and allow the import :-)

    Issues importing a bacpac are nearly always down to the version of SSMS being used from my experience.

    Thanks,

    Matt

    Monday, September 16, 2019 3:05 PM
  • Hi Matt,

    thanks for your answer!
    Updating the SSMS was the first thing we did.
    The versions we tried:
    17.9.1 (14.0.17289.0) 
    18.2 (15.0.18142.0)

    I will update the post with the used SSMS versions.

    Thanks,
    Alex

    Tuesday, September 17, 2019 6:52 AM