locked
Querying Azure Active Directory Data from Azure SQL Server RRS feed

  • Question

  • User-893002196 posted

    Hi All,

    This is local database querying LDAP Active Directory userprincipalname method:
    https://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/

    SELECT * FROM OpenQuery

    (

    ADSI,

    'SELECT userPrincipalName

    FROM ''LDAP://domain.com/OU=Users,OU=xxxx,OU=.AP,DC=domain,DC=com''

    WHERE objectClass = ''User''

    ') AS tblADSI

    ORDER BY userPrincipalName

    Anyone here can guide me how to query Azure active directory userprinciplaname via Azure SQL without VM?

    Please advise.

    Thanks.

    Regards,

    Micheale

    Thursday, June 18, 2020 2:44 AM

All replies

  • User-893002196 posted

    Hi,

    I am changing the method doing in C# controller:

    String id = "xxxxxxxxxx-xxxx-xxxx-b9e3-xxxxxxxxxxxxxxx";

    String secret = "xxxxxxxxxxxxxxxxxxxxxxxxxxxx";

    var client = new RestClient("https://login.microsoftonline.com/xxxxxxxxx-xxxx-xxxx-ab62-xxxxxxxxxxxx/oauth2/token");

    var request = new RestRequest(Method.POST);

    request.AddHeader("cache-control", "no-cache");

    request.AddHeader("content-type", "application/x-www-form-urlencoded");

    request.AddParameter("application/x-www-form-urlencoded", "grant_type=client_credentials&scope=all&client_id=" + id + "&client_secret=" + secret, ParameterType.RequestBody);

    IRestResponse response = client.Execute(request);

    dynamic resp = JObject.Parse(response.Content);

    String token = resp.access_token.Value;

    client = new RestClient("https://graph.microsoft.com/v1.0/users?$filter=givenName cn 'andy'");

    request = new RestRequest(Method.GET);

    request.AddHeader("authorization", "Bearer " + token);

    request.AddHeader("cache-control", "no-cache");

    response = client.Execute(request);

    But on the  2nd call of RestClient, I got error "{\r\n  \"error\": {\r\n    \"code\": \"InvalidAuthenticationToken\",\r\n    \"message\": \"Access token validation failure. Invalid audience.\",\r\n    \"innerError\": {\r\n      \"date\": \"2020-06-18T14:03:30\",\r\n      \"request-id\": \"a22664bf-89b9-4bd2-9c78-1e288b20fc23\"\r\n    }\r\n  }\r\n}"

    If I run using postman after getting the bearer code & call https://graph.microsoft.com/v1.0/users?$filter=givenName cn 'andy'; I'm getting error as below:-

    {
      "error": {
        "code": "InvalidAuthenticationToken",
        "message": "Access token validation failure. Invalid audience.",
        "innerError": {
          "date": "2020-06-18T14:03:19",
          "request-id": "ca98fbed-3954-45ad-98e1-16cbffe32920"
        }
      }
    }

    Please advise.

    Thanks.

    Regards,

    Micheale

    Thursday, June 18, 2020 2:05 PM