0

We are using SQL Server 2012 on AZURE. I would like to use queries from different databases; As azure does not allow us to query from different databases, I decided to create an external table.

However, when I try to create

CREATE DATABASE SCOPED CREDENTIAL cred WITH IDENTITY = 'justin@gmail.com', SECRET = 'the password belonging to the username to access the source database';

I get an error saying The specified schema name "justin@gmail.com" either does not exist or you do not have permission to use it.

Could anyone let me know what the issue is?

Thank you, Justin

Justin
  • 321
  • 1
  • 5
  • 19
  • Are the different databases on the same server? Or your account executing the ddl statement does not have the permission. – Joseph Xu Jun 10 '21 at 02:19
  • Hello @HarrisXu - Yes, the databases are on the same server, and my account has DDL permissions. – Justin Jun 10 '21 at 10:20

1 Answers1

1

Per my experience, you're using the AAD user 'justin@gmail.com' to create the external table. Just for now, create the CREATE DATABASE SCOPED CREDENTIAL doesn't support login with AD authentication, that's why we can't use AD user to create the cross database query.

Azure documents didn't talk more about this. Please user the normal another user and password.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • I believe that you are right here. However, we are still on 2012 version, and windows login are not supported I think. what is your view on this? Thank you – Justin Jun 10 '21 at 19:28
  • thanks. What would then be an alternate approach to setup external tables if Azure AD credential and windows authentication is not supported? – Justin Jun 11 '21 at 05:06
  • Hi @Justin, As I know, there isn't such a alternate approach. – Leon Yue Jun 11 '21 at 05:27
  • Hmmm. The reason is I have seen people creating external tables on Azure SQL, and I am not sure how they do it then :D – Justin Jun 11 '21 at 05:42
  • @Justin ref this tutorial: https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical. You only can use the normal user( SQL authentication) to create the query. You can follow the example step by step. – Leon Yue Jun 11 '21 at 06:01