2

I am trying to connect the Azure SQL Database from Azure Machine Learning Service with MSI Authentication (Without a username and password).

I am trying to Machine learning model on azure Machine learning service that purpose I need data that' why I want to connect Azure SQL Database from Azure Machine Learning Service using MSI Authentication.

But I got below error:-

 "error": {"message": "Activity Failed:\n{\n    \"error\": {\n        \"code\": \"UserError\",\n        \"message\": \"User program failed with KeyError: 'MSI_ENDPOINT'\",\n

Please check the below code that I have used for the database connection.

import logging
import struct
import pyodbc
import os
import requests


class AzureDbConnect:
    def __init__(self):
        print("Inside msi database")
        msi_endpoint = os.environ["MSI_ENDPOINT"]
        msi_secret = os.environ["MSI_SECRET"]

        resource_uri = 'https://database.windows.net/'

        logging.info(msi_endpoint)
        print(msi_endpoint)
        logging.info(msi_secret)
        print(msi_secret)
        print("Inside token")

        token_auth_uri = f"{msi_endpoint}?resource={resource_uri}&api-version=2017-09-01"
        head_msi = {'Secret': msi_secret}
        resp = requests.get(token_auth_uri, headers=head_msi)
        access_token = resp.json()['access_token']
        logging.info(access_token)
        print("Token is :- ")
        print(access_token)

        accesstoken = bytes(access_token, 'utf-8')
        exptoken = b""
        for i in accesstoken:
            exptoken += bytes({i})
            exptoken += bytes(1)
        tokenstruct = struct.pack("=i", len(exptoken)) + exptoken

        conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};"
                              "Server=tcp:<Server Name>"
                              "1433;Database=<Database Name>",
                              attrs_before={1256: bytearray(tokenstruct)})

        print(conn)

        self.sql_db = conn.cursor()

Is there any way to connect Azure, SQL Database from Azure Machine Learning Service With MSI Authentication?

Akshay Godase
  • 239
  • 7
  • 15
  • the document says it should work: https://learn.microsoft.com/en-us/azure/machine-learning/service/concept-enterprise-security#securing-compute-targets-and-data. but where did you get your sample code form ? – Thomas Nov 01 '19 at 06:19
  • @Thomas I tried it but I got the above error. – Akshay Godase Nov 01 '19 at 07:14
  • did you activate managed identity ??? also it says it is not activatedby defautl on sql server – Thomas Nov 01 '19 at 07:52
  • @Thomas Are you tried above code on azure ML Service? – Akshay Godase Nov 01 '19 at 09:22
  • @Thomas I am not able to see the Identity on Azure ML Service. – Akshay Godase Nov 02 '19 at 15:08
  • have you seen that: https://learn.microsoft.com/en-us/azure/machine-learning/service/concept-enterprise-security#securing-compute-targets-and-data – Thomas Nov 03 '19 at 07:19
  • @Thomas Yes but I am not able to understand how I can connect Azure SQL Database from Azure ML Service using MSI. Do you have any example or code? if you have then please share. – Akshay Godase Nov 03 '19 at 09:12

1 Answers1

0

Currently MSI Authentication is not supported to connect Azure SQL DB from Azure ML, It's on road map to add in future. You can Usually this is to do with setting up service principals in the DB, Attached is a step-by-step guide for getting this setup for Azure ML.

enter image description here

enter image description here

Ram
  • 2,459
  • 1
  • 7
  • 14