Error with a replicated database on Azure Synapse Serverless

I'm trying to add a login/User to a replicated database on Azure Synapse Serverless.

I created a new login/User

CREATE LOGIN <Name> WITH PASSWORD = 'Password';
CREATE USER <Name> FOR LOGIN <Name>;

Now I can access the db and see all tables. But when I tried to query a table I get:

> Cannot find the CREDENTIAL 'https://....... , because it does not exist or you do not have permission.

Then I created a scoped credential:

CREATE DATABASE SCOPED CREDENTIAL [SasToken]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sp=XXXX';

Now I'm stuck with an error when I try:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[SasToken] TO [Name];

The message is saying

> Operation GRANT is not allowed for a replicated database.

How to solve this. I only need a solution that works with Serverless. Thank you on advanced.

As Bhavani mentioned in their answer, Lake databases are replicated from and managed by Spark. I got around this by instead creating a new database in Serverless SQL outside of Spark.

To do this, you'll need to have Spark write out your tables to Azure Data Lake Gen 2. I've chosen to use Delta tables. You can then create a database scoped credential for the Managed Service Identity, an external data source that represents the root folder which contains your Delta table folders, a role (if you like) to grant credential access to, and views to encapsulate the OPENROWSET queries on your Delta tables.

This can all be done in a SQL Script on the built-in serverless pool, or connected via Azure Data Studio or a similar client.

On the master database:

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = '[your database name]')
  CREATE DATABASE[your database name]

On your new database:

IF NOT EXISTS(SELECT * FROM sys.database_credentials WHERE name = 'ManagedIdentityCredential')
  BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD='[master encryption password]'
                    
    CREATE DATABASE SCOPED CREDENTIAL ManagedIdentityCredential
       WITH IDENTITY = 'MANAGED SERVICE IDENTITY'

    CREATE EXTERNAL DATA SOURCE [your_database_name]
       WITH (
         LOCATION = 'https://[storage_account_name].dfs.core.windows.net/path_to_database_files_root/',
         CREDENTIAL = ManagedIdentityCredential
       );
                    
  END

Create views for your tables:

CREATE OR ALTER VIEW [view_name] AS
  SELECT
  {columns}
  FROM
  OPENROWSET( BULK '/path_to_delta_table', DATA_SOURCE = '[your_database_name]', FORMAT='DELTA') AS Result

A role for granting access:

IF NOT EXISTS (select * from sys.database_principals where type='R' and name = '[role_name]')
    CREATE ROLE {role_name}
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[ManagedIdentityCredential] to [role_name]
GRANT SELECT ON SCHEMA::[dbo] TO [role_name]

Add your database user to that role:
ALTER ROLE [role_name] ADD MEMBER [user_name]

I hope this helps! In my particular use case I've automated this as a python script executed by the pipeline and used conventions to automatically generate views for folders within the database folder root.

I tried to replicate your issue in my environment

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[SasToken] TO [Name];

I got same error which you got

As per this above error is returned if you are trying to modify a Lake database, create external tables, external data sources, database scoped credentials or other objects in your Lake database. These objects can be created only on SQL databases.

The Lake databases are replicated from the Apache Spark pool and managed by Apache Spark. Therefore, you cannot create objects like in SQL Databases by using T-SQL language. So, it is not possible to perform these types of operations on data lake database.