Migrating data from on-premises to cloud based

Question

Hi All,

I am currently involved in a project whereby we would like to migrate data from an on-premises SQL DB into an Azure SQL DB in near real-time, whilst also transforming.

Due to constraints with enabling CDC on the on-prem DB, we are having to migrate the data via the following method: On-Prem SQL DB -> Azure Staging SQL DB <- Azure Data Factory (Transform data) -> Azure SQL DB.

My question is, should communication to and from the Azure SQL DBs be via an API rather than direct access/writes? Currently Azure data factory has full access to both DBs.

Assuming an Azure user account with ADF access was compromised, would that leave the DBs vulnerable? Therefore, an API that brokers the connections seems like a safer option?

Regards

Dene

Answer 1

Should communication to and from the Azure SQL DBs be via an API rather than direct access/writes?

In your scenario, using an API to broker the connections between your on-premises SQL DB and Azure SQL DBs can offer additional security benefits. By using an API, you can implement more granular access controls, monitor API calls, and apply additional layers of security such as authentication and authorization mechanisms. This approach can mitigate the risks associated with direct database access.

Assuming an Azure user account with ADF access was compromised, would that leave the DBs vulnerable?

If an Azure user account with Azure Data Factory (ADF) access were compromised, it could indeed leave your databases vulnerable. This user would have direct access to both the staging and the main Azure SQL DBs, potentially leading to unauthorized data access, manipulation, or data breaches. Implementing an API as an intermediary can limit the exposure of your databases by restricting direct access and enabling more controlled and monitored interactions.

Therefore, an API that brokers the connections seems like a safer option?

Yes, utilizing an API that brokers the connections between your data sources and destinations can be a safer option. An API can enforce stricter access controls and limit the attack surface, as it provides a single point of entry to your data services. It allows you to implement additional security measures such as:

1. Rate Limiting: To prevent abuse and mitigate the risk of Denial of Service (DoS) attacks.
2. Authentication and Authorization: Using tokens or other secure methods to verify user identity and permissions.
3. Logging and Monitoring: Keeping track of all API requests and responses, which helps in detecting and responding to suspicious activities.
4. Data Validation and Sanitization: Ensuring that the data being sent to the database is valid and free from harmful input.