Can't Create Database Scoped Credentials in Serverless synapse Database

Teja.Gudipati 31 Reputation points
2022-08-15T22:57:56.153+00:00

When I run the below script, I am getting an error saying Incorrect syntax near IDENTITY

231179-image.png

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,346 questions
0 comments No comments
{count} votes

Accepted answer
  1. Alberto Morillo 32,806 Reputation points MVP
    2022-08-16T00:25:12.82+00:00

    Please read the steps provided on this article.

    WITH IDENTITY = 'Managed Identity'

    USE [master]  
    GO  
      
    -- Create the lake house logic database  
    IF db_id('nyctaxidwdelta') IS NULL   
    EXEC('CREATE DATABASE nyctaxidwdelta COLLATE Latin1_General_100_BIN2_UTF8')  
    GO  
      
    USE [nyctaxidwdelta]  
    GO  
      
    -- Create a master key  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'blabla!'  
      
      
    GO  
    -- Create database scoped credential that use Synapse Managed Identity   
    CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity  
    WITH IDENTITY = 'Managed Identity'  
    GO  
      
    -- Create external data source   
    IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'eds_nyctaxi')   
     CREATE EXTERNAL DATA SOURCE [eds_nyctaxi]   
     WITH (  
     LOCATION   = 'https://mystorage.dfs.core.windows.net/lakedata/',  
            CREDENTIAL =  WorkspaceIdentity    
     )  
    GO  
    

1 additional answer

Sort by: Most helpful
  1. Agneum 26 Reputation points
    2023-02-03T10:59:54.74+00:00

    I have the same issue. I cannot run the query

    CREATE DATABASE SCOPED CREDENTIAL TestCredential
    WITH IDENTITY = 'xxx',
    	SECRET = 'yyy';
    
    0 comments No comments