Can't Access Azure Synapse Spark Tables through SSMS

Question

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:

5) Then when I go to query the spark database and table I get the below 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 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

Answer 1

Hi @Silvano P

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'

Answer 2

Hi @Courtney Haedke ,

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.