How can we connect Blackboard DDA (a SaaS using PostgreSQL to store data ) via Azure Synapse analytics using SSL certificate for authentication in SHIR for Private Vnet enabled networks.
Blackboard DDA is a SaaS application which uses PostgreSQL as DB to store data. The DB uses a SSL certificate key to authenticate and we have private Vnet enabled. How to create a linked service for this DB and where do we store certificate in SHIR. Are there any steps required to access this DB?
Azure Synapse Analytics
Azure Data Factory
-
Harishga 4,485 Reputation points • Microsoft Vendor
2024-05-06T07:16:01.5833333+00:00 Hi @Dravya Jain
Welcome to Microsoft Q&A platform and thanks for posting your question here.
To create a linked service for PostgreSQL in Azure Synapse Analytics, you need to go to the Manage tab in your Azure Synapse Analytics workspace and select Linked Services. Then, click New to create a new linked service. Search for PostgreSQL and select the Azure Database for PostgreSQL connector. Configure the service details, including the server name, database name, authentication type, and credentials. Test the connection to ensure that the linked service is working correctly and save the linked service.
To store the SSL certificate key in Azure Synapse Analytics, you can use Azure Key Vault. You need to create an Azure Key Vault if you haven't already done so. Then, add a new secret to the Azure Key Vault that contains the SSL certificate key. Grant the Synapse workspace's managed identity access to the Azure Key Vault by adding an access policy for the managed identity.
To access the Blackboard DDA database in Azure Synapse Analytics, you need to create a linked service for the Blackboard DDA database in Azure Synapse Analytics. Configure the linked service to use SSL certificate authentication. Store the SSL certificate key in Azure Key Vault. Grant the Synapse workspace's managed identity access to the Azure Key Vault. Create a pipeline in Azure Synapse Analytics that uses the linked service for the Blackboard DDA database, the Azure Key Vault linked service, and the VNet integration. Test the pipeline to ensure that it is working correctly.
I hope this information helps you. Let me know if you have any further questions or concerns.
-
Dravya Jain 0 Reputation points
2024-05-06T15:11:35.82+00:00 Hi ,
The PostgreSQL DB of Blackboard Database is not hosted on Azure, it is hosted outside Azure therefore I believe that Azure Database for PostgreSQL connector can not be used here. Moreover, I think we need to use Self Hosted Integration Runtime in the linked service and I am looking for the location where I should install the certificate .pem file to authenticate.
Also, since you mentioned to create a linked service for Blackboard DDA database, I dont see any dedicated connector for Blackboard.
Let me know if my understanding is not correct.
-
Harishga 4,485 Reputation points • Microsoft Vendor
2024-05-07T06:24:21.47+00:00 Hi @Dravya Jain
Your understanding is mostly correct. If the PostgreSQL database of Blackboard is not hosted on Azure, you cannot use the Azure Database for PostgreSQL connector directly. However, you can still connect to a non-Azure hosted PostgreSQL database using other methods that are supported by Azure Data Factory, such as using a Self-Hosted Integration Runtime (SHIR).For the SHIR, you would typically install it on an on-premises machine or a virtual machine within your private network. The location for installing the .pem certificate file for authentication would depend on the specific requirements of your setup and the application you’re using to connect. Generally, the certificate file should be placed in a directory that is accessible by the application or service that requires it for the SSL connection.
Regarding the Blackboard DDA database, while there isn’t a dedicated connector specifically for Blackboard, you can use a generic PostgreSQL connector provided by Azure Data Factory to connect to the Blackboard DDA database, as it is a PostgreSQL database. You’ll need to ensure that your connection string and any necessary credentials or certificates are correctly configured for secure access.
For Direct Data Access (DDA) with Blackboard, you would use a database client or tool that supports connections to PostgreSQL over SSL. You can refer to the detailed schema documentation provided by Blackboard for information on the database structure and to assist with creating your linked service.
Reference:
https://help.blackboard.com/Learn/Administrator/SaaS/Integrations/Direct_Data_AccessI hope this information helps you. Let me know if you have any further questions or concerns.
-
Harishga 4,485 Reputation points • Microsoft Vendor
2024-05-08T04:10:27.7266667+00:00 Hi @Dravya Jain
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. -
Dravya Jain 0 Reputation points
2024-05-08T07:13:35.14+00:00 I have not received any solution yet, still struggling to find the location where the .pem file should be placed or how to use the SSL key.
-
PRADEEPCHEEKATLA-MSFT 80,491 Reputation points • Microsoft Employee
2024-05-20T07:43:05.6066667+00:00 @Dravya Jain - Just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment