Accessing Azure Synapse Dedicated SQL Pool Using PySpark Without Exposing Passwords

Question

I want to create a notebook for processing data in a table from the database using PySpark, to be used later in a Databricks pipeline.

I'm unsure how to read data from the existing database in the notebook without including my password. Here is my current method, which I don't want to use due to security concerns:

python

Copy code

spark = SparkSession.builder.appName("SynapseAnalyticsDemo").getOrCreate()

Set up Synapse Analytics credentials

synapse_servername = ".sql.azuresynapse.net"

synapse_database = ""

synapse_username = ""

synapse_password = ""

synapse_jdbc_url = f"jdbc:sqlserver://{synapse_servername}:1433;database={synapse_database};user={synapse_username};password={synapse_password}"

Define the SQL query and table name

table_name = ""

query = f"SELECT * FROM {table_name} WHERE some_column = 'some_value'"

I've also tried this approach without success:

pyspark

Copy code

%%pyspark

df = spark.sql("SELECT * FROM TableName")

df.show()

In this second method, I used the exact table name from SQL Server Management Studio and tried the Azure Synapse dedicated SQL pool name, but neither worked.

How can I access the Azure Synapse dedicated SQL pool using a PySpark notebook without exposing my password?

Accepted Answer

Hi @Vandrangi Charaz Kumar

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

To access data from the dedicated SQL pool in Azure Synapse Notebook without exposing your password, you can store your database password securely in Azure Key Vault. To do this, you need to create an Azure Key Vault and then create a secret in the Key Vault with your database password.

Retrieve the secret value using below code:

from notebookutils import mssparkutils
mssparkutils.credentials.getSecret('AzureKeyvaultName', 'secretName')

Access dedicated sql database using below code:

from notebookutils import mssparkutils

jdbcHostname = "synpool.database.windows.net"

jdbcPort = 1433

jdbcDatabase = "dedsql"

jdbcUsername = "sqladminuser"

jdbcPassword = mssparkutils.credentials.getSecret('sqlservercred', 'sqlpassword')

jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

#url = s"jdbc:sqlserver://${database_host}:${database_port}/${database_name}"

table = "students"

jdbcUrl = f"jdbc:sqlserver://{jdbcHostname}:{jdbcPort};databaseName={jdbcDatabase}"

df1 = spark.read.format("jdbc").option("driver", jdbcDriver).option("url", jdbcUrl).option("dbtable", table).option("user", jdbcUsername).option("password", jdbcPassword).load()

df1.show()

enter image description here

Check this for adding access policies.

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

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Share via

Accessing Azure Synapse Dedicated SQL Pool Using PySpark Without Exposing Passwords

Set up Synapse Analytics credentials

Define the SQL query and table name

0 additional answers

Your answer