question

CourtneyHaedke-0265 avatar image
0 Votes"
CourtneyHaedke-0265 asked CarrinWu-MSFT commented

Can't Access Azure Synapse Spark Tables through SSMS

Hello,
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 pyspark:
%%sql
Create Database test
2) Next I create a table pointing to an ADLS2 folder with parquet files:
spark.sql("CREATE TABLE IF NOT EXISTS test.testparquet USING parquet LOCATION 'abfss://output@test.test.core.windows.net/test/output'")
3) The database is created through Synapse Studio with no issues.
4) I then go to SSMS and put the ondemand sql instance into the browser:

84523-picture1.png


5) Then when I go to query the spark database and table I get the below error:

84571-synapseerror.jpg


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 create 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?
Thanks,
Courtney


sql-server-generalazure-synapse-analytics
picture1.png (13.6 KiB)
synapseerror.jpg (18.5 KiB)
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Similar issue here!

I created a DB on Synapse SQL Serverless and for several months had no issues, now I cannot access the ADLS Gen 2 from sql endpoint, no issues from Synapse Studio.

Please help!

0 Votes 0 ·

Thanks for letting me know! This happened over the weekend. When did the issue start for you?

0 Votes 0 ·

Hello,

it started for me today, but yesterday I didn't have anything scheduled.

Thank you for posting the issue, it really helped me to avoid wasting time.

Now it it Microsoft turn to help us, already 4 hours without any feedback...

0 Votes 0 ·

Hi @CourtneyHaedke-0265, we didn't get a reply from you, any update for this?

0 Votes 0 ·

Hi @CarrinWu-MSFT if you read my discussion with @SilvanoP-4597 the issue has been resolved

0 Votes 0 ·

Hi @CarrinWu-MSFT if you read my discussion with @SilvanoP-4597 the issue has been resolved

Yeah, if. These -msft people do not always read the threads too closely, it seems.

Anyway, what you could do, is to convert the comment where you had the resolution to an Answer, and then accept that answer. Because that is what Carrin & co are after: trying to get an Answer to every Question.

0 Votes 0 ·
Show more comments

Hi @CourtneyHaedke-0265, yes, I had read your discussion before, and very good to know that the issue has been resolved. I just hope that you could accept the useful anwser, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Have a nice day!

0 Votes 0 ·
CourtneyHaedke-0265 avatar image
0 Votes"
CourtneyHaedke-0265 answered

Hi @SilvanoP-4597

I have a solution here is what you need to do:
l verify if the credential has been dropped or does not exist any longer
use master;
select * from sys.database_scoped_credentials;


Recreate if missing (only change your ADLS path)
This authenticates using managed Identity.
CREATE CREDENTIAL [https://<storage_account>.test.core.windows.net/<container>;]
WITH IDENTITY='Managed Identity'

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ciao @CourtneyHaedke-0265 ,

thank you so much for your feedback, it worked!

The table was empty, I run your command, table it is still empty, but now query works from SSMS.

I'm wondering if the issue was caused by the fact that I created another Synapse workspace (to leverage an offer) pointing to the same ADLS Gen2.

So I have two workspaces pointing to the same ADLS.

Support told me that this was not an issue at all, but after I did this I got this CREDENTIAL issue, wondering if you did something similar.

Anyway thank you so much!




0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CourtneyHaedke-0265 converted comment to answer

Hi @CourtneyHaedke-0265,

Did you create database scoped credential for your database test?

 -- create master key that will protect the credentials:
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = <enter very strong password here>
    
 -- create credentials for containers in our demo storage account
 CREATE DATABASE SCOPED CREDENTIAL sqlondemand
 WITH IDENTITY='SHARED ACCESS SIGNATURE',  
 SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
 GO
 CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
     LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
     CREDENTIAL = sqlondemand
 );

Please refer to SQL On-Demand: An easier way to Query Data and Quickstart: Use serverless SQL pool to get more details.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Carrin,

The initial set up to create the spark databases and tables did not require a master key to be created. Why has is changed all of a sudden?

0 Votes 0 ·

@CarrinWu-MSFT As I said in my previous message I tried those steps.
I'm getting the below error
Msg 15875, Level 16, State 8, Line 2
Operation DROP EXTERNAL DATA SOURCE is not allowed for a replicated database.
Msg 15875, Level 16, State 7, Line 6
Operation CREATE EXTERNAL DATA SOURCE is not allowed for a replicated database
*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 create scope credential within the spark database but when I go to drop an recreate the data source I get the below error.

In the past when you just created a spark table using the below simple pyspark command the database would create it own external source with credentials.
spark.sql("CREATE TABLE IF NOT EXISTS test.testparquet USING parquet LOCATION 'abfss://output@test.test.core.windows.net/test/output'")
What has changed since then?

0 Votes 0 ·

Hello @CarrinWu-MSFT having a similar issue, never creates a master key, everything has been working for months what has changed during the WE, my pipeline are failing, what's next?

1 Vote 1 ·

Hi @CourtneyHaedke-0265, could you please get the error details from error log? Please opent the latest one by Notepad, and search 'error' and you will get more details about this issue. Path: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log

0 Votes 0 ·