Connect-DbaInstance tenants and tokens research

[potatoqualitee]

These are my notes. So far, only the az thing works, but I'm exploring other ways that won't include depending on external modules.

Great getting started with the new SqlClient and PowerShell tho I haven't gotten it to work yet because I didn't try on core. https://gist.github.com/MartinHBA/86c6014175758a07b09fa7bb76ba8e27

A bunch of different connection options from Microsoft (but in C#)
https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver15

I also learned that if you are creating Conditional Access that is not a Managed Identity 😅 Conditional Access is using Authenticator while Managed Identities create client ids.

And shueybubble's comments for reference: #7271 (comment)

$tenantId = "abc" 
$clientId = "def"
$clientSecret = "hijk"

$connstring = "Server=dbatoolstest.database.windows.net; Authentication=Active Directory Service Principal; Database=test; User Id=$clientId; Password=$clientSecret";
Add-Type -ReferencedAssemblies Microsoft.Data.SqlClient.SNI -Path "C:\Users\ctrlb\Downloads\microsoft.data.sqlclient.3.0.0\lib\net461\Microsoft.Data.SqlClient.dll"

$conn = New-Object Microsoft.Data.SqlClient.SqlConnection
$conn.ConnectionString = $connstring

I'm also exploring creating Tokens without the Az module, by using this: https://blog.kloud.com.au/2019/10/31/microsoft-graph-using-msal-with-powershell/ MSAL is a replacement for their previous ADAL. The MSAL.PS module is lightweight with just a couple DLLs and some PowerShell I can wrap in New-DbaAzAccessToken (it's MIT licensed and we have permission to use the DLLs)

$tenantId = "abc" 
$clientId = "def"
$clientSecret = ConvertTo-SecureString "hijk" -AsPlainText -Force 

$response = Get-MsalToken -ClientId $clientId -ClientSecret $clientSecret  -Authority $authority -TenantId $tenantId -Scopes "https://database.windows.net/.default" 

Set-DbatoolsConfig -FullName sql.connection.experimental -Value $true
$server = Connect-DbaInstance -SqlInstance dbatoolstest.database.windows.net -Database test -AccessToken $response.AccessToken -DisableException

[potatoqualitee]

MFA will use this somehow