sql - 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)

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"
   $hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")
   $fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)
   $hash = $hasher.ComputeHash($fileStream)
   $hashString = ""
   Foreach ($b in $hash) { $hashString += $b.ToString("X2") }
   $fileStream.Close()
   $hashString
   

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.