Search code examples
sql-serverazureencryptionazure-sql-databasetransactional-replication

Transactional Replication to Azure SQL DB - How to Encrypt data?


My organisation is considering using Transactional Replication to Azure SQL DB but unsure where encryption and security fits in to this new capability. We are looking for documentation on how to configure security for replication to Azure SQL, perhaps with encryption and other steps to help mitigate vulnerabilities.

This resource has some details but does it also apply to Azure scenaio?


Solution

  • See this article especially part about creating subscription using transact-SQL. Also see this about connection encryption. So I think that when you add a subscriber all you have to do is to make sure that connection encryption is enabled. Below is a brief description how to achieve this while connecting to Azure SQL. Since when you add a subscriber you have to connect to Azure SQL database then the process is similar:

    1. Open SQL Server Management Studio.
    2. From Object Explorer, click Connect, then click Database Engine.
    3. From Connect to Server, click Connection Properties.
    4. Select Encrypt connection

    Also you could consider a VPN connection between on-prem and Azure as mentioned here.

    Protecting data in transit should be essential part of your data protection strategy. Since data will be moving back and forth from many locations, the general recommendation is that you always use SSL/TLS protocols to exchange data across different locations. In some circumstances, you may want to isolate the entire communication channel between your on-premises and cloud infrastructure by using a virtual private network (VPN).

    For data moving between your on-premises infrastructure and Azure, you should consider appropriate safeguards such as HTTPS or VPN.

    For organizations that need to secure access from multiple workstations located on-premises to Azure, use Azure site-to-site VPN.

    For organizations that need to secure access from one workstation located on-premises to Azure, use Point-to-Site VPN.

    Larger data sets can be moved over a dedicated high-speed WAN link such as ExpressRoute. If you choose to use ExpressRoute, you can also encrypt the data at the application-level using SSL/TLS or other protocols for added protection.