How to connect Synapse SparkPool notebook with external databases

Question

I'm trying to connect my Synapse SparkPool notebook to a external Postgres DataBase. I set the conecction as described here access-postgresql-from-azure-synapse-notebook however it returning an error related to IP not in pg_hba.confg.

I think it is because the VPN.
My question is: what is the correct way to access external postgres database, that is covered by VNP, in Synapse SparkPool notebooks ?

Answer 1

Hey @lucas.pontes

here are the step-by-step instructions on how to access an external Postgres database that is covered by a VPN in Synapse SparkPool notebooks:

1. Open the Synapse SparkPool notebook.
2. In the notebook, click on the "File" menu and select "Settings".
3. In the "Settings" dialog, select the "Connections" tab.
4. In the "Connections" tab, click on the "Add" button.
5. In the "Add Connection" dialog, select the "Proxy" connection type.
6. In the "Proxy" connection type, enter the following information: Host: The hostname or IP address of the proxy server. Port: The port number of the proxy server. Username: The username for the proxy server. Password: The password for the proxy server.
7. Click on the "Test" button to test the connection to the proxy server.
8. If the connection is successful, click on the "Save" button.
9. Open the connection string for the external Postgres database.
10. In the connection string, find the following section:

[connection]
host=localhost
port=5432
database=postgres
username=postgres
password=postgres

1. Replace the value of the "host" property with the hostname or IP address of the proxy server.
2. Replace the value of the "port" property with the port number of the proxy server.
3. Add the following section to the connection string:

[proxy]
host=localhost
port=8080
username=username
password=password

14. Save the connection string.
15. In the Synapse SparkPool notebook, create a new cell.
16. In the new cell, import the following libraries:

import pyodbc

17. In the new cell, create a connection object to the external Postgres database using the connection string you updated in step 13.

conn = pyodbc.connect(connection_string)

18. In the new cell, execute a query against the external Postgres database.

cursor = conn.cursor()
cursor.execute("SELECT * FROM table")
rows = cursor.fetchall()

19. In the new cell, print the rows returned by the query.

for row in rows:
print(row)

20. Run the cell.

If you have followed these steps correctly, you should be able to connect to the external Postgres database from your Synapse SparkPool notebook and execute queries against it.

If you find this helpful please Accept the answer by clicking Accept Answer Button and Upvote.