SQL Azure cross DB Query Privileges

Question

I'm currently working through an example on cross database queries on SQL Azure. http://www.c-sharpcorner.com/article/cross-database-queries-in-azure-sql/

I'm currently working on the following section -

CREATE EXTERNAL DATA SOURCE RefmyDemoDB2  
WITH  
(  
    TYPE=RDBMS,  
    LOCATION='your server name',  
    DATABASE_NAME='myDemoDB2',  
    CREDENTIAL= your “Server admin login”  
);

I'm getting the following error The specified credential cannot be found or the user does not have permission to perform this action.

I have the correct LOCATION and DATABASE_NAME however the CREDENTIAL seems wrong. I am using the Server-Admin account that is displayed in the overview of the database server on Azure, I also use this role to log into management studio and can query both databases ok.

Can anyone please advise?

Jayendran Jayendran · Accepted Answer · 2017-09-28T10:46:46

Try this, Creating new Credentials

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STrongPA5swor$';

CREATE DATABASE SCOPED CREDENTIAL MyLogin
WITH IDENTITY = 'MyLogin',
SECRET = 'STrongPA5swor$';

CREATE EXTERNAL DATA SOURCE PHPSTGRemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='servername',
    DATABASE_NAME='DBName',
    CREDENTIAL= MyLogin
);

This works for me

SQL Azure cross DB Query Privileges

1 Answers