Encryption

Question

Hello,

I have a requirement to bring the data from SQL database to Azure Datalake for Analytics and Reporting in Power BI.

Source :Microsoft SQL database host externally.

Destination : Azure Data Lake.

I want to ingest data from the SQL table into Azure Data Lake.

The external database can be accessed from organization on-prem and the connection is through MPLS exists.

New Change that we are adding is we want to use Azure. In ADF we will create the linked service for source SQL . ADF SHIR will be used and an Azure VM is setup with the SHIR agent. From Azure to On-prem we will setup express route.

Questions :

1. ADF uses outbound port 443 to connect right ?
2. Where should we open port 443 - Self Hosted VM NSG outbound rule ?
3. SQL database should also expose 443 ? Where else ?
4. TLS encryption. Where to enable TLS settings in Azure ?
5. How data will be encrypted during transit and at rest in ADF, ADLS ?
6. What TLS settings need to done in Self Hosted agent ?
7. Do we need to store the TLS key/certificate in Azure , where and how ?

Please provide clear answers to the specific query. Please do not share chat gpt generated response or skip the actual ask.

Answer 1

Hi Sourav,

Thank you for posting query in Microsoft Q&A Platform.

Yes, ADF uses outbound port 443 to connect to external services, including SQL databases.

To allow outbound traffic on port 443, you need to configure the outbound rules in the network security group (NSG) associated with the self-hosted integration runtime (SHIR) VM. You can add a rule to allow outbound traffic on port 443 to the IP address range of the SQL database.

The SQL database should also expose port 443 for ADF to connect to it securely. You can configure the SQL database to use TLS encryption to secure the connection. You can enable TLS settings in Azure by configuring the TLS version in the Azure SQL database firewall settings.

Data will be encrypted during transit and at rest in ADF and ADLS. ADF uses HTTPS to encrypt data in transit, and ADLS uses Azure Storage Service Encryption (SSE) to encrypt data at rest. You can also enable client-side encryption in ADF to encrypt data before it is sent to ADLS.

To configure TLS settings in the self-hosted agent, you need to configure the TLS version in the Java Virtual Machine (JVM) used by the agent. You can do this by setting the JVM system properties for TLS.

You do not need to store the TLS key/certificate in Azure. The TLS key/certificate is used to secure the connection between ADF and the SQL database, and it should be stored securely on the SQL database server.

Answer 2

Yes, that's correct. Azure Data Factory (ADF) uses outbound port 443 for secure communication with external services.

Yes, you should open port 443 in the outbound rules of the Network Security Group (NSG) associated with your Self-Hosted Integration Runtime (SHIR) VM. This allows the SHIR agent to establish secure connections to external services.

While your SQL database doesn't need to expose port 443 specifically, it's essential that the SQL database allows outbound connections to port 443 for ADF to communicate securely with it. In this scenario, it's mainly about allowing the SHIR agent on your VM to connect to the SQL database.

Azure services, including Azure Data Factory and Azure Data Lake Storage, use TLS encryption by default. You don't need to enable it separately, as it's an inherent part of the Azure infrastructure.

Data Factory and Azure Data Lake Storage both utilize TLS encryption for data in transit, ensuring that data transferred between services is secure. For data at rest in Azure Data Lake Storage, data is automatically encrypted using Azure Storage Service Encryption (SSE).

The Self-Hosted Integration Runtime (SHIR) agent handles secure communication with Azure services. You don't typically need to configure specific TLS settings for the SHIR agent itself. Ensuring that your VM is up to date with the latest security updates should suffice.

Azure manages the TLS certificates and keys required for secure communication between services. You generally don't need to manage or store TLS certificates yourself, as Azure abstracts away this complexity to provide a secure environment. Focus on securing access to your resources and configuring services to communicate securely.