Sql-server – Azure SQL Db Linked Server – Secured Connection Methods

azure-sql-databaseencryptionlinked-serversql server

The developers want a linked server from our on-prem server to our Azure SQL Db (DBaaS) in order to complete a nightly data pull into our data warehouse.

I'd much rather go the route of SSIS but don't have the time to properly develop the package or teach them how to support it.

My main concern at this point is ensuring the data is encrypted in transit.

Once the linked server is in place along with a corresponding Agent job, my options appear to be:

sqlcmd (with the -N switch, "…used by the client to request an encrypted connection."
Invoke-SQLcmd Azure PowerShell cmdlet (with the -EncryptConnection switch)
OR… Azure SQL Data Sync (Hub to Member), which as of this writing is still in preview but looks promising!

Are there any gotchas or special considerations for these methods that I should be aware of?

Best Answer

The main difference between this answer and the previous one offered is the (what I find to be incredibly important) acknowledgement of MitM attacks rather than a blanket statement which overlooks caveats.

For posterity, from Securing your SQL Database in the documentation:

Important

All connections to Azure SQL Database require encryption (SSL/TLS) at all times while data is "in transit" to and from the database. In your application's connection string, you must specify parameters to encrypt the connection and not to trust the server certificate (this is done for you if you copy your connection string out of the Azure portal), otherwise the connection does not verify the identity of the server and is susceptible to "man-in-the-middle" attacks. For the ADO.NET driver, for instance, these connection string parameters are Encrypt=True and TrustServerCertificate=False. For information about TLS and connectivity, see TLS considerations.

For other ways to encrypt your data, consider:

Cell-level encryption to encrypt specific columns or even cells of data with different encryption keys.

If you need a Hardware Security Module or central management of your encryption key hierarchy, consider using Azure Key Vault with SQL Server in an Azure VM.

Related Solutions

SQL Azure – Fix Slow Insert Through Linked Server

I insert into this table small amount of data just about 1000 rows and it takes more than 4 minutes

Remote data modifications through a linked server use the sp_cursor model. The effect is similar to issuing 1000 separate single inserts (one for each row). If a round trip takes 250ms, 1000 such trips will take 4 minutes and 10 seconds. Using a bulk loading method such as bcp or SSIS will generally be more efficient (unless the number of rows to be inserted is tiny).

Another alternative for ad-hoc needs is to build a string containing a single INSERT statement with multiple rows in the VALUES clause. The statement is then used with EXECUTE AT, for example:

-- Note local-to-azure table name used, not four-part syntax
DECLARE @sql varchar(max) = 'INSERT dbo.Test VALUES ';

-- Build a list of 1000 numbers to insert    
SELECT @sql += '(' + CONVERT(varchar(11), n) + '),'
FROM dbo.Numbers AS N
WHERE n BETWEEN 1 AND 1000;

-- Remove trailing comma
SET @sql = LEFT(@sql, LEN(@sql) - 1);

-- For debugging
PRINT @sql;

-- Execute the finished statement at the remote server;
EXECUTE (@sql) AT AZURE;

The constructed insert statement executes at the Azure database, so a local name is used for the table (note: building a dynamic insert statement using a four-part target name buys you nothing).

Note that EXECUTE ... AT requires RPC and RPC OUT enabled in the linked server options.

The VALUES clause for a plan INSERT statement is limited to 1000 elements. There are ways around that (a VALUES clause in a CTE does not have that restriction). You could also choose to build 1000-row batches or build single-row insert statements, but if the there is that much data on a regular basis, you would probably be better off using one of the bulk loading methods instead.

Sql-server – Continuous replication between Oracle Database and Azure SQL Database

Oracle Golden Gate has always supported replication between Oracle and SQL Server. For a project I recently confirmed that Golden Gate for Oracle 10 is still available if you push a bit. If you are using an Oracle version that is out of support then you need to update for a number of reasons.

Synchronization with Azure is detailed here:

Configuring Oracle GoldenGate for Azure

Best Answer

Related Solutions

SQL Azure – Fix Slow Insert Through Linked Server

Sql-server – Continuous replication between Oracle Database and Azure SQL Database

Related Question