How to access database by External Datasource using Service Principal Name(SPN)

Question

I am seeking a solution for cross-database access via an external data source using Service Principal credentials. Specifically, I want to perform Transact-SQL queries from one database to another using Service Principal credentials. To achieve this, I am using Service Principal Name(SPN) for creating required Database Scoped Credentials (DSC) and External Datasource.

Here is how I am creating the DSC:

CREATE DATABASE SCOPED CREDENTIAL testManageAccessSPN2 WITH IDENTITY = 'CLIENT_ID' , SECRET = 'CLIENT_SECRET';

Where CLIENT_ID is a special id from the overview tab of App registration (Application (client) ID) And Secret = CLIENT_SECRET from the "Certificate & secrets"

Format:
CLIENT_ID = 00000000-0000-0000-0000-000000000000

CLIENT_SECRET = O7q8Q~otvRVdha.EmqEsyxxxxxxxxxxxxx

However, when I try to access the database using these credentials, I encounter the following error:

An error occurred while establishing connection to remote data source: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user '

I have tested these credentials by logging into SSMS, and they are working fine. Please assist me with a possible solution. Thanks.

Answer 1

Based on my understanding, only SQL auth is supported for cross database queries and AD auth is not supported.

https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical?view=azuresql#database-scoped-master-key-and-credentials

Answer 2

Have you tried adding user to db_owner role ?

Check these old threads :

https://learn.microsoft.com/en-us/answers/questions/1336664/elastic-database-query-failing

https://stackoverflow.com/questions/74010044/error-an-error-occurred-while-establishing-connection-to-remote-data-source

Answer 3

Hi @Devendra Parmar

Here are some resolutions from this blog:

Enable the option "Allow Azure services and resources to access this server" and to set the option "Public network access" to "Selected networks" in the remote Azure SQL Database server's firewall configuration.

If "Allow Azure services" is not secure enough for your requirements, you could also try to create IP address firewall rules for each of the local databases. You need to catch the firewall errors and get the public IP address of the database that is attempting to log in. You could then add that IP in a firewall rule to the remote server and retry the query. This approach is relatively complex to implement, as it is a manual process and cannot be automated. It is also possible that the IP address may change in the future.

If neither of these is a valid option, you may consider moving the Azure SQL Databases to an Azure SQL Managed Instance, which natively allows cross-database queries.  

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".