Share via

Vertical Elastic Query is not identifying Object

Sarvesh Pandey 141 Reputation points
2021-01-16T18:42:47.293+00:00

I did create an External Datasource, identical to the guide described here.
https://www.sqlservercentral.com/articles/cross-database-queries-in-azure-sql-database

The process is pretty simple, so just for illustration.

CREATE MASTER KEY ENCRYPTION ...
CREATE DATABASE SCOPED CREDENTIAL ...

CREATE LOGIN <externaldbname> WITH PASSWORD = '<somepassword1>';
CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE EXTERNAL DATA SOURCE ...
CREATE EXTERNAL TABLE ..
All executed successfully. Now when I try to select something from the external database, this error is raised

Msg 46823, Level 16, State 1, Line 3
Error retrieving data from sar*****.database.windows.net.carDB. The underlying error message received was: 'Invalid object name 'SalesLT.Addresstest'.'.

I didn't get the error msg as the method I followed is used/shown in multiple website and tutorials.

Azure SQL Database

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Martin Cairney 2,246 Reputation points
    2021-01-18T06:24:01.917+00:00

    It looks permissions/authentication related to me.

    Assuming that the 2 databases are on the same logical server, then your checklist of things to validate:

    There is a login in the master database named <externaldbname>
    There is a user in the "carDB" database named <externaldbname> mapped to the login
    The SalesLT.Addresstest table exists in carDB
    The user <externaldbname> in carDB has SELECT permissions on the SalesLT.Addresstest table

    **** Test the above by connecting to carDB as the user and selecting from SalesLT.addresstest ***

    There is a MASTER KEY created in the database that you are querying from
    There is a CREDENTIAL in the database that you are querying from whose identity matches the LOGIN that was created - same name and password.
    The EXTERNAL DATA SOURCE is created in the database you are querying from
    The EXTERNAL TABLE is created in the database you are querying from and matches the schema of SalesLT.Addresstest

    0 comments
  2. Anurag Sharma 17,591 Reputation points
    2021-01-18T09:37:23.667+00:00

    Hi @Sarvesh Pandey , welcome to Microsoft Q&A forum.

    On top of the suggestion provided by @Martin Cairney , could you try adding below code while creating the external table: 

    CREATE EXTERNAL TABLE [dbo].[Address](  
	--column details  
) WITH  
(  
DATA_SOURCE = SQLSatDB,  
SCHEMA_NAME = 'SalesLT', --source database schema  
OBJECT_NAME = 'Addresstest'  
)

    In above script, schema_name and object_name belong to external database table we are referring to.

    Also can you please check if you provided the grant permission on the table as mentioned in the same article: 

    GRANT SELECT, INSERT ON dbo.SQLSatEvent TO QueryUser

    Please let us know if this works or else we can discuss further on the same.

    0 comments