Error Importing Azure bacpac file to local db error incorrect syntax near EXTERNAL

Question

When importing db fro azure bacpac file to local sql server 2016 I'm geting the following error.

Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'EXTERNAL'.
Error SQL72045: Script execution error.  The executed script: CREATE EXTERNAL DATA SOURCE [BoxDataSrc]
    WITH (
    TYPE = RDBMS,
    LOCATION = N'MYAZUREServer.database.windows.net',
    DATABASE_NAME = N'MyAzureDb',
    CREDENTIAL = [SQL_Credential]
    );

(Microsoft.SqlServer.Dac)

Heyerdahl

Did you ever work out how to fix this? – Ixia

Answer 1

I ran into this same issue today. Since "WITH(TYPE = RDBMS)" is only applicable to Azure SQL DB, we get the error when attempting to import the bacpac into SQL Server 2017 on-premise. I did find a solution thanks to this article:

https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/08/16/editing-a-bacpac-file/

The relevant steps rewritten here:

1. Make a copy of the bacpac file (for safety in case of errors).

2. Change the file extension to zip, then decompress it into a folder. Surprisingly, a bacpac is actually just a zip file, not something proprietary and hard to get into.

3. Find the model.xml file and edit it to remove the section that looks like this:

   <Element Type="SqlExternalDataSource" Name="[BoxDataSrc]">
       <Property Name="DataSourceType" Value="1" />
       <Property Name="Location" Value="MYAZUREServer.database.windows.net" />
       <Property Name="DatabaseName" Value="MyAzureDb" />
       <Relationship Name="Credential">
           <Entry>
               <References Name="[SQL_Credential]" />
           </Entry>
       </Relationship>
   </Element>
   

4. If you have multiple external data sources of this type, you will pobably need to repeat step 3 for each one. I only had one.

5. Save and close model.xml.

6. Now you need to re-generate the checksum for model.xml so that the bacpac doesn't think it was tampered with (since you just tampered with it). Create a PowerShell file named computeHash.ps1 and put this code into it.

   $modelXmlPath = Read-Host "model.xml file path"
   Get-FileHash -Path $modelXmlPath -Algorithm 'SHA256' | Select-Object -ExpandProperty Hash
   

7. Run the PowerShell script and give it the filepath to your unzipped and edited model.xml file. It will return a checksum value.

8. Copy the checksum value, then open up Origin.xml and replace the existing checksum, toward the bottom on the line that looks like this:

   <Checksum Uri="/model.xml">9EA0F06B282D4F42955C78A98822A31AA0ED0225CB131B8759379055A482D01F</Checksum>
   

9. Save and close Origin.xml, then select all the files and put them into a new zip file and rename the extension to bacpac.

Now you can use this new bacpac to import the database without getting the error. It worked for me, it could work for you, too.

Fonz

Great !!. it work fine. is it make any trouble in that database in future use? – Whitefly

FYI, I had to use $hasher = New-Object System.Security.Cryptography.SHA256CryptoServiceProvider to get my $hasher. – Almoner

In the powershell script, you may want to add something like Read-Host -Prompt "Press Enter to exit" at the end to keep the output visible. – Winstead

This didn't work for me, after rezipping the contents of the directory, SSMS reports an error about the contents of the file being corrupted. I tried unzipping and rezipping without changes and got the same error, as if there was some sort of header in the zip file recognised by SSMS / SqlPackage – Fuse

Still works well in 2023 – Childlike

For those having issues like @MickaëlDerriey, check out an alternative method that I posted below. Way simpler, and doesn't require actually modifying the .bacpac file at all. – Auckland

Still works well in 2024 – Lichenology

7. Enter the pathname, not just the path when prompted, or it will do nothing. The script needs the full filename, not just the path. – Marlin

Answer 2

As per @SQLDoug's answer, this can happen if your Azure SQL database has External Tables (i.e. linked tables from other databases). You can check that in SSMS here:

Addendum to accepted answer

If you delete those external tables' datasouces you'll also need to delete the SqlExternalTable elements in the model.xml file that were using those datasources too, they'll look something like this:

   <Element Type="SqlExternalTable" Name="[dbo].[DeliveryMethodsRestored]">
        <Property Name="ExternalSchemaName" Value="dbo" />
        <Property Name="ExternalObjectName" Value="DeliveryMethods" />
        <Property Name="IsAnsiNullsOn" Value="True" />
        <Property Name="IsQuotedIdentifierOn" Value="False" />
        <Relationship Name="Columns">
            <Entry>
                <Element Type="SqlSimpleColumn" Name="[dbo].[DeliveryMethodsRestored].[DeliveryMethodId]">
                    <Property Name="IsNullable" Value="False" />
                    <Relationship Name="TypeSpecifier">
                        <Entry>
          SNIP....
    </Element>

If you do a search for 'SqlExternalTable' in model.xml you'll find them all easily.

Alternative approach to solving this issue

