TDM - Feasibility check of FDM 4.9.129.0 to mask the data in Azure sql database using Azure Server principal

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We are currently using the FastDataMasker(v 4.9.129.0) as part of our TDM solution for masking. We now have a new requirement for masking the data on Azure cloud. 

Would like to know if we have feasibility in this version of FDM to mask the Azure sql database using the Azure Server principal,

if so :

Could you please let us know the prerequisites for preparing our windows server 2012 for connecting to Azure cloud database and also the generic configurations needs to use FDM with the azure cloud?

if not then :

Can you please let us know if the latest version of FDM(4.10.XX.0) has this capability?

Environment

Release : 4.9

Cause

Azure Service Principal authentication support depends upon on a combination of SQL Server JDBC driver and an additional java libraries which allows us to authenticate on Azure service using a Azure Service Principal ID and secret through FDM.

This can be achieve both on FDM 4.9.xx and 4.10.xx with different approaches which relies on either updating your SQL Server JDBC driver or add additional JAR files to your FDM lib folder.

More details on how Azure Service Principal and Azure SQL authentication works:

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal?view=azuresql 

https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver16

If you do not update either your SQL Server JDBC Driver and add additional JARs to your FDM lib folder you won't be able to connect into a Azure SQL database using a Azure Service Principal

Resolution

There is a couple different solutions to be able to connect to Azure SQL using Azure Service Principal through FDM for masking:

Option A : FDM update to 4.10.xx and add Azure Service Principal necessary JAR files to FDM lib folder

1. Update FDM from 4.9.xx to the latest 4.10.xx version (recommended solution) and add the necessary additional JARs which will enable you to authenticate using a Azure Service Principal previously created
2. Add the necessary additional dependencies JAR files to your FDM installation \lib folder (the additional JARs are provided on the zip file attached to this article)
3. Configure a SQL Server connection on FDM by using the following parameters:
   Server Name: your Azure SQL server name
   Username: blank
   Password: blank
   Port: your Azure SQL server port
   Database Name: your Azure SQL database name
   Default schema: your Azure SQL desired schema to connect
   Additional Parameter: AADSECUREPRINCIPALSECRET={your azure service principal secret};AADSECUREPRINCIPALID={your azure service principal};AUTHENTICATION=ACTIVEDIRECTORYSERVICEPRINCIPAL
   
4. This should allow to connect and mask any data within Azure SQL using a Azure Service Principal using FDM

Option B: Keep using FDM 4.9.xx if unable to upgrade to 4.10.xx and update your SQL JDBC Driver to version 9.4 and add Azure Service Principal necessary JAR files to FDM lib folder (alternative solution)

1. Replace your existing SQL Server JDBC driver  JAR file on your FDM installation SQLSERVER_DLLs folder (files provided on this article)
2. Replace your existing SQL Server JAR file on your FDM installation lib folder (files provided on this article)
3. Make sure to delete your old SQL Server JDBC driver which resides on the /lib folder
4. Add the necessary additional dependencies JAR files to your FDM installation \lib folder (the additional JARs are provided on the zip file attached to this article)
5. Configure a SQL Server connection on FDM by using the following parameters:
   Server Name: your Azure SQL server name
   Username: blank
   Password: blank
   Port: your Azure SQL server port
   Database Name: your Azure SQL database name
   Default schema: your Azure SQL desired schema to connect
   Additional Parameter: AADSECUREPRINCIPALSECRET={your azure service principal secret};AADSECUREPRINCIPALID={your azure service principal};AUTHENTICATION=ACTIVEDIRECTORYSERVICEPRINCIPAL
   
   
6. 

Additional Information

Please ensure your have properly setup a Azure Service Principal and that it has all necessary permissions on Azure SQL for this procedure to work