1

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?

2 Answers 2

1

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:

-1

Data is encrypted in transit to Azure by default. It is not possible to send non-encrypted in-transit data to Azure.

Even when you try to connect to Azure SQL Database with tools like SSMS and you did not specify encryption, encryption is still enforced.

When a client first attempts a connection to SQL Server / SQL Azure, it sends an initial connection request. Consider this a "pre-pre-connection" request. At this point the client does not know if SSL/Encryption is required and waits an answer from SQL Server/SQL Azure to determine if SSL is indeed required throughout the session (not just the login sequence, the entire connection session). A bit is set on the response indicating so. Then the client library disconnects and reconnects armed with this information.

By the way I suggest you not to use a linked server for ETL. You will find OLEDB waits so high that it will be no viable option.

1
  • Just to clarify your first paragraph: I'm pulling from Azure, not sending data to Azure (except the handshake and query). Also, this isn't truly ETL... More accurately, it's "lift and shift." Commented Apr 25, 2018 at 4:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.