MSTeamsCQD - How do I authenticate?
Opened this issue · 42 comments
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...**```
"1.1.0 now supports CQD large Query and and headless login."
Headless login, how?
Hi @akaRookieDev could you please run the following command
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
@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.
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.
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> ```
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
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?
I am logging into teams via the Connect-MicrosoftTeams module but it's doesn't appear to authenticate to the connect-cqdonline command
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> ```
@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
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
@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?
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
@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.
Does any of this help?
https://adamtheautomator.com/invoke-restmethod/
Lines 111 to 125 in ba4d786
in this lines seams that is opening the Interactive windows
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
@lucahttp Both of the links above look promising from what I can see, do you think that will work for your module?
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
@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.
# 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-AADTokenseems that is not possible by design
Azure/azure-powershell#5785
Azure/azure-powershell#5785 (comment)
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'."
@lucahttp I am getting the same error. Have not had much luck on that repo, but I may be overlooking things.
@akaRookieDev we could have a better results using App registrations with client secret and client id
@lucahttp I tried that before but couldn't make it work. Is that something we could test possibly, or no?
I think yes, I need to check the token request process
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
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
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
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)
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
If I do this though, Connect-CqdOnline doesn't authenticate.
maybe this is similar to the blog you were looking at?
https://www.sqlshack.com/different-ways-to-login-to-azure-automation-using-powershell/
Just wanted to follow-up to see if anything new might have come to light?
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
ah ok
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.
@lucahttp I don't know if this is relevant but maybe we could try something like this?
@lucahttp Just following up to see if you've heard or seen anything regarding this issue?
Hi there, has there been any possible remedies to this?
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
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.
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)
}