Elastic Database Query Failing

Question

Elastic Database Query Failing

Hi All

So I have followed all the steps in Get started with cross-database queries But when I try and run a select on the External table I am getting the following error:

Msg 46832, Level 16, State 3, Line 2 An error occurred while establishing connection to remote data source: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'WFBE'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'WFBE'.

WFBE has a login and is set as server admin in Azure

under the tables though the user is USER [dbo] FOR LOGIN [WFBE] WITH DEFAULT_SCHEMA=[dbo]

I have tried setting up
CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred

WITH IDENTITY = 'USER',

SECRET = 'password';

For but dbo and WFBE but keep getting the above.

The table is on the same server but different DB, all databases are in the same SQL elastic pool on the AZURE SQL server

In Azure networking is set to

public network access - Selectednetworks

Allow Azure services and resources to access this server is ticked

Any question or any screen shoots needed then please let me know.

ShaktiSingh-MSFT 16,271 Reputation points

2023-07-25T11:32:26.8033333+00:00

Hi Greenwood, Justin -Administrator •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are getting error in doing cross database queries in Elastic Azure DB.

Could you please confirm if you tried CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master_key_password>';?

Please describe what all steps are done.

Thanks.

Greenwood, Justin -Administrator 75

Hi @ShaktiSingh-MSFT

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master_key_password>';

Done

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred WITH IDENTITY = '<username>', SECRET = '<password>';

Done

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
    (TYPE = RDBMS,
    LOCATION = '<server_name>.database.windows.net',
    DATABASE_NAME = 'Customers',
    CREDENTIAL = ElasticDBQueryCred,
) ;

Done

CREATE EXTERNAL TABLE [dbo].[CustomerInformation]
( [CustomerID] [int] NOT NULL,
    [CustomerName] [varchar](50) NOT NULL,
    [Company] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc)

Done

Please note I have changed the info in my scrips to what I need.

When I am in the database with the external table showing in the object panel and try to execute:

SELECT * FROM [dbo].[CustomerInformation] is when I get the Error.

I have checked and the user can log on to the server as it is the server admin account I am using.

Greenwood, Justin -Administrator 75 Reputation points

2023-07-26T09:12:00.7966667+00:00
So do I need to do this:

ALTER EXTERNAL DATA SOURCE ElasticDBQueryDataSrc SET CREDENTIAL = ElasticQueryUser ;
Greenwood, Justin -Administrator 75 Reputation points

2023-07-26T09:28:39.4266667+00:00

error
ShaktiSingh-MSFT 16,271 Reputation points

2023-07-27T10:54:39.8666667+00:00

Hi Greenwood, Justin -Administrator •,

Sorry to know.

Could you share details of the error please?
Greenwood, Justin -Administrator 75 Reputation points

2023-07-27T13:07:08.52+00:00

Managed to do it by adding user to db_owner role
ShaktiSingh-MSFT 16,271 Reputation points

2023-07-28T04:17:40.19+00:00

Hi Greenwood, Justin -Administrator •,

Good to know that you are able to proceed.

Appreciate for sharing the solution with Q&A Community.

Thanks
ShaktiSingh-MSFT 16,271 Reputation points

2023-07-31T04:46:53.52+00:00

Hi Greenwood, Justin -Administrator •,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Thanks

Accepted answer

1 additional answer

Your answer

ShaktiSingh-MSFT 16,271 Reputation points

2023-07-25T11:32:26.8033333+00:00

Hi Greenwood, Justin -Administrator •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are getting error in doing cross database queries in Elastic Azure DB.

Could you please confirm if you tried CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master_key_password>';?

Please describe what all steps are done.

Thanks.
Greenwood, Justin -Administrator 75 Reputation points

2023-07-25T12:09:55.61+00:00

Hi @ShaktiSingh-MSFT

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master_key_password>';

Done

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred WITH IDENTITY = '<username>', SECRET = '<password>';

Done

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH (TYPE = RDBMS, LOCATION = '<server_name>.database.windows.net', DATABASE_NAME = 'Customers', CREDENTIAL = ElasticDBQueryCred, ) ;

