question

gowrishankar-5586 avatar image
0 Votes"
gowrishankar-5586 asked AnuragSharma-MSFT answered

External table creation based on table from another server

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-databaseazure-synapse-analytics
script-used.txt (663 B)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @gowrishankar-5586, welcome to Microsoft Q&A forum.

This error mostly is encountered when the password of login identity does not match with the secret value of Scoped credential.

 CREATE DATABASE SCOPED CREDENTIAL SQL_Credential    
 WITH IDENTITY = 'abcde',  
 SECRET =  <password of abcde for below external data source>; ;  

In above SQL Statement, the identity 'abcde' must be a login with some password set while creation. We need to use the same password with the secret mentioned here.

Could you please try it once and let us know if this works?

0 Votes 0 ·

Hi @AnuragSharma-MSFT ,

Thanks for replying.

We are already using ýour same suggestion while creating 'In above SQL Statement, the identity 'abcde' must be a login with some password set while creation'' and we are getting the 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 ?

Kind Regards,
Gowri Shankar



0 Votes 0 ·

1 Answer

AnuragSharma-MSFT avatar image
1 Vote"
AnuragSharma-MSFT answered

Hi @gowrishankar-5586, 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](
    [OrderID] [int] NOT NULL,
    [CustomerID] [int] NOT NULL
    )
    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](
    [CustomerID] [int] NOT NULL,
    [CustomerName] [varchar](50) NULL,
    [Company] [varchar](50) NULL
    CONSTRAINT [CustID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
    )
    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@12345';
    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'








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.