Azure SQL Creating Database Scoped Credential

Question

I'm trying to create a scoped credential in azure SQL using SSMS.

CREATE DATABASE SCOPED CREDENTIAL [cred-name] WITH IDENTITY = [db-user], SECRET = 'password'

I keep running into the error message stating "Incorrect syntax near 'cred-name'. Expected '='." I'm not sure how my syntax is incorrect as I've done this exact command successfully in the past so I'm not sure what has changed. I thought maybe it was just intellisense that was messing up so I updated my SSMS instance from 17.3 to 17.7 but I still get the same error message.

Does anyone have any idea of what could have changed?

Answer 1

Running the exactly T-SQL you posted against Microsoft SQL Azure (RTM) - 12.0.2000.8 May 4 2018 13:05:56 version leads to the following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'db-user'.

Replacing the identity name brackets for single quotes leads to the error below:

Msg 15581, Level 16, State 6, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.

Creating the master key with following T-SQL allows me to create the credential successfully:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='MyPassw0rdIsComplex.'
GO

CREATE DATABASE SCOPED CREDENTIAL [cred-name] WITH IDENTITY = 'db-user' , SECRET = 'password'
GO

Also, you can check if the scoped credential using the following query:

SELECT * FROM sys.database_scoped_credentials WHERE credential_identity='db-user'

I'm using SSMS version 17.2, but I'm not sure if this matters since errors would come from the SQL Server engine itself.

Answer 2

The master key needs to be open to enable encryption of the database scoped credential.

So if a master key exists already, you can write the following:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<your password>';
CREATE DATABASE SCOPED CREDENTIAL [<your credential>]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<your SAS secret without the preleading ?>';
CLOSE MASTER KEY; -- only necessary if you need to close the master key context and continue scripting. (it will close with the session/query close)
... 

if no master key exists, you can write:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<your password>';
CREATE DATABASE SCOPED CREDENTIAL [<your credential>]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<your SAS secret without the preleading ?>';
CLOSE MASTER KEY; -- only necessary if you need to close the master key context. (it will close with the session/query close)
... 

For some reason intellisense does not understand the syntax - but it will execute as it should regardless.