In this post, we are going to see how to establish a connection to Azure SQL Server Database from within Azure Databricks Notebooks. We will explore two examples – one by specifying the credentials from within the notebook and then an ideal way to use Azure Key Vault to fetch and use the database user credentials.
The very first thing we need to know is that this connectivity is done through JDBC (Java DataBase Connectivity) driver. “com.microsoft.sqlserver.jdbc.SQLServerDriver” is the exact driver that you need to specify while connecting to the database. Rest of the parameters like database server, database, user name and password are specified as –
Instead of table name, you can specify the query too as –
and then mapping this query to the table parameter in the jdbc call –
In an ideal world, you will always be connecting to the database by storing the credentials and other sensitive information in Azure Key Vault and then fetching it from there. Below snapshot is a clear reflection of this.
You can directly use the variables holding the values read from Azure Key Vault in the jdbc url instead of mapping them to other variables. Additional variables have been used here for easy understanding.
You can read other posts about How to configure Azure Key Vault for Azure Databricks and How to fetch secrets from Azure Key Vault in Azure Databricks, if you were not able to follow the variables in above exaples.