11

I tried connecting to a MS SQL database using azureml.dataprep in an Azure Notebook, as outlined in https://learn.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?

Md Farid Uddin Kiron
  • 16,817
  • 3
  • 17
  • 43
Davide Fiocco
  • 5,350
  • 5
  • 35
  • 72

2 Answers2

4

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)

Note that the usage of dataprep is deprecated, sqlalchemy can be used an alternative

import pandas as pd
from sqlalchemy import create_engine

def mssql_engine(user = "[DATABASE-USERNAME]", 
                 password = "[SECRET-PASSWORD]", 
                 host = "[SERVER-NAME],[PORT]",
                 db = "[DATABASE-NAME]"):
    engine = create_engine(f'mssql+pyodbc://{user}:{password}@{host}/{db}?driver=SQL+Server')
    return engine

query = "SELECT ..."

df = pd.read_sql(query, mssql_engine())
Davide Fiocco
  • 5,350
  • 5
  • 35
  • 72
3

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)
Neil
  • 1,613
  • 1
  • 16
  • 18
  • Aw, thanks but that still fails with `ExecutionError: Could not connect to specified database.` In the end I gave up (see my own answer on how I "solved" this) – Davide Fiocco Feb 14 '19 at 15:12
  • 1
    @DavideFiocco I slightly modified my answer above. I can successfully use that to windows authenticate / query a sql express database on my local PC. I don't have a full domain to work on but the local windows auth works as expected. – Neil Feb 15 '19 at 04:30
  • Actually after some further testing, it seems the windows creds specified are irrelevant to the server authentication. The query fails if no windows creds are provided, but providing invalid creds makes no difference - the authentication uses the windows user that is running your python process. I'm not sure how this would work from linux - it seems the support for windows authentication is quite limited. – Neil Feb 15 '19 at 04:34