Vertical Elastic Query is not identifying Object

Question

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.

Answer 1

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

Answer 2

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.