I have set up Azure elastic database queries before by using the following on Azure PaaS databases:
On the data source database:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<encryption key>'; GO CREATE DATABASE SCOPED CREDENTIAL CRED_Azure_Login WITH IDENTITY = 'SomeUser', SECRET = '<Some password>' GO
This run, no problems and I can see the credential created when looking at sys.database_scoped_credentials. So it seems the source is fine.
On the destination database:
CREATE EXTERNAL DATA SOURCE Source_AzureDatabase WITH ( TYPE=RDBMS, LOCATION='<DB location>.database.windows.net', DATABASE_NAME='SourceDBName', CREDENTIAL= CRED_Azure_Login );
This used to work a-OK, but I get the following error:
Msg 46516, Level 16, State 26, Line 1 The specified credential cannot be found or the user does not have permission to perform this action.
I am using the admin login in the destination database (I changed the admin login via the Azure Portal Overview page for the database and re logged in, just to be sure). The documentation says you need CONTROL permission on the database, so this shouldn't be an issue. As I have successfully set up the source databases Master Key and credential, linking to it should not be a problem.
Can someone else try this to see if it is just me? Another DBA has reviewed the code and can't find any problems. Feels like Microsoft might have broken this feature - can someone else check they can do it?