Done

CREATE EXTERNAL TABLE [dbo].[CustomerInformation] ( [CustomerID] [int] NOT NULL, [CustomerName] [varchar](50) NOT NULL, [Company] [varchar](50) NOT NULL) WITH ( DATA_SOURCE = MyElasticDBQueryDataSrc)

Done

Please note I have changed the info in my scrips to what I need.

When I am in the database with the external table showing in the object panel and try to execute:

SELECT * FROM [dbo].[CustomerInformation] is when I get the Error.

I have checked and the user can log on to the server as it is the server admin account I am using.
Greenwood, Justin -Administrator 75 Reputation points

2023-07-26T09:12:00.7966667+00:00

So do I need to do this:

ALTER EXTERNAL DATA SOURCE ElasticDBQueryDataSrc SET CREDENTIAL = ElasticQueryUser ;
Greenwood, Justin -Administrator 75 Reputation points

2023-07-26T09:28:39.4266667+00:00

error
ShaktiSingh-MSFT 16,271 Reputation points

2023-07-27T10:54:39.8666667+00:00

Hi Greenwood, Justin -Administrator •,

Sorry to know.

Could you share details of the error please?
Greenwood, Justin -Administrator 75 Reputation points

2023-07-27T13:07:08.52+00:00

Managed to do it by adding user to db_owner role
ShaktiSingh-MSFT 16,271 Reputation points

2023-07-28T04:17:40.19+00:00

Hi Greenwood, Justin -Administrator •,

Good to know that you are able to proceed.

Appreciate for sharing the solution with Q&A Community.

Thanks
ShaktiSingh-MSFT 16,271 Reputation points

2023-07-31T04:46:53.52+00:00

Hi Greenwood, Justin -Administrator •,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Thanks

Answer 1

ShaktiSingh-MSFT 16,271

Hi Greenwood, Justin -Administrator •,

As mentioned above, you have solved the error by adding user to db_owner role.

This is the solution which worked for the above question.

Thank you.

Answer 2

Hi Greenwood, Justin -Administrator •,

Please note that:

You must possess ALTER ANY EXTERNAL DATA SOURCE permission. This permission is included with the ALTER DATABASE permission. ALTER ANY EXTERNAL DATA SOURCE permissions are needed to refer to the underlying data source.

Also note the below Preview limitations:

Running your first elastic query can take up to a few minutes on smaller resources and Standard and General Purpose service tier. This time is necessary to load the elastic query functionality; loading performance improves with higher service tiers and compute sizes.
Scripting of external data sources or external tables from SSMS or SSDT is not yet supported.
Import/Export for SQL Database does not yet support external data sources and external tables. If you need to use Import/Export, drop these objects before exporting and then re-create them after importing.
Elastic query currently only supports read-only access to external tables. You can, however, use full Transact-SQL functionality on the database where the external table is defined. This can be useful to, e.g., persist temporary results using, for example, SELECT <column_list> INTO <local_table>, or to define stored procedures on the elastic query database that refer to external tables.
Except for nvarchar(max), LOB types (including spatial types) are not supported in external table definitions. As a workaround, you can create a view on the remote database that casts the LOB type into nvarchar(max), define your external table over the view instead of the base table and then cast it back into the original LOB type in your queries.
Columns of nvarchar(max) data type in result set disable advanced batching techniques used in Elastic Query implementation and may affect performance of query for an order of magnitude, or even two orders of magnitude in non-canonical use cases where large amount of non-aggregated data is being transferred as a result of query.
Column statistics over external tables are currently not supported. Table statistics are supported, but need to be created manually.
Cursors are not supported for external tables in Azure SQL Database.
Elastic query works with Azure SQL Database only. You cannot use it for querying a SQL Server instance.
Private links are currently not supported with elastic query for those databases that are targets of external data sources.

Let us know if this helped. Thank you.

Share via

Elastic Database Query Failing

1 additional answer

Your answer