question

Rock-1931 avatar image
0 Votes"
Rock-1931 asked PRADEEPCHEEKATLA-MSFT commented

Synapse server credential using key vault

Hi

I want to create a server-level or database scoped credential with azure Key vault in a Synapse workspace. Below is the example from MS


/ Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
CREATE CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
/


SELECT TOP 10 *
FROM OPENROWSET(
PROVIDER = 'CosmosDB',
CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
OBJECT = 'Ecdc',
SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential'
) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows


Instead of using Cosmos db key can we create a credential by using a key vault?

I tried to use below syntax but getting an error ' Incorrect syntax near 'IDENTITY'.

CREATE CREDENTIAL Azure_DF_Test
WITH IDENTITY = 'ContosoKeyVault',
SECRET = 'EF5C8E094D2A4A769998D93440D8115DSECRET_DBEngine'
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;

Thank you



azure-synapse-analytics
· 2
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.

Hello @Rock-1931,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.

0 Votes 0 ·

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';

The above statement is not working in Azure Synapse Studio for Serverless SQL database pool.
and returning below error:

Incorrect syntax near 'IDENTITY'.

Kindly suggest @PRADEEPCHEEKATLA-MSFT

Thanks

0 Votes 0 ·
PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @Rock-1931,

Thanks for the question and using MS Q&A platform.

Azure SQL Database only supports Azure Key Vault and Shared Access Signature identities.

138781-image.png

For more details, refer to CREATE CREDENTIAL (Transact-SQL).

Hope this will help. Please let us know if any further queries.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators


image.png (13.6 KiB)
· 6
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.

Hello @Rock-1931,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hi @PRADEEPCHEEKATLA-MSFT

               Thank you for the answer. I am new here, I tried to create a credential with Azure Key vault but not sure about the client Id they have mentioned in the document? Can you please help me with this?

Also, there is one more option in a synapse workspace to create a credential by using key vault but I am not able to use it in SQL queries. Can you please help me with this too?

0 Votes 0 ·

Hello @Rock-1931,

Could you please share the document which you are referring to?

0 Votes 0 ·

The document which you have mentioned above. This is the link

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql?view=sql-server-ver15

Create credential by using Key Vault.

0 Votes 0 ·

Hi @PRADEEPCHEEKATLA-MSFT, I just want to query the Cosmos DB in a Synapse workspace where we have to use the below syntax to create a credential that can be used in a SQL query (Openrowset ) :

/ Setup - create server-level or database scoped credential with Azure Cosmos DB account key:
CREATE CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'XYZ';
/

So my question here is can we create the above credential by using Key vault in a synapse workspace ? or is there any other way to create a credential by using key vault in a synapse that can be used in an open row set?

Please refer below document for Querying cosmos in a synapse :
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-cosmos-db-analytical-store?toc=%2Fazure%2Fcosmos-db%2Ftoc.json&bc=%2Fazure%2Fcosmos-db%2Fbreadcrumb%2Ftoc.json&tabs=openrowset-key

Thnak you

0 Votes 0 ·

Hello @Rock-1931,

As I explained on the above answer. Currently, only Azure SQL Database only supports Azure Key Vault and Shared Access Signature identities.

0 Votes 0 ·
SahilBansal-6386 avatar image
0 Votes"
SahilBansal-6386 answered PRADEEPCHEEKATLA-MSFT commented

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';


The above statement is not working in Azure Synapse Studio for Serverless SQL database pool.
and returning below error:

Incorrect syntax near 'IDENTITY'.

Kindly suggest @PRADEEPCHEEKATLA-MSFT

Thanks

· 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.