Vertical Elastic Query is not identifying Object

azureazure-sql-databasequery

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.

Best Answer

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
There is a user in the "carDB" database named mapped to the login
The SalesLT.Addresstest table exists in carDB
The user 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

Related Solutions

Create user on Azure elastic database and deny view to other databases i same setting

Reading you comment I see now what you are looking for.

Here is how you can do this.

Create 2 empty database:

USE [master]
GO
CREATE DATABASE [TestPerm1]
GO
ALTER DATABASE [TestPerm1] SET COMPATIBILITY_LEVEL = 130
GO


USE [master]
GO    
CREATE DATABASE [TestPerm2]
GO    
ALTER DATABASE [TestPerm2] SET COMPATIBILITY_LEVEL = 130
GO

Create 2 login

USE [master]
GO    
CREATE LOGIN [testPerm1] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [master]
GO    
CREATE LOGIN [testPerm2] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Create a user in master using one of the login

USE [master]
GO
CREATE USER [testPerm2] FOR LOGIN [testPerm2] WITH DEFAULT_SCHEMA=[dbo]
GO

Using SSMS connect with user testPerm2 using default setting. You will be able to see all database ( I know you do not want this, but hang on)

Create a user with testPerm1 in testPerm1 database that we created earlier.

USE [TestPerm1]
GO
CREATE USER [testPerm1] FOR LOGIN [testPerm1] WITH DEFAULT_SCHEMA=[dbo]
GO

Now using SSMS connect to the server by changing default database, this user will only see testPerm1 but not testPerm2.

Is the database in the elastic pool

Looks like the database is not in the pool. Go to the Elastic Pool in the portal and click on Configure Pool, and Add Databases.

Best Answer

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

Related Solutions

Create user on Azure elastic database and deny view to other databases i same setting

Is the database in the elastic pool

Related Question