raiajithkumarr
commented
1 year ago Here synapse name is jsynapse
CREATE USER [jsynapse] FROM EXTERNAL PROVIDER; ALTER ROLE db_datawriter ADD MEMBER [jsynapse]; ALTER ROLE db_datareader ADD MEMBER [jsynapse];
after adding this working fine
For more information: https://stackoverflow.com/questions/76580895/azure-synapse-scala-using-msi-access-token-unable-to-retrieve-data-from-azure/76586172#76586172
Linked services configured for sql db through System assigned identity as shown in below image
Below find note book (in scala) in Azure synapse
import com.microsoft.azure.synapse.tokenlibrary.TokenLibrary import java.util.Properties
val jdbcHostname = ".sql.azuresynapse.net"
val jdbcPort = 1433
val jdbcDatabase = ""
// Create the JDBC URL without passing in the user and password parameters. val jdbcUrl = s"jdbc:sqlserver://${jdbcHostname}:${jdbcPort};database=${jdbcDatabase}"
// Create a Properties() object to hold the parameters. val connectionProperties = new Properties()
// Driver that can also be observed in the log when using the 'native' Synapse SQL way. val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver" connectionProperties.setProperty("Driver", driverClass)
// Create a linked server to your dedicated pool with a Manged Identity connectionProperties.setProperty("accessToken", mssparkutils.credentials.getConnectionStringOrCreds("samplesqllink"))
// Define your query val pushdown_query = "(select top 10 ID from dbo.tblEmployees) data_alias" val df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties) display(df)
Here getting this error
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ''. ClientConnectionId:xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx /
But with User name and password it is working fine.
Please help here did I miss anything