question

BattulaSuchit-4551 avatar image
0 Votes"
BattulaSuchit-4551 asked SaurabhSharma-msft commented

Query Error Message after Creating EXTERNAL DATA SOURCE In/from Azure SQL Database to Azure Synapse(formerly Azure SQL DWH)

I'm trying to query between databases.
Using the following documentation :

https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-scale-get-started

When I want to query the external table I created as below:

CREATE DATABASE SCOPED CREDENTIAL Myxxxxxx
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx';

CREATE EXTERNAL DATA SOURCE aaaaaaaaaaa
WITH
(
TYPE=SHARD_MAP_MANAGER,
LOCATION='xxxxxxxx.database.windows.net',
DATABASE_NAME='abc',
CREDENTIAL= Myxxxxxx,
SHARD_MAP_NAME='aaaaaaaaaaa'
);

CREATE EXTERNAL TABLE [dbo].[Dim_abcd]
(
[Col1] [bigint] NOT NULL,
[Col2] [char](9) NULL,
[Col3] [varchar](250) NULL,
[Col4] [varchar](17) NULL,
[Col5] [int] NOT NULL,
)
WITH
(

 DATA_SOURCE = aaaaaaaaaaa,
 DISTRIBUTION = ROUND_ROBIN

)
GO

While querying i get error message


Error Message :
Error accessing the shard map manager database.
Please verify that the shard map manager database is available.
If it is available, please verify that the definition and affiliated credential are correct for external data source xxxxxxxxxxxxx.

Note : Azure SQL Database is Server1 and Azure Synapse ( Azure SQL data warehouse) is in Server2

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered SaurabhSharma-msft commented

Hi @battulasuchit-4551,

Thanks for using Microsoft Q&A !!
I am able to reproduce your issue and got the same error -
117568-image.png

Now in order to fix this issue, you need to create you database scoped credential using the same username and password which has access to your shadmapmanager database and shards ( i.e. you can use the same credentials you have used to create the ShardMapManager and Shards databases i.e. Server 1) instead of using 'SHARED ACCESS SIGNATURE' as it is used against Type = BLOB_STORAGE.

 CREATE DATABASE SCOPED CREDENTIAL shardelasticcredential
 WITH IDENTITY = 'username',
 SECRET = 'password';

Please refer to the documentation for detailed steps.

Here is code snippet which I have used on Azure SQL Database (Server 2) -

 CREATE DATABASE SCOPED CREDENTIAL shardelasticcredential
 WITH IDENTITY = 'username',
 SECRET = 'password';
    
 CREATE EXTERNAL DATA SOURCE ShardElasticServer
 WITH
 (
 TYPE=SHARD_MAP_MANAGER,
 LOCATION='<servername>.database.windows.net',
 DATABASE_NAME='ElasticScaleStarterKit_ShardMapManagerDb',
 CREDENTIAL= shardelasticcredential,
 SHARD_MAP_NAME='CustomerIDShardMap'
 );
    
 CREATE EXTERNAL TABLE [dbo].[Customers]
 ( [CustomerId] [int] NOT NULL,
     [Name] [nvarchar](256) NOT NULL,
     [RegionId] [int] NOT NULL)
 WITH
 ( DATA_SOURCE = ShardElasticServer,
     DISTRIBUTION = SHARDED([CustomerId])
 ) ;


Also, you need to set "Allow Azure Services and Resources to access this server" setting to Yes from "Firewalls and Virtual Networks" of your ShardMap server on Azure portal.

117593-image.png

I am curious though (if I understood it correctly) how were you able to create CREATE EXTERNAL DATA SOURCE on Azure Synapse with TYPE = 'SHARD_MAP_MANAGER' as I am getting error while creating the credential as TYPE = 'HADOOP' is only supported as per this documentation.
117556-image.png

Thanks
Saurabh


Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.


image.png (19.2 KiB)
image.png (89.5 KiB)
image.png (22.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @battulasuchit-4551,

We haven't heard back from you. Just wanted to check if you are you still facing the issue? In case If you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Thanks
Saurabh

0 Votes 0 ·

Hi @battulasuchit-4551,

Please let me know if you are still facing this issue and needs any help.

Thanks
Saurabh

0 Votes 0 ·