Executing an OPENROWSET on azure synapse result in a 'Resolving CosmosDB path has failed with e-CodePudding

I am deploying to an azure synapse environment queries using sqlcmd to serverless pool. The environment contains an SQL database that my deploying account has access to.

I am creating first the credentials to access a cosmosDB with :

CREATE DATABASE SCOPED CREDENTIAL [mycosmos] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<accessKeyToCosmosAccount>'

Then using the openrowset with the created credentials to retrieve records from the aforementioned COSMOSDB

SELECT TOP (100) *
from OPENROWSET (
    PROVIDER = 'CosmosDB',
    CONNECTION = 'Account=mycosmos;Database=reporting;',
    OBJECT = 'data',
    CREDENTIAL = 'mycosmos'
) as o;

however executing the latter gives the following error: Resolving CosmosDB path has failed with error 'Secret is not base64 encoded.'.

Does anyone has tips or ideas on how to get more information or to understand the issue at hand?

the credentials are indeed created and I checked that by using:

SELECT * FROM SYS.database_scoped_credentials

I also tried to base64 the secret accessKeyToCosmosAccount using

echo $mysecret | tr -d '\n\r' | base64 -w 0

to no avail (I still keep getting the same error)

thanks

CodePudding user response：

I tried to reproduce similar scenario in my environment and faced similar error.

enter image description here

After researching I found that cause of error is the incorrect secret. To resolve this, check your secret/primary key is correct.

To get connection string you can go to your cosmos db account >> Settings >> Keys >> Primary Key.

enter image description here

My code:

CREATE  CREDENTIAL MyCosmosDbAccountCredential
WITH  IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'primary key from cosmos db';

SELECT  TOP  10 *
FROM  OPENROWSET(
PROVIDER = 'CosmosDB',
CONNECTION = 'Account=cosmosdb_name;Database=databassename',
OBJECT = 'container2',
SERVER_CREDENTIAL = 'MyCosmosDbAccountCredential1'
) with ( id varchar(10), name  varchar(10) ) as  rows

Execution and Output:

enter image description here

CodePudding user response：

Hi thanks for your quick answer. turns out it was indeed the secret which we retrieve with an az cosmosdb keys list --name <name> -g <resource-group> --query 'primaryReadonlyMasterKey' -o tsv

Something wrong happens when we use the sqlcmd and pass the secret with -v. turns out passing variables with = sign results in sqlcmd trimming the rest (see Escape variable in sqlcmd / Invoke-SqlCmd)

I think this issue is not much relevant to Synapse than it is to sqlcmd. I will continue the conversation there.