External table creation based on table from another server

Gowri Shankar 211 Reputation points
2021-08-16T13:20:42.01+00:00

Hi,

I am trying to create an External table in one database server , by accessing some table from another Remote Database server.

Creation of External Data source as well as External table was successful. But when I try to query the External Table, it is giving me the below error:

Error Message:
Msg 46824, Level 16, State 1, Line 1
Login failed on remoteserver.windows.net.DB_name. Please verify that the shards are accessible and that the credential information affiliated with external data source testExtDtaSource is correct.

Can you please confirm whether it is possible to create External table and get the data in one database server from another remote server ?

Attached is the script used for creating external table.123636-script-used.txt

Kind Regards,
Gowri Shankar

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics

An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.


1 answer

Sort by: Newest
  1. Anurag Sharma 16,961 Reputation points
    2021-08-17T07:27:44.877+00:00

    Hi @Gowri Shankar , thanks for replying back.

    Yes cross-database queries is supported in Azure SQL and known as Elastic query, we can query cross multiple databases in same server or databases across different servers as well. Please check the sequence of steps I followed:

    1. Created first SQL Database server Customers with username 'admin' and password 'password@123'
    2. Created another SQL Database server Orders with username 'admin' and password 'password@123'(You can choose different username and password here)
    3. Login to Orders database and run below query: CREATE TABLE [dbo].OrderInformation
      INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (123, 1)
      INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (149, 2)
      INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (857, 2)
      INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (321, 1)
      INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (564, 8)
    4. Login to Customers Database run below query: CREATE TABLE [dbo].CustomerInformation
      INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (1, 'Jack', 'ABC')
      INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (2, 'Steve', 'XYZ')
      INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (3, 'Lylla', 'MNO')
    5. Again go back to Orders database and run below query: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@rahul kumar ';
      CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
      WITH IDENTITY = 'admin',
      SECRET = 'password@123';

    Please note the identity and password we use here is same what we used for Customers database. If the passwords don't match we get the exact same error mentioned.

    6). Create external data source in Orders database as below:

    CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH  
        (TYPE = RDBMS,  
        LOCATION = '<server_name>.database.windows.net',  
        DATABASE_NAME = 'Customers',  
        CREDENTIAL = ElasticDBQueryCred,  
    ) ;  
    

    7). Create external data table in Orders database as below:

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

    8). Then we can execute the sample elastic query in Orders database as below:

     SELECT OrderInformation.CustomerID, OrderInformation.OrderId, CustomerInformation.CustomerName, CustomerInformation.Company  
    FROM OrderInformation  
    INNER JOIN CustomerInformation  
    ON CustomerInformation.CustomerID = OrderInformation.CustomerID  
    

    Reference Article: Get started with cross-database queries (vertical partitioning) (preview)

    Please let me know if this helps or else we can discuss further on the same.

    ----------

    If answer helps you, you can mark it 'Accept Answer'

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.