How to query data inside azure sql db from azure ml studio within private network?

Question

Hi,

I try to query data which is in an azure sql db from azure machine learning studio from within the same private network based on a managed identity.

This error appears:

DatasetValidationError: DatasetValidationError: Message: Cannot load any data from the datastore using the SQL query "SELECT getdate()". Please make sure the datastore and query is correct. Database connection failed due to authentication with error code: "18456", error_class: "14" on server: "SQL Azure"

I checked the following documentation:

https://learn.microsoft.com/en-us/python/api/azureml-core/azureml.core.datastore.datastore?view=azure-ml-py#azureml-core-datastore-datastore-register-azure-sql-database

It says to give the managed identity owner or "user access adminsitration of the storage" rights to the managed identity.

I tried with the above rights forthe managed identity of my user assigned compute instance. however, it does not work.

Which settings am I missing?

This is the code I try:

from azureml.core import Workspace, Datastore, Dataset
#from azure.ai.ml.entities import register_azure_sql_database

ws = Workspace.from_config()

Datastore.register_azure_sql_database(workspace=ws, 
    datastore_name="sql_test",  
    server_name="server_xxx", 
    database_name="database_xxx", 
    subscription_id="subscription_xxx",
    resource_group="resourcegroup_xxx", 
    grant_workspace_access=True # only working with Owner-Rights of users managed identity
    )

query = """SELECT getdate()"""

from azureml.data.datapath import DataPath
from azureml.core import Workspace, Datastore, Dataset
import pandas as pd

ws = Workspace.from_config()
sql_datastore = Datastore.get(ws, datastore_name="sql_test")

query = DataPath(sql_datastore, query)

test_report = Dataset.Tabular.from_sql_query(query).to_pandas_dataframe()
test_report

Answer 1

Hi Moritz Damm,

Welcome to Microsoft Q&A Forum, thank you for posting your query here!

The error suggests that there might be issues in SQL query syntax or datastore/networking configuration or authentication issue on connecting to SQL database.

Here are the suggested steps to check each steam

1. SQL query syntax

query = """SELECT getdate()""" #contains extra double quotes
to
query = "SELECT GETDATE()" #Validate the query in the SQL server first.

2. On Datastore registration

Please make sure to provide username,password and endpoint details along other details

 sql_datastore = Datastore.register_azure_sql_database(
       workspace=ws,
       datastore_name=sql_datastore_name,
       server_name=server_name,  # name should not contain fully qualified domain endpoint
       database_name=database_name,
       username=username,
       password=password,
       subscription_id="subscription_xxx",
       resource_group="resourcegroup_xxx", 
       grant_workspace_access=True
       endpoint='database.windows.net')

3. On Network Security Group

Please make sure provided outbound access to SQL service tag at port 443 from your virtual network in your Network Security Group or firewall.

how-to-access-azureml-behind-firewall

4. Please make sure workspace has been provider reader and contributor access over SQL

5. Managed identity and SQL are in same virtual network and region and DNS resolution is happening for the private resources to their private ip .

how-to-network-security-overview

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful.

Thank you.