Synapse Notebook to Azure SQL Database connection issue

Question

0

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"}

Answer 1

I did like below and it worked perfectly. the only thing I think you are missing is the 'f' at jdbcurl.

server`` ``=`` ``'myserver.database.windows.net'``

Port`` ``=`` ``1433``

Database`` ``=`` ``"sqldb-mydb-001"

jdbcUrl`` ``=`` ``f"jdbc:sqlserver://``{``server``}``:``{``Port``}``;databaseName=``{``Database``}``;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"``

token=TokenLibrary.getConnectionString``(``"CmbSqlDatabase"``)

query`` ``=`` ``"(SELECT * FROM dbo.table)as tb"``

conn_Prop`` ``=`` { ``"driver"`` : ``"com.microsoft.sqlserver.jdbc.SQLServerDriver"``, ``"accessToken"`` : ``token``

}

df`` ``=`` ``spark``.``read``.``jdbc``(``url=jdbcUrl``, ``table=query``, ``properties=conn_Prop``) ``display``(``df``)