Deliver and Export an Azure SQL Database securely with Azure Private Link

Hey everyone, recently, I had to deliver some SQL Scripts and export an Azure SQL Database securely. Knowing that the Azure SQL Server is behind a firewall and it is linked to a private endpoint.

• How to deliver a SQL Script to an Azure SQL Database ?

In order to deliver an SQL Script to an Azure SQL Database, if you are inside a company where the security matters, you will have your own DNS instead of “database.windows.net”, this script will work in this case too :

Let’s test it, I want to deploy the following sample to my db :

I just downloaded this SQL Script from : https://use-the-index-luke.com/sql/example-schema/sql-server/where-clause

After running the script let’s check the database :

Note that since the Azure SQL Server is linked to a Private Endpoint and since the IP Address is known (with the 1433 Port allowed in your on-premise Firewall), you won’t need to whitelist the source IP.

• How to export a Database linked with the Private Endpoint?

Now, I want to export my Database, the security guy says “ I don’t want any internet flow here”. The only way to export it securely is passing through the private ER/VPN, here, the private endpoint shows up.

I tried again and again, first, the following command won’t work :

az sql db export

Because it will pass through the Public IP and use the “.database.windows.net” which your internal firewall will never ever allow it. So you will always get the same error telling you that it could not reach the server or if you have a cool network team (or not) it will return “the myserver.database.windows.net” could not be resolved.

The second test was more smarter (or not), I tried to use the T-SQL :

IF NOT EXISTS  
(SELECT * FROM sys.credentials   
WHERE name = '<mycredentialname>')  
CREATE SCOPED CREDENTIAL [<mycredentialname>] WITH IDENTITY = '<mystorageaccountname>'  
,SECRET = '<mystorageaccountaccesskey>';

This one worked, I said “Yes, finally something works”, then I tried to save the backup :

BACKUP DATABASE mydb
TO URL ='https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/mydb.bak';   
GO

Aand, I got a beautiful red message saying :

'BACKUP DATABASE' is not supported in this version of SQL Server.

So the question now, if the “backup database” is not working, how can I atleast export the database ?

• Using SqlPackage.exe

SqlPackage.exe /a:Export /tf:testExport.bacpac /scs:”Server=<serverName>,1433;Initial Catalog=<databaseName>;Persist Security Info=False;User ID=<userName>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;” /ua:True

Running it, it gives :

Yes, it works ! since we can give the connection string we can export the database through the private endpoint.

Also for the import, we can use the same tool :

SqlPackage.exe /a:Import /tcs:”Data Source=<serverName>;Initial Catalog=<database>;User ID=<userName>;Password=<password>" /sf:testExport.bacpac

I will clean the database and reimport the “bacpac” :

I run the import command :

Let’s try again a select once again :

Bella ciao,