2

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.