Why does my login to MS SQL with AzureML dataprep

Question

I tried connecting to a MS SQL database using azureml.dataprep in an Azure Notebook, as outlined in https://docs.microsoft.com/en-us/azure/machine-learning/service/how-to-load-data#load-sql-data, using MSSqlDataSource, using code of the form

import azureml.dataprep as dprep

secret = dprep.register_secret(value="[SECRET-PASSWORD]", id="[SECRET-ID]")

ds = dprep.MSSQLDataSource(server_name="[SERVER-NAME]",
                       database_name="[DATABASE-NAME], [PORT]",
                       user_name="[DATABASE-USERNAME]",
                       password=secret)

Setting [DATABASE-USERNAME] equal to MYWINDOWSDOMAIN\\MYWINDOWSUSERNAME and the password [SECRET-PASSWORD] coinciding with my Windows password (i.e. trying to use Windows authentication).

After firing a query with

dataflow = dprep.read_sql(ds, "SELECT top 100 * FROM [dbo].[MYTABLE]")
dataflow.head(5)

I get

ExecutionError: Login failed.

I could connect to other databases without Windows Authentication fine. What am I doing wrong?

Answer 1

Consider using SQL server authentication as a workaround/alternative solution to connect to that db (the same dataflow syntax will work):

import azureml.dataprep as dprep
secret = dprep.register_secret(value="[SECRET-PASSWORD]", id="[SECRET-ID]")

ds = dprep.MSSQLDataSource(server_name="[SERVER-NAME],[PORT]",
               database_name="[DATABASE-NAME]",
               user_name="[DATABASE-USERNAME]",
               password=secret)

Answer 2

Here is the MS Doc on MSSQLDataSource. MSSQLDataSource instances have a property, credentials_type which defaults to SERVER. Try explicitly setting this to WINDOWS before you do your query. Also, the port should be specified together with the server name.

import azureml.dataprep as dprep

windows_domain = 'localhost'
windows_user = 'my_user'
windows_password = 'my_password'

secret = dprep.register_secret(value=windows_password, id="password")

ds = dprep.MSSQLDataSource(server_name="localhost",
                   database_name="myDb",
                   user_name=f'{windows_domain}\{windows_user}',
                   password=secret)

ds.credentials_type = dprep.DatabaseAuthType.WINDOWS

dataflow = dprep.read_sql(ds, "SELECT top 100 * FROM [dbo].[MYTABLE]")
dataflow.head(5)