为什么使用Windows身份验证使用AzureML dataprep登录MS SQL失败? [英] Why does my login to MS SQL with AzureML dataprep using Windows authentication fail?

问题描述

我尝试使用 MSSqlDataSource ，使用格式为

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)

设置 [DATABASE-USERNAME] 等于 MYWINDOWSDOMAIN \\ MYWINDOWSUSERNAME 和密码 [SECRET-PASSWORD] 与我的Windows密码一致(即尝试使用Windows身份验证.)

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

用

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

我知道

ExecutionError:登录失败.

ExecutionError: Login failed.

我可以在没有Windows身份验证的情况下连接到其他数据库.我在做什么错了?

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

推荐答案

考虑使用

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)

请注意，不赞成使用 dataprep ，可以替代使用 sqlalchemy

note that 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())

这篇关于为什么使用Windows身份验证使用AzureML dataprep登录MS SQL失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！