CREATE DATABASE SCOPED CREDENTIAL on APS database fails

Question

I have been trying to follow the instructions on this APS to Azure Synapse Migration - 5.DeployScriptsToSynapse video https://www.youtube.com/watch?v=oorsuHHs7Ls&list=PLTPqkIPx9Hx8-dxWWv9Wyup2RQMMa6lHx&index=9 but when trying to create a database scoped credential on an APS database using the command

-- Replace <storageaccountname> and <storageaccountkey> with actual values valid in your environment
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential WITH IDENTITY = '<storageaccountname>',
    SECRET = '<storageaccountkey>';
GO

it fails with the error

"Incorrect syntax near 'AzureStorageCredential'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon."

I think this could be due to the version of Microsoft Analytics Platform System which is Microsoft SQL Server 2012 - 10.0.8035.0 (X64) Feb 28 2017 15:33:36 Copyright (c) Microsoft Corporation Parallel Data Warehouse (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) as on the video it shows that it does work on the APS database there and on all online documentation that is the correct commnd.

Does anybody know a workaround to be able to create the database scoped credential or external data source on this version of APS please?

Answer 1

can you place this query into a new query tab(remove all other queries) and try? i tried from my database it does work! it has no restriction to APS or specific databases.

Answer 2

Hi Vinod

Thank for the reply. I had tried to execute the

-- Replace <storageaccountname> and <storageaccountkey> with actual values valid in your environment
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential WITH IDENTITY = '<storageaccountname>',
    SECRET = '<storageaccountkey>';
GO

command in a new query but it did give the error

"Incorrect syntax near 'AzureStorageCredential'.

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon."

Like you said from what I can see and everything I've read it should be able to execute this on an APS database but it doesn't work on the ones on the APS server we have.

I was wondering what version of APS you're using please as there are a couple of objects in some of the other **Migrate APS/PDW to Azure Synapse Analytics playlist **videos that were not there so I can only think it might be because the APS server is an older version?

Answer 3

I meant you try to execute in a new window against another database, not against APS. Just run the below without any other text, not even the comments.

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential WITH IDENTITY = '<storageaccountname>', SECRET = '<storageaccountkey>';