Rather than correcting the bacpac after downloading it, the other way to deal with this is simply to remove the external tables before creating the bacpac i.e.:

1. Restore a copy of your database to a separate database
2. Delete the External Tables in the restored copy

3. Delete the External Data Sources in the restored copy

4. Create the bacpac from that restored copy
5. Delete the copy database

This approach has the advantage that you aren't creating the bacpac from the live database, which apparently 'can cause the exported table data to be inconsistent because, unlike SQL Server's physical backup/restore, exports do not guarantee transactional consistency'.

If that's something you're likely to do a that a lot you could probably write scripts to automate most of the above steps.

Racecourse

The alternative approach was easy and very straight-forward. Thank you. – Gemstone

Answer 3

Same error code with different error.

Could not import package. Warning SQL72012: The object [PreProd_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Warning SQL72012: The object [PreProd_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'OPTIMIZE_FOR_AD_HOC_WORKLOADS'.

Error SQL72045: Script execution error. The executed script: IF EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'$(DatabaseName)') BEGIN ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON; END

Solution

this blog will help to edit model.xml to remove Relationship command for OPTIMIZE_FOR_AD_HOC_WORKLOADS which is not necessary in SQL Server 2017 Instance.

https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/08/16/editing-a-bacpac-file/

Make a copy of the bacpac file (for safety in case of errors). Change the file extension to zip, then decompress it into a folder. Surprisingly, a bacpac is actually just a zip file, not something proprietary and hard to get into. Find the model.xml file and edit it to remove the section that looks like this:

The relevant steps rewritten here:

1. Make a copy of the bacpac file (for safety in case of errors).

2. Change the file extension to zip, then decompress it into a folder. Surprisingly, a bacpac is actually just a zip file, not something proprietary and hard to get into.

3. Find the model.xml file and edit it to remove the section that looks like this:

   <Relationship Name="GenericDatabaseScopedConfigurationOptions">
       <Entry>
           <References Name="[OPTIMIZE_FOR_AD_HOC_WORKLOADS]" />
       </Entry>
   </Relationship>
   

4. Remove following block from model.xml

   <Element Type="SqlGenericDatabaseScopedConfigurationOptions" Name="[OPTIMIZE_FOR_AD_HOC_WORKLOADS]">
       <Property Name="GenericValueType" Value="2" />
       <Property Name="GenericValue" Value="ON" />
   </Element>
   

5. Save and close model.xml.

6. Now you need to re-generate the checksum for model.xml so that the bacpac doesn't think it was tampered with (since you just tampered with it). Create a PowerShell file named computeHash.ps1 and put this code into it.

7. Run the PowerShell script and give it the filepath to your unzipped and edited model.xml file. It will return a checksum value.

8. Copy the checksum value, then open up Origin.xml and replace the existing checksum.

9. Save and close Origin.xml, then select all the files and put them into a new zip file and rename the extension to bacpac.

Now bacpack file will is ready to import and it work for me.

Thanks.

Whitefly

Answer 4

I was facing same issue while import Azure SQL .bacpac file ( which had external data source created ) into on-prem SQL 2019 using latest SSMS (19.1)

External data source support is provided in SQL 2022 , hence after upgrading to SQL 2022 from SQL 2019 the issue got resolved

Therefore always use latest SSMS while importing bacpac and check if Azure SQL features are being supported in on-prem SQL server or not

Lambdoid

Answer 5

For those following SQLDoug's answer and receiving the dreaded File contains corrupted data after after updating the Checksum value in Origin.xml, there is a simpler way that doesn't require modifying the .bacpac file at all.

1. Rename your .bacpac to .zip
2. Open your .zip file and copy the model.xml file to a local directory
3. Modify the copied model.xml file as required.
4. If you don't already have it installed, get yourself a copy of sqlpackage.exe from here. Simplest way to install the latest version is simply running

dotnet tool install -g microsoft.sqlpackage

5. Now, simply execute:

sqlpackage /Action:Import /SourceFile:”blahblahblah.bacpac” /TargetDatabaseName:TargetDBName /TargetServerName:localhost /TargetUser:sa /TargetPassword:SQLPassword /ModelFilePath:”model.xml” /TargetTrustServerCertificate:True

6. The restore will execute without having to deal with any "corrupt file" issues or the possible delays decompressing/compressing the contents of the .bacpac file.

Auckland

Answer 6

Elastic Database queries are supported only on Azure SQL Database v12 or later, Not on local server. https://msdn.microsoft.com/en-us/library/dn935022.aspx

Slab

Answer 7

I got the same error code (SQL72045) when importing bacpac even though we have deleted the external data sources in Azure that we used to sync data with. It turned out that there was a procedure "TransferDo" left with reference to SCOPED CREDENTIAL for another database. After we removed the procedure, the import worked well.

Agglomeration

which step we can remove this procedure? – Shimberg

@Shimberg remove the procedure before making the bacpac – Agglomeration