Access Secret from vault using Synapse pyspark notebook

Question

How to access secret keys from vault using azure synpase pyspark notebook?

Answer 1

Hello @Shivank.Agarwal ,

Steps to access synapse DB from spark notebook and added the db username and password in secret vault:

Step1: Create synapse username and password in Key vault secrets:

Note: I had created synapse username as synapseusername and synapse password as synapsepassword and my Azure Key vault name is chepra.

Step2: Using TokenLibrary function you can access the secrets from keyvault in your notebook.

Method1: Direct call to the TokenLibrary which contains synapseusername and synapsepassword from Azure Key vault.

Note: For example: TokenLibrary.getSecret("AzureKeyvaultName", "SecretName")

%%pyspark  
jdbcDF = spark.read.format("jdbc") \  
    .option("url", f"jdbc:sqlserver://cheprasynapse.sql.azuresynapse.net:1433;database=chepra") \  
    .option("dbtable","drivers") \  
    .option("user", TokenLibrary.getSecret('chepra', 'synapseusername')) \  
    .option("password",TokenLibrary.getSecret('chepra', 'synapsepassword')) \  
    .option("driver","com.microsoft.sqlserver.jdbc.SQLServerDriver") \  
    .load()  
jdbcDF.show()  

Method2: Declaring the synapseusername and synapse password in a variables.

Declaring variables:

synapseusername = TokenLibrary.getSecret('chepra', 'synapseusername')  
synapsepassword = TokenLibrary.getSecret('chepra', 'synapsepassword')  

Calling the variables in the connection string:

%%pyspark  
jdbcDF = spark.read.format("jdbc") \  
    .option("url", f"jdbc:sqlserver://cheprasynapse.sql.azuresynapse.net:1433;database=chepra") \  
    .option("dbtable","drivers") \  
    .option("user", synapseusername) \  
    .option("password",synapsepassword) \  
    .option("driver","com.microsoft.sqlserver.jdbc.SQLServerDriver") \  
    .load()  
jdbcDF.show()  

Hope this helps. Do let us know if you any further queries.

---------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

Answer 2

Hello @Shivank.Agarwal ,

Thanks for the question and using MS Q&A platform.

You can use the MSSparkUtils Credentials Utilities to get the access tokens of linked services and manage secrets in Azure Key Vault.

Connect to ADLS Gen2 storage using a SAS token stored in Azure Key Vault secret.

%%pyspark  
spark.conf.set("fs.azure.account.auth.type", "SAS")  
spark.conf.set("fs.azure.sas.token.provider.type", "com.microsoft.azure.synapse.tokenlibrary.AkvBasedSASProvider")  
spark.conf.set("spark.storage.synapse.akv", "<AZURE KEY VAULT NAME>")  
spark.conf.set("spark.storage.akv.secret", "<SECRET KEY>")  
  
df = spark.read.csv('abfss://<CONTAINER>@<ACCOUNT>.dfs.core.windows.net/<FILE PATH>')  
  
display(df.limit(10))  

For more details, refer to Secure credentials with linked services using the TokenLibrary.

Hope this helps. Do let us know if you any further queries.

---------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

Answer 3

@PRADEEPCHEEKATLA-MSFT I am getting a 401 error while using the below code

Error: NoAuthenticationInformation, "Server failed to authenticate the request. Please refer to the information in the www-authenticate header.

%%pyspark
spark.conf.set("fs.azure.account.auth.type", "SAS")
spark.conf.set("fs.azure.sas.token.provider.type", "com.microsoft.azure.synapse.tokenlibrary.AkvBasedSASProvider")
spark.conf.set("spark.storage.synapse.akv", "<AZURE KEY VAULT NAME>")
spark.conf.set("spark.storage.akv.secret", "<SECRET KEY>")

df = spark.read.csv('abfss://<CONTAINER>@<ACCOUNT>.dfs.core.windows.net/<FILE PATH>')

display(df.limit(10))

Answer 4

synapseusername=token_library.getSecret(key_name, secret
synapsepassword=token_library.getSecret(key_name, secretpassword, linked_service)


.option("user", synapseusername) \  
.option("password",synapsepassword) \