Securely access Azure SQL Database from Azure Synapse
Tags: bigdata, analytics, python, programming
Published: 2021-07-17 06:49:21

[The Apache Spark connector for Azure SQL Database (and SQL Server)](https://docs.microsoft.com/sql/connect/spark/connector?view=sql-server-ver15&WT.mc_id=data-34417-abhishgu) enables these databases to be used as input data sources and output data sinks for Apache Spark jobs. You can use the connector in [Azure Synapse Analytics](https://docs.microsoft.com/azure/synapse-analytics/spark/data-sources/apache-spark-sql-connector?WT.mc_id=data-34417-abhishgu) for big data analytics on real-time transactional data and to persist results for ad-hoc queries or reporting.

At the time of writing, there is no linked service or AAD pass-through support with the Azure SQL connector via Azure Synapse Analytics. But you can use other options such as Azure [Active Directory authentication](https://docs.microsoft.com/azure/synapse-analytics/spark/data-sources/apache-spark-sql-connector?WT.mc_id=data-34417-abhishgu#azure-active-directory-authentication) or via direct SQL authentication (username and password based). A secure way of doing this is to store the Azure SQL Database credentials in Azure Key Vault (as Secret) — this is what’s covered in this short blog post.


Assuming you have an [Azure Synapse Workspace](https://docs.microsoft.com/azure/synapse-analytics/quickstart-create-workspace?WT.mc_id=data-34417-abhishgu) and [Azure SQL Database](https://docs.microsoft.com/azure/azure-sql/database/single-database-create-quickstart?tabs=azure-portal&WT.mc_id=data-34417-abhishgu) already created, all you need to is:
- Create an Azure Key Vault and add a Secret to store the Azure SQL Database connectivity info.
- Create a Linked Service for your Azure Key Vault in Azure Synapse Workspace.
- Provide appropriate permissions to Azure Synapse workspace managed service identity to Azure Key Vault

## Here is a walk through of the process

[Create an Azure Key Vault](https://docs.microsoft.com/azure/key-vault/general/quick-create-portal?WT.mc_id=data-34417-abhishgu) and [add a Secret](https://docs.microsoft.com/azure/key-vault/secrets/quick-create-portal?WT.mc_id=data-34417-abhishgu#add-a-secret-to-key-vault).

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n4q9s3cnt8x8y1i6csjo.png)

> I have stored the entire JDBC connection string in this case but you can choose to just store the password as well.

To retrieve secrets from Azure Key Vault, the recommended way is to create a Linked Service to your Azure Key Vault. Also, make sure that the [Synapse workspace managed service identity (MSI)](https://docs.microsoft.com/azure/synapse-analytics/security/synapse-workspace-managed-identity?WT.mc_id=data-34417-abhishgu) has Secret Get privileges on your Azure Key Vault. This will let Synapse authenticate to Azure Key Vault using the Synapse workspace managed service identity.

> You can also authenticate using your user Azure Active Directory credential.

Create a Linked Service in Azure Synapse Workspace:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vu0q7qi27twsfe38fs8e.png)

[Grant appropriate access](https://docs.microsoft.com/azure/key-vault/general/assign-access-policy-portal?WT.mc_id=data-34417-abhishgu) for Azure Synapse workspace service managed identity to your Azure Key Vault:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8xniaqmnvknlzyfzyiui.png)

Choose `Get` permission on `Secret`:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wjvogk10rykwii2rho9m.png)

Search for the Synapse Workspace Managed Service Identity — it’s the same name as that of the workspace

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mk1dpsweqm2x540seuxi.png)

Add the policy:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5thmy61st6g55ellfee9.png)

Click Save to confirm:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fry4926cycozbglysniv.png)

## Let’s see how to use this…

> I will be using `pyspark` in Synapse Spark pools as an example.

Synapse uses Azure Active Directory (AAD) passthrough by default for authentication between resources. If you need to connect to a resource using other credentials, [use the TokenLibrary directly](https://docs.microsoft.com/azure/synapse-analytics/spark/apache-spark-secure-credentials-with-tokenlibrary?pivots=programming-language-python&WT.mc_id=data-34417-abhishgu) — this simplifies the process of retrieving SAS tokens, AAD tokens, connection strings, and secrets stored in a linked service or from an Azure Key Vault.

To retrieve a secret stored from Azure Key Vault, use the `TokenLibrary.getSecret()` function. Here is a [python example](https://docs.microsoft.com/azure/synapse-analytics/spark/apache-spark-secure-credentials-with-tokenlibrary?pivots=programming-language-python&WT.mc_id=data-34417-abhishgu#getsecret) but same applies to [C#](https://docs.microsoft.com/azure/synapse-analytics/spark/apache-spark-secure-credentials-with-tokenlibrary?pivots=programming-language-csharp&WT.mc_id=data-34417-abhishgu#getsecret) or [Scala](https://docs.microsoft.com/azure/synapse-analytics/spark/apache-spark-secure-credentials-with-tokenlibrary?pivots=programming-language-scala&WT.mc_id=data-34417-abhishgu#getsecret).

For example, to access data from `SalesLT.Customer` table (part of AdventureWorks sample database), you can use the following:

```python
url = TokenLibrary.getSecret("<Azure Key Vault name>", "<Secret name>", "<Linked Service name>")
dbtable = "SalesLT.Customer"
customers = spark.read \
    .format("com.microsoft.sqlserver.jdbc.spark") \
    .option("url", url) \
    .option("dbtable", dbtable) \
    .load()
print(customers.count())
customers.show(5)
```

That’s all there is to it!