Can't Access Azure Synapse Spark Tables through SSMS

0
votes

I am experiencing an issue connecting to my spark pool cluster through SSMS. In Synapse Studio, I am performing the below steps:

  1. First a I create a database on the Apache Spark Cluster using sql: %%sql

Create Database test

  1. Next I create a table pointing to an ADLS2 folder with parquet files using pyspark: spark.sql("CREATE TABLE IF NOT EXISTS test.testparquet USING parquet LOCATION 'abfss://[email protected]/test/output'")

  2. The database is created through Synapse Studio with no issues.

  3. I then go to SSMS and put the ondemand sql instance into the browser:

Azure Synapse On Demand SqlInstance

  1. Then when I go to query the spark database and table I get the below error:

Credential Error

This functionality has been for working for months so I don't know what could have caused this. I tried to fix the issue by creating a scope credential within the spark database but when I go to drop an recreate the data source I get the below error:

Msg 15875, Level 16, State 8, Line 2 Operation DROP EXTERNAL DATA SOURCE is not allowed for a replicated database. Msg 102, Level 15, State 1, Line 6 Incorrect syntax near 'sqlondemand'.

How come I can no longer access my ADLS 2 parquet files through SSMS?

1
apache-sparkazure-synapse
Was the underlying Database Scoped Credential created with a SAS token? Perhaps it expired? – Joel Cochran
The strange part about this was it didn't require a scoped credential when creating the spark database and tables I just was able to connect to the ondemand instance and query the tables. – DataLady
Maybe I misread - but if you were creating EXTERNAL RESOURCES, then you would need a SCOPED CREDENTIAL. – Joel Cochran
Sorry I should have been more clear in my post.My bad. Here is what is happening 1) When I first set this up, I created spark databases and tables through the synapse studio. To set up the databases and tables through synapse studio it did not require any scope credentials to be created before hand. I was able to connect to my spark tables through SSMS using the sqlondemand link. Some how the external sources were created through the synapse studio. I was able to access my spark database and tables through ssms with no issues for months now I'm getting this credentials error. – DataLady
Thanks for clarifying, unfortunately I don't have any guidance for you. You may want to put in a support ticket. – Joel Cochran

1 Answers

0
votes

Do you use AAD user or SQL user to connect?

With AAD user, identity of the user is passed through to storage to read the data.

With SQL user, you must create credential to read data form storage. SQL user is scoped to the SQL pool so it can't access storage without appropriate credential. To do that run the following commands:

CREATE CREDENTIAL [https://<mystorageaccountname>.dfs.core.windows.net/<mystorageaccountcontainername>] WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET = 'YOUR_SECRET';
GRANT REFERENCES ON CREDENTIAL::[https://<mystorageaccountname>.dfs.core.windows.net/<mystorageaccountcontainername>] TO [specific_user];

With SQL user, when you name your credential as path towards storage, serverless SQL pool automatically uses it (as long as user has permissions to use it) when querying data in the lake.

What I guess happened, is that you created credential long time ago, but now it expired so you started seeing this error message.