How to access data of one DB from another using Elastic Job?

Question

I am trying to access data from one DB to another DB.For that I am using Elastic Job.Using Elastic Job I am to create table from one DB to another.But not able to access data or transfer data.I tried it using External Data source and External Table.

I used the below code : External Data Source

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='myserver',
    DATABASE_NAME='dbname',
    CREDENTIAL= JobRun
);

CREATE EXTERNAL TABLE [tablename] (
[Id] int null,
[Name] nvarchar(max) null
) 
WITH (

     DATA_SOURCE = RemoteReferenceData,
     SCHEMA_NAME = N'dbo', 
OBJECT_NAME = N'mytablename'

);

Getting error below:

> Error retrieving data from server.dbname.  The underlying error
> message received was: 'The server principal "JobUser" is not able to
> access the database "dbname" under the current security context.
> Cannot open database "dbname" requested by the login. The login
> failed. Login failed for user 'JobUser'.

Both the DB's are in same server so i hope refresh credential is not necessary — akshaya parimaladevi, Mar 11 '20 at 06:51
@akshayaparimaladevi Please see my answer, and hope it can help you solve the error. If my answer is helpful for you, you could mark it as answer This can be beneficial to other community members. Thank you. — Leon Yue, Mar 17 '20 at 07:18

score 0 · Answer 1 · answered Mar 17 '20 at 07:17

There are some errors in you statements:

the LOCATION value should be: LOCATION='[servername].database.windows.net'
Make sure when you create the CREDENTIAL: The "username" and "password" should be the username and password used to log in into the Customers database. Authentication using Azure Active Directory with elastic queries is not currently supported.

The whole T-SQL code example should be like this:

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
 WITH IDENTITY = 'Username',
 SECRET = 'Password';  

 CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
    (TYPE = RDBMS,
    LOCATION = '[servername].database.windows.net',
    DATABASE_NAME = 'Mydatabase',
    CREDENTIAL = ElasticDBQueryCred,
) ;

CREATE EXTERNAL TABLE [dbo].[CustomerInformation]
( [CustomerID] [int] NOT NULL,
  [CustomerName] [varchar](50) NOT NULL,
  [Company] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc)


SELECT * FROM CustomerInformation

I using the code to query the table in Mydatabase from DB1:

For more details, ref here: Get started with cross-database queries (vertical partitioning) (preview)

Hope this helps.

How to access data of one DB from another using Elastic Job?

1 Answers1