A Credential-Safe Way to Connect and Access Azure Synapse Analytics in Azure Databricks

A guide on how to setup SQL Server firewall and connect from Databricks using Secret Scopes in PySpark.

Prerequisites

Set up firewall access

Initially, the sever firewall prevents all access to our Azure SQL Server.

Azure SQL Server: Configure firewall settings (Image by author)
Azure SQL Server: Add client IP and allow Azure resources (Image by author)

Connect to Azure Synapse Analytics data warehouse by using SSMS

SSMS: Connect to SQL server (Image by author)
SSMS: Create a new table query (Image by author)
CREATE SCHEMA csvData;
GO
CREATE TABLE csvData.covidcsvdata
(
provincestate VARCHAR(255) NULL
,countryregion VARCHAR(255) NULL
,lastupdate DATETIME NULL
,confirmed INT NULL
,deaths INT NULL
,recovered INT NULL
,active INT NULL
,latitude DECIMAL(12,9) NULL
,longitude DECIMAL(12,9) NULL
,sourcefile VARCHAR(255) NOT NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
);
GO
SSMS: Create and locate the new table (Image by author)

Store SQL Server credentials in the Azure Key Vault

Azure Key Vault: Store a new secret (Image by author)
Azure Key Vault: Properties page (Image by author)

Create an Azure Key Vault-backed Secret Scope in Databricks

Azure Databricks: Create a secret scope (Image by author)

Vault URI and Resource ID link the Azure Key Vault and Secret Scopes. Any changes you make in your Azure Key Vault are automatically available in your Secret Scope.

Connect to Azure Synapse Analytics data warehouse from Databricks using Secret Scopes

Azure Databricks: Connecting and querying SQL Server using Python (Image by author)

Conclusion

Next Steps

The Startup

Get smarter at building your thing. Join The Startup’s +788K followers.

Sign up for Top 10 Stories

By The Startup

Get smarter at building your thing. Subscribe to receive The Startup's top 10 most read stories — delivered straight into your inbox, once a week. Take a look.

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Dhyanendra Singh Rathore

Written by

Microsoft certified Data and BI Professional. Enthusiastic about solving problems. Currently playing with Azure Functions and XMLA endpoints.

The Startup

Get smarter at building your thing. Follow to join The Startup’s +8 million monthly readers & +788K followers.

Dhyanendra Singh Rathore

Written by

Microsoft certified Data and BI Professional. Enthusiastic about solving problems. Currently playing with Azure Functions and XMLA endpoints.

The Startup

Get smarter at building your thing. Follow to join The Startup’s +8 million monthly readers & +788K followers.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store