Я также столкнулся с этой ошибкой при попытке использовать PowerQuery для получения данных API в электронной таблице Excel, и мне было трудно обойтись без нее, в основном, потому что документация по всему этому несколько тонкая.

Проблема - как импортировать данные из API в Azure в электронную таблицу Excel?

У меня был ASP.NET API, работающий в Azure и открытый в моем собственном домене по URL-адресу, например. https://api.myapp.net (вместо встроенного лазурного URL-адреса). Этот API подключался к Azure AD через промежуточное ПО OWIN: UseWindowsAzureActiveDirectoryBearerAuthentication, то есть в службе приложений в Azure была отключена проверка подлинности AAD. Приложение было зарегистрировано в AAD как мультитенантное, в другом клиенте, чем то, где был размещен ресурс службы приложения.

Проблема 1 - Учетные данные

Итак, сначала в Excel выполните Данные> Из Интернета> https://api.myapp.net/Products и выберите «Организационная учетная запись» и нажмите «Войти», что даст следующую ошибку:

Для этого есть два разных исправления:

1. Вернуть заголовок ответа WWW-Authenticate на всех 401

Если вы включили AAD в своем API в коде с помощью промежуточного программного обеспечения owin, вам необходимо убедиться, что служба возвращает правильный заголовок WWW-Authenticate в ответе 401 клиенту, в частности, мы должны указать конечную точку входа в AAD. в качестве URI авторизации, например:

WWW-Authenticate: Bearer realm="", 
authorization_uri="https://login.microsoftonline.com/<<tenant id of your users>>"

См.: этот вопрос TechNet, предлагающий это решение

2. Включите аутентификацию AAD для службы приложений API на портале Azure.

• Или на портале Azure для клиента, где размещены сами службы приложений.
• Перейдите в службы приложений и найдите службу приложений API.
• В режиме аутентификации / авторизации включите аутентификацию службы приложений.
• Для «Действия при отсутствии аутентификации» выберите «Войти с помощью Azure Active Directory».
• В разделе «Поставщики аутентификации» щелкните «Azure Active Directory» и выберите «Дополнительные» настройки.
• В разделе «Идентификатор клиента» введите идентификатор приложения для регистрации приложения API.
• В разделе "URL-адрес эмитента" введите конечную точку входа для клиента, из которого происходят пользователи API.
• В разделе «Разрешенные аудитории токенов» убедитесь, что вы добавили фактический URL-адрес вашего API, например https://api.myapp.net
• Сохраните изменения

По сути, эта конфигурация описана здесь

Проблема 2 - Регистрация приложения

Теперь вернитесь в Excel, когда вы нажмете «Войти» в запросе, откроется всплывающее окно, которое перенесет вас на страницу входа Microsoft для настроенного вами клиента. Когда вы вводите учетные данные и входите в систему, вы можете получить эту ошибку (та, что указана в вопросе):

Чтобы решить эту проблему, вам необходимо убедиться, что приложение правильно зарегистрировано в AAD.

Вот как ...

• На портале Azure для клиента, на котором зарегистрированы ваши приложения.
• Перейдите в Azure Active Directory> Регистрация приложений и найдите регистрацию для службы API.
• Отредактируйте манифест и убедитесь, что фактический URL-адрес развернутого API настроен в списке идентификаторов Uris, например https://api.myapp.net (будет уже настроен встроенный URL-адрес Azure)

{
  "identifierUris": [
    "https://api.myapp.net",
    "https://<mytenant>.onmicrosoft.com/<myappregname>"
  ]
}

• Если приложение является мультитенантным, вам необходимо убедиться, что в этом URL-адресе используется домен подтверждено в Azure
• Вы также должны убедиться, что для приложения доступна user_impersonation область:

{
  "oauth2Permissions": [
    {
      "adminConsentDescription": "Allow the application to access myapp on behalf of the signed-in user.",
      "adminConsentDisplayName": "Access myapp",
      "id": "xxxxx-xxx-xxx-xxx-xxxxxxx",
      "isEnabled": true,
      "lang": null,
      "origin": "Application",
      "type": "User",
      "userConsentDescription": "Allow the application to access my on your behalf.",
      "userConsentDisplayName": "Access my app",
      "value": "user_impersonation"
    }
  ]
}

• Сохранить изменения.

Проблема 3 - Разрешенная аудитория токенов

Теперь, вернувшись в Excel, вы сможете обойтись без входа, но при нажатии на «Подключиться» вы можете получить эту ошибку:

Теперь, заглянув в скрипач, вы увидите, что вход в систему AAD работает и возвращает токен, но когда он отправляется в API, вы получаете 401.

Это проблема, только если вы включили AAD через код, а не через портал Azure (см. Проблему 1 выше!). Чтобы исправить это, вам необходимо убедиться, что TokenValidationParameters класс, переданный промежуточному программному обеспечению owin, ValidAudience установлен на фактический URL-адрес вашего развернутого API.

Запустить запрос

После всех этих настроек все должно работать, вернувшись в Excel ...

• Нажмите «Войти», откроется всплывающее окно, на котором вы попадете на страницу входа в Microsoft для настроенного вами клиента, войдите в систему с вашими учетными данными.
• Нажмите Подключиться.
• Затем откроется редактор PowerQuery и отобразит данные, полученные из API.
• Щелкните Главная> Расширенный редактор, вы сможете просмотреть необработанный запрос - он находится в синтаксис M-запроса язык запросов, используемый PowerQuery, в моем случае данные представляли собой плоский массив, поэтому этого было достаточно:

let
    Source = Json.Document(Web.Contents("https://api.myapp.net/Products")),
    #"Converted to Table" = Table.FromRecords(Source)
in
    #"Converted to Table"

• Нажмите «Закрыть и загрузить», чтобы вернуть данные на лист Excel.

Как это работает

Если вам не все равно (и вы все еще читаете это!), Похоже, это работает следующим образом:

1. PowerQuery запрашивает доступ к вашему API во встроенном приложении Microsoft PowerQuery For Excel (идентификатор клиента a672d62c-fc7b-4e81-a576-e60dc46e951d)
2. Когда вы входите в систему, AAD предоставляет динамическую область user_impersonation для вашего API (определяется URL-адресом ресурса https://api.myapp.net) в приложение "Microsoft PowerQuery For Excel"
3. Вы можете увидеть это на портале, перейдя в Корпоративные приложения, проверив Приложения Microsoft и выполнив поиск Microsoft PowerQuery For Excel.

Извините за длинный пост, но, надеюсь, это поможет кому-то сделать что-то, казалось бы, довольно тривиальное - вытащить данные из API в Azure в Excel!

Мне удалось решить эту проблему, опубликовав свой WebAPI в веб-приложении Azure. Интересно, что при размещении в Azure он предлагал разрешить доступ «Power Query for Excel». Проблема могла быть ограничена тем фактом, что я запускал ее в IIS Express на моем компьютере Dev.