search

lucahttp / MSTeamsCQD

https://www.powershellgallery.com/packages/MSTeamsCQD
3 stars 0 forks source link

MSTeamsCQD - How do I authenticate? #1

Open akaRookieDev opened 2 years ago

akaRookieDev commented 2 years ago

I have Global Administrator rights in my Office 365 tenant, and I also assigned Reports Reader and Teams Administrator just to make sure I have all of the needed permissions.

When I run the Connect-CQDOnline command, I get the error below. I know my tenant is "provisioned". If I try to log in manually via the web browser, and then run this command, I get the same error (see below). I need this to be headless so I can automate some data pulls. How do you currently authenticate with this module, and what is the intended way?


At C:\Program Files\WindowsPowerShell\Modules\MSTeamsCQD\1.2.4\MSTeamsCQD.psm1:155 char:25
+ ... ingStatus = Invoke-RestMethod -Uri ('{0}tenant/provision' -f $Reposit ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
CQD Not Provisioned for TenantId . Stopping...
At C:\Program Files\WindowsPowerShell\Modules\MSTeamsCQD\1.2.4\MSTeamsCQD.psm1:157 char:5
+     throw ('CQD Not Provisioned for TenantId {0}. Stopping...' -f $Pr ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (CQD Not Provisi...d . Stopping...:String) [], RuntimeException
    + FullyQualifiedErrorId : CQD Not Provisioned for TenantId . Stopping...**```
akaRookieDev commented 2 years ago

"1.1.0 now supports CQD large Query and and headless login."

Headless login, how?

lucahttp commented 2 years ago

Hi @akaRookieDev could you please run the following command [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

akaRookieDev commented 2 years ago

@lucahttp I did this on my laptop and it connected to the cqd right away when i ran the connect-cqdonline command, however my other computer still gives the above error.

akaRookieDev commented 2 years ago

Do i need to log in via the browser for the command to work? ideally i am trying to add this to an automated script so that i can pull data, but also not having to provide a login or interact with it.

akaRookieDev commented 2 years ago 

PS C:\Windows\system32> [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

PS C:\Windows\system32> Connect-CqdOnline
Invoke-RestMethod : {"Message":"Authorization has been denied for this request."}
At C:\Program Files\WindowsPowerShell\Modules\MSTeamsCQD\1.2.4\MSTeamsCQD.psm1:155 char:25
+ ... ingStatus = Invoke-RestMethod -Uri ('{0}tenant/provision' -f $Reposit ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
CQD Not Provisioned for TenantId . Stopping...
At C:\Program Files\WindowsPowerShell\Modules\MSTeamsCQD\1.2.4\MSTeamsCQD.psm1:157 char:5
+     throw ('CQD Not Provisioned for TenantId {0}. Stopping...' -f $Pr ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (CQD Not Provisi...d . Stopping...:String) [], RuntimeException
    + FullyQualifiedErrorId : CQD Not Provisioned for TenantId . Stopping...

PS C:\Windows\system32>  ```
lucahttp commented 2 years ago

Do i need to log in via the browser for the command to work? ideally i am trying to add this to an automated script so that i can pull data, but also not having to provide a login or interact with it.

Yes, you need to login in the browser, If you want to automate you will need to login before to other office365 tool like MicrosoftTeams powershell module or azuread

lucahttp commented 2 years ago 

PS C:\Windows\system32> [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

PS C:\Windows\system32> Connect-CqdOnline
Invoke-RestMethod : {"Message":"Authorization has been denied for this request."}
At C:\Program Files\WindowsPowerShell\Modules\MSTeamsCQD\1.2.4\MSTeamsCQD.psm1:155 char:25
+ ... ingStatus = Invoke-RestMethod -Uri ('{0}tenant/provision' -f $Reposit ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
CQD Not Provisioned for TenantId . Stopping...
At C:\Program Files\WindowsPowerShell\Modules\MSTeamsCQD\1.2.4\MSTeamsCQD.psm1:157 char:5
+     throw ('CQD Not Provisioned for TenantId {0}. Stopping...' -f $Pr ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (CQD Not Provisi...d . Stopping...:String) [], RuntimeException
    + FullyQualifiedErrorId : CQD Not Provisioned for TenantId . Stopping...

PS C:\Windows\system32>  ```

Are you trying to login without the browser login? How are you sending the credentials?

akaRookieDev commented 2 years ago

I am logging into teams via the Connect-MicrosoftTeams module but it's doesn't appear to authenticate to the connect-cqdonline command

akaRookieDev commented 2 years ago 

PS C:\Windows\system32> Connect-MicrosoftTeams

Account                           Environment Tenant                   TenantId                            
-------                              ----------- ------                         --------                            
username@myorg.com   AzureCloud                                TenantID {Redacted}

PS C:\Windows\system32> Connect-CqdOnline
Invoke-RestMethod : {"Message":"Authorization has been denied for this request."}
At C:\Program Files\WindowsPowerShell\Modules\MSTeamsCQD\1.2.4\MSTeamsCQD.psm1:155 char:25
+ ... ingStatus = Invoke-RestMethod -Uri ('{0}tenant/provision' -f $Reposit ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
CQD Not Provisioned for TenantId . Stopping...
At C:\Program Files\WindowsPowerShell\Modules\MSTeamsCQD\1.2.4\MSTeamsCQD.psm1:157 char:5
+     throw ('CQD Not Provisioned for TenantId {0}. Stopping...' -f $Pr ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (CQD Not Provisi...d . Stopping...:String) [], RuntimeException
    + FullyQualifiedErrorId : CQD Not Provisioned for TenantId . Stopping...

PS C:\Windows\system32> ```
akaRookieDev commented 2 years ago

@lucahttp Here is what I have found out today.

If I run Connect-MicrosoftTeams by itself and I enter my email and password, it logs me in and I am able to use commands like Get-CQDData.

If I use this code to authenticate with Teams:

[PSCredential]$PSCreds = New-Object System.Management.Automation.PSCredential ($GraphU, $GraphP)
Connect-MicrosoftTeams -Credential $PSCreds

The Get-CQDData command gives me the errors that were sent above. I always feed credentials to Teams this way to automate certain processes, will this not work with this module to authenticate? Because it still authenticates fine with Teams.

In my variables file, this is how the credential variables are formatted:

$GraphU = "user@mydomain.com"
$GraphP = ConvertTo-SecureString "Password" -AsPlainText -Force
lucahttp commented 2 years ago

https://github.com/lucahttp/MSTeamsCQD/blob/main/MSTeamsCQD.psm1#L105-L108

the authentication of the module is here

the only thing that I change was this:

from prompt=login to prompt=none https://www.powershellgallery.com/packages/CQDPowerShell/2.0.1/Content/CQDPowerShell.psm1#:~:text=%2B%0A%C2%A0%C2%A0%22%26-,prompt%3Dlogin,-%22%C2%A0%2B

akaRookieDev commented 2 years ago

@lucahttp so when authenticating with the Connect-MicrosoftTeams command, Connect-CqdOnline works if I type my creds manually vs supplying them via -credential $PSCreds. Is there a way to make this work?

lucahttp commented 2 years ago

same issue https://docs.microsoft.com/en-us/answers/questions/491498/invoke-webrequest-to-get-access-token.html i will continue looking for a solution

akaRookieDev commented 2 years ago

@lucahttp I appreciate your continued support. I haven't been able to determine why the Connect-CqdOnline does not like the auth response it gets when using Connect-MicrosoftTeams -Credential $PSCreds.

One theory is its generating an auth token that is less secure, but I can't prove that. I feel like this should just work, but I can't think of anything else to try.

akaRookieDev commented 2 years ago

Does any of this help? https://adamtheautomator.com/invoke-restmethod/

lucahttp commented 2 years ago

https://github.com/lucahttp/MSTeamsCQD/blob/ba4d7867d0bc8eb70959bbdfe02419d1cbe55d32/MSTeamsCQD.psm1#L111-L125 in this lines seams that is opening the Interactive windows

lucahttp commented 2 years ago

https://github.com/microsoft/PowerBIVSTSExtension/blob/11e94323a9f2427aaed3771ab070cdcb49cf6ec2/VSTS%20Extension/Master-Powershell/Library.psm1#L56-L106

could be that here is the solution, let me check I will continue searching on this repos https://github.com/search?q=https%3A%2F%2Flogin.microsoftonline.com%2Fcommon%2Foauth2%2Fauthorize+language%3APowerShell&type=Code&ref=advsearch&l=&l=PowerShell

akaRookieDev commented 2 years ago

@lucahttp Both of the links above look promising from what I can see, do you think that will work for your module?

lucahttp commented 2 years ago

For my point of view yes, but could be that we are not seeing something, could be something like the thing that you mentioned before, different tokens with different security, Also is asking for the client I'd, I think there is one generic for powershell

akaRookieDev commented 2 years ago

@lucahttp I have Client ID set to a variable, so that shouldn't be a problem. I don't see why we couldn't get it to automate correctly, I just don't know what I am missing.

lucahttp commented 2 years ago 
# https://docs.microsoft.com/en-us/powershell/scripting/learn/deep-dives/add-credentials-to-powershell-functions?view=powershell-7.2
# https://duffney.io/addcredentialstopowershellfunctions/#:~:text=%40splat%0A%7D-,Working%20with%20%5Bstring%5D%20Passwords,-A%20good%20example

Function Get-AADToken{

    Param(

        [parameter(Mandatory=$true)][string]$Username,

        [parameter(Mandatory=$true)][string]$Password,

        [parameter(Mandatory=$true)][guid]$ClientId,

        [parameter(Mandatory=$true)][string]$Resource

    )
    Write-Host "Load Get-AADToken"

    $authorityUrl = "https://login.microsoftonline.com/common/oauth2/authorize"

    ## load active directory client dll

    $typePath = $PSScriptRoot + "\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"

    Add-Type -Path $typePath 

    Write-Verbose "Loaded the Microsoft.IdentityModel.Clients.ActiveDirectory.dll"

    Write-Verbose "Using authority: $authorityUrl"

    $authContext = New-Object -TypeName Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext -ArgumentList ($authorityUrl)

    $credential = New-Object -TypeName Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential -ArgumentList ($userName, $passWord)

    Write-Verbose "Trying to aquire token for resource: $resource"

    $authResult = $authContext.AcquireToken($resource,$clientId, $credential)

    Write-Verbose "Authentication Result retrieved for: $($authResult.UserInfo.GivenName)"

    return $authResult.AccessToken

}
Export-ModuleMember -Function Get-AADToken

seems that is not possible by design https://github.com/Azure/azure-powershell/issues/5785 https://github.com/Azure/azure-powershell/issues/5785#issuecomment-379599422

but is not working at the moment, im getting this error

https://cqd.teams.microsoft.com/repository/clientconfiguration

Get-AADToken -Username _useremail_ -Password _password_ -ClientId _clientid_from_clientconfiguration_ -Resource "https://analysis.windows.net/powerbi/api"
Load Get-AADToken
MethodInvocationException: Exception calling "AcquireToken" with "3" argument(s): "Could not load type 'System.Security.Cryptography.SHA256Cng' from assembly 'System.Core, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089'."

imagen

akaRookieDev commented 2 years ago

@lucahttp I am getting the same error. Have not had much luck on that repo, but I may be overlooking things.

lucahttp commented 2 years ago

@akaRookieDev we could have a better results using App registrations with client secret and client id

akaRookieDev commented 2 years ago

@lucahttp I tried that before but couldn't make it work. Is that something we could test possibly, or no?

lucahttp commented 2 years ago

I think yes, I need to check the token request process

akaRookieDev commented 2 years ago

Do you have a discord for support or anything?

I feel like the token process is the key, but to be 100% honest thats where I am still learning haha

lucahttp commented 2 years ago

Get-CQDToken https://login.microsoftonline.com/common/oauth2/authorize? response_type=token &redirect_uri= "https://cqd.teams.microsoft.com/spd/" &client_id= "c61d67cf-295a-xxxxxxxxxxxxxxxxxxx" &prompt=none &nonce= [guid]::NewGuid().GUID &resource= "https://cqd.teams.microsoft.com"

inputs
    resource / WebResource from https://cqd.teams.microsoft.com/repository/clientconfiguration -> AuthLoginResource
    client_id from $UriVar = "https://cqd.teams.microsoft.com/repository/clientconfiguration" -> AuthWebAppClientId
    redirectUrl from $CQDUri = "https://cqd.teams.microsoft.com/spd/"

output to Authorization Token

Connect-CqdOnline "https://cqd.teams.microsoft.com/repository/" tenant/dataservice inputs Authorization from Get-CQDToken endpoint from https://cqd.teams.microsoft.com/repository/clientconfiguration -> RepositoryApiBaseUrl

output to $DataServiceBaseUrl

Get-CQDData "$DataServiceBaseUrl" RunQuery output the call records

GetParams "$DataServiceBaseUrl" CubeStructure output to Get-CQDDimensions and Get-CQDMeasures

lucahttp commented 2 years ago

after check seems that they are using some strange configuration for the API, from my point of view the user is accesing an custom global CQD App Registration and the Teams Admin accounts are assigned to this

lucahttp commented 2 years ago

some time ago I saw a blog that shows you how to bypass the auth gui when you login inside powershell using some script, I thing that this could be the way to automate this process (also disabling the MFA)

akaRookieDev commented 2 years ago

This is how I bypass the teams auth gui in PowerShell

[PSCredential]$PSCreds = New-Object System.Management.Automation.PSCredential ($GraphU, $GraphP) Connect-MicrosoftTeams -Credential $PSCreds

akaRookieDev commented 2 years ago

If I do this though, Connect-CqdOnline doesn't authenticate.

akaRookieDev commented 2 years ago

maybe this is similar to the blog you were looking at?

https://www.sqlshack.com/different-ways-to-login-to-azure-automation-using-powershell/

akaRookieDev commented 2 years ago

Just wanted to follow-up to see if anything new might have come to light?

lucahttp commented 2 years ago

I was reading this but there is no hope https://techcommunity.microsoft.com/t5/teams-developer/how-to-make-source-code-from-cqdpowershell-cmdlet-acquiring/m-p/1325146

akaRookieDev commented 2 years ago

ah ok

akaRookieDev commented 2 years ago

According to my MS contact at work, what we are trying to do is possible, but they didn't say how. I am going to keep looking at it I guess.

akaRookieDev commented 2 years ago

@lucahttp I don't know if this is relevant but maybe we could try something like this?

https://stackoverflow.com/questions/62056920/rest-api-basic-authentication-and-invoke-webrequest-via-powershell

akaRookieDev commented 1 year ago

@lucahttp Just following up to see if you've heard or seen anything regarding this issue?

akaRookieDev commented 1 year ago

Hi there, has there been any possible remedies to this?

lucahttp commented 1 year ago

Hi @akaRookieDev I saw that there is a service called Graph Data Connect https://learn.microsoft.com/en-us/graph/data-connect-concept-overview https://learn.microsoft.com/en-us/graph/data-connect-datasets#:~:text=BasicDataSet_v0.TeamsCallRecords_v1

could an expensive workaround but seems to be the "best" way

RupenAnjaria commented 6 months ago

I am also looking some ways to get CQD either via PowerShell, PowerBI REST API or by Graph API, however lack of documentation and references are blocking. Have you made any progress so far? Please let me know. Thank you.

DailenG commented 5 months ago

I was able to work around this by providing the URL to generate the token to paste into a browser, then letting the user provide the resulting URL.

Here's my updated Get-CDQToken function. Not a perfect solution so I didn't submit it as a pull request.



####Function to Get the JWT Token VIA OAuth
function Get-CQDToken ([string]$client_id) {

  if ($CQDVer -eq "V2") {
    $CQDUri = "https://cqd.lync.com/spd/"
    $V2Token = $true
  }
  else {
    $CQDUri = "https://cqd.teams.microsoft.com/spd/"
    $V3Token = $true
  }

  Add-Type -AssemblyName System.Web
  $resourceUrl = $WebResource
  $redirectUrl = $CQDUri
  $nonce = [guid]::NewGuid().GUID
  $url = "https://login.microsoftonline.com/common/oauth2/authorize?response_type=token&redirect_uri=" +
  [System.Web.HttpUtility]::UrlEncode($redirectUrl) +
  "&client_id=$client_id" +
  "&prompt=none" + "&nonce=$nonce" + "&resource=" + [System.Web.HttpUtility]::UrlEncode($WebResource)

  Write-Host "Navigate to the url below to authenticate, then copy the URL you are redirected to." -ForegroundColor Cyan
  Write-Host "`n$url`n" -ForegroundColor Blue
  $web = Read-Host -Prompt "Paste URL here and press Enter"

  <#
  Add-Type -AssemblyName System.Windows.Forms

  $form = New-Object -TypeName System.Windows.Forms.Form -Property @{ Width = 440; Height = 640 }
  $web = New-Object -TypeName System.Windows.Forms.WebBrowser -Property @{ Width = 420; Height = 600; Url = ($url) }
  $DocComp = {
    $Global:uri = $web.Url.AbsoluteUri
    Write-Host $Global:uri
    if ($Global:Uri -match "error=[^&]*|access_token=[^&]*") { $form.Close() }
  }

  $web.ScriptErrorsSuppressed = $true
  $web.Add_DocumentCompleted($DocComp)
  $form.Controls.Add($web)
  $form.Add_Shown({ $form.Activate() })
  $form.ShowDialog() | Out-Null
  #>

  $Script:TokenLifeTime = [Web.HttpUtility]::ParseQueryString(($web -replace '^.*?(expires_in.+)$', '$1'))['expires_in']
  # Write-Host $TokenLifeTime
  $Script:Token = [Web.HttpUtility]::ParseQueryString(($web -replace '^.*?(access_token.+)$', '$1'))['access_token']
  # Write-Host $Token

  return ('Bearer {0}' -f $Script:Token)

}