Azure SQL Database – Is CREATE EXTERNAL DATA SOURCE Broken?

azure-sql-databasesql server

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?

Best Answer

You need to create the db scoped credential and the external data source all on the same (destination) database.

The IDENTITY='' should be an account that has access to the source database where you want to query from, in my example Test2.

So for example to access database Test2's tables from database test, execute all this on the database: Test.

USE TEST
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STRONGP@SSWORD124321';
GO
CREATE DATABASE SCOPED CREDENTIAL CRED_Azure_Login
WITH IDENTITY = 'SomeUser', SECRET = 'STRONGP@SSWORD124321' -- this user needs to  have sufficient rights on the test2 db.
GO

CREATE EXTERNAL DATA SOURCE Source_AzureDatabase
WITH
(
    TYPE=RDBMS,
    LOCATION='<DB location>.database.windows.net',
    DATABASE_NAME='Test2',
    CREDENTIAL= CRED_Azure_Login
);

CREATE EXTERNAL TABLE [dbo].[test]
([Test ID] int)
WITH
(
DATA_SOURCE= Source_AzureDatabase
);

Source

Related Question