Synapse Notebook to Azure SQL Database connection issue

Question

created Linked service to Azure SQL database in Synapse Analytics that uses Authentication type " System Assigned Managed Identity". Than referencing it in PySpark Notebook. Method getConnectionString is used to get access token.

But its failing with error Message=Missing required property 'connectionstring' on AzureSqlLinkedservice

Below is the code and error message details

Code :

jdbcUrl = "jdbc:sqlserver://my_server_name.database.windows.net:1433;database=my_db_name;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
token=TokenLibrary.getConnectionString("AzureSQLLinkedServiceName")

pushdown_query = "(SELECT col1 FROM my_table_name) as tbl"connectionProperties = {
 "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
 "accessToken" : token
}
df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)
display(df)

Error:

Py4JJavaError: An error occurred while calling z:com.microsoft.azure.synapse.tokenlibrary.TokenLibrary.getConnectionString. : com.microsoft.azure.synapse.tokenlibrary.TokenServiceClientResponseStatusException: Token Service returned 'Client Error' (400), with message: {"result":"DependencyError","errorId":"BadRequest","errorMessage":"[Code=400, Target=AzureSqllinkedservice, Message=Missing required property 'connectionstring' on AzureSqllinkedservice]. TraceId : 32b30cfe-512e-4d33-b423-279ac0ee933d | client-request-id : e232532f-dbe7-49c1-8111-316c65213981. Error Component : LSR"}

Pratik Lad · Accepted Answer · 2024-07-25 11:05:27Z

1

The error you are facing is because you are creating linked service with incorrect configuration to resolve it while creating linked service you have to select version as Legacy and also Authentication Should be System Assigned Managed Identity.

enter image description here

Execution:

enter image description here

answered Jul 25, 2024 at 11:05

Pratik Lad

8,7282 gold badges5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Synapse Notebook to Azure SQL Database connection issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related