[ Azure Synapse Serverless SQL Database ] : CREATE EXTERNAL TABLE to [ Azure SQL Database Serverless ]

Question

hi Community members,

I'm a beginner of Azure Services, trying to hands-on Azure Synapse Studio and also Azure SQL Database Serverless.

I managed to create external table reference ( with SQL scripts below ) to Azure SQL Database Serverless, but then when I try to execute select query using the table reference then encountered error message :

SELECT TOP 50 * FROM [dbo].[PUBLIC_HOLIDAYS]

"Bad or inaccessible location specified in external data source "Azure_DbServer".

Wondering whether problem with my configuration OR such integration not feasible/permissible with Azure Synapse ?

CREATE EXTERNAL FILE FORMAT DeltaFileFormatWITH ( FORMAT_TYPE = DELTA );

CREATE DATABASE SCOPED CREDENTIAL [MyCred] WITH IDENTITY = 'User Identity', SECRET = 'XXXXXXXX';

CREATE EXTERNAL DATA SOURCE Azure_DbServer
WITH (
    LOCATION = 'sqlserver://azure-dbserver.database.windows.net:1433',
    CREDENTIAL = [MyCred]
);

CREATE EXTERNAL TABLE PUBLIC_HOLIDAYS(
	[DATE_ID] [int],
	[FULL_DATE] [datetime],
	[PUBLIC_HOLIDAY] [varchar](255),
	[DESCRIPTION] [varchar](255) NULL
)
WITH (
      LOCATION = 'DbName.dbo.PUBLIC_HOLIDAYS',
      DATA_SOURCE = Azure_DbServer,
      FILE_FORMAT = DeltaFileFormat
);

Answer 1

Hi,

Thanks for reaching out to Microsoft Q&A.

The error message "Bad or inaccessible location specified in external data source 'Azure_DbServer'" indicates that there may be a configuration issue with your external data source or the way you're trying to access the Azure SQL Database Serverless from Azure Synapse.

1. External Data Source Configuration:
   • Ensure that the LOCATION specified in your CREATE EXTERNAL DATA SOURCE statement is correct. The format should be sqlserver://<server-name>.database.windows.net:1433. double-check the server name and ensure it matches your azure SQL db serverless instance.
   • Verify that the credentials used in CREATE DATABASE SCOPED CREDENTIAL [MyCred] are correct and have the necessary permissions to access the database.
2. Database and Table Access:
   • Confirm that the database and table you are trying to access (DbName.dbo.PUBLIC_HOLIDAYS) exist and that the user associated with the credentials has access to them.
   • Make sure the user has been granted the appropriate roles, such as db_datareader, to read from the specified table.
3. Network and Firewall Settings:
   • Check if there are any network restrictions or firewall rules that might be preventing Azure Synapse from connecting to your Azure SQL Database Serverless. Ensure that Synapse is allowed to access the SQL Database.
4. Querying External Tables:
   • When querying an external table, ensure that the SQL syntax is correct. The external table should point to a valid data source and file format. Since you've defined a DeltaFileFormat, ensure that this format is supported for the data you're trying to access.
5. Using Managed Identity:
   • If you are using a managed identity for authentication, ensure that the identity has been correctly configured and granted access to the Azure SQL Database.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop

Point to note:

• Serverless SQL Pool Limitations:
  • Note that serverless SQL pools have certain limitations regarding external tables, especially concerning the data formats they can handle. Ensure that the formats you are using are supported as per the azure doc .
• Testing Connectivity:
  • You can test the connectivity to your azureSQLdb serverless using tools like azure data studio or SSMS to ensure that the issue is specific to the Synapse setup.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.