[Bug]Connect-DbaInstance can't connect to Azure SQL or SQL MI

[cmuncan]

Report

We are working on the migration to that fancy fluffy stuff up in the sky. As of now, we have a mixed bag of SQL: SQL Server (on prem & VM), Azure SQL, & SQL MI. Now that we have Azure SQL & MI, I need to be able to connect holistically, regardless of what my SQL type is! I figured, based on the documentation in Connect-DbaInstance that I could do it, but unfortunately I was getting a bunch of errors! What are these errors you speak of? Well I am so happy you read this far and are interested for more!!!! Below I have the various errors and scripts that I used. I worked with @wsuhoey & @andreasjordan in the dbatools channel in Slack for this too.

I did figure out a workaround for this (answered below too!)

Host used

• [x] powershell.exe
• [x] ISE
• [x] VS Code
• [ ] Other (please specify)

Errors Received

Method 1

Connect-DbaInstance -SqlInstance $instance -Verbose -Database 'master' -Tenant $ConnectedAz -SqlCredential $cred -AuthenticationType 'AD Universal with MFA Support'

Yielded Response status code does not indicate success: 400 (Bad Request).

Full error message with -DEBUG

$connectedInstance = Connect-DbaInstance -SqlInstance $instance -Verbose -Database 'master' -Tenant $ConnectedAz -SqlCredential $cred -AuthenticationType 'AD Universal with MFA Support' -Debug
DEBUG: 2275 | [11:46:42][Connect-DbaInstance] Starting process block
DEBUG: 2277 | [11:46:42][Connect-DbaInstance] Starting loop for 'aaaaa.bbbbb.database.windows.net': ComputerName = 'aaaaa.bbbbb.database.windows.net', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Default'
DEBUG: 2646 | [11:46:42][Connect-DbaInstance] We are about to connect to Azure
DEBUG: 2682 | [11:46:42][Connect-DbaInstance] We have a Tenant and build the connect string
VERBOSE: [11:46:42][Connect-DbaInstance] Creating renewable token
DEBUG: 2717 | [11:46:42][Connect-DbaInstance] Creating renewable token
VERBOSE: The source code was already compiled and loaded.
VERBOSE: [11:46:42][Connect-DbaInstance] Connecting to Data Source=TCP:aaaaa.bbbbb.database.windows.net,1433;Initial Catalog=master;Persist Security Info=True;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;Application Name="dbatools PowerShell module - dbatools.io"
DEBUG: 2725 | [11:46:42][Connect-DbaInstance] Connecting to Data Source=TCP:aaaaa.bbbbb.database.windows.net,1433;Initial Catalog=master;Persist Security Info=True;MultipleActiveResultSets=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False;Application Name="dbatools PowerShell module - dbatools.io"
VERBOSE: [11:46:42][Connect-DbaInstance] Connecting to Azure: aaaaa.bbbbb.database.windows.net
DEBUG: 2737 | [11:46:42][Connect-DbaInstance] Connecting to Azure: aaaaa.bbbbb.database.windows.net
DEBUG: [11:46:43][Connect-DbaInstance] Failure | Response status code does not indicate success: 400 (Bad Request).

Method 2 Using the new method of Connect-DbaInstance I get this (example 17 from the documentation page)

Set-DbatoolsConfig -FullName sql.connection.experimental -Value $true
Connect-DbaInstance -SqlInstance $instance -SqlCredential $Cred

Yielded Cannot open server "MFCGD.COM" requested by the login. The login failed.

Full error message with -DEBUG

Set-DbatoolsConfig -FullName sql.connection.experimental -Value $true
Connect-DbaInstance -SqlInstance $instance -SqlCredential $Cred -Debug
DEBUG: 2275 | [12:02:50][Connect-DbaInstance] Starting process block
DEBUG: 2277 | [12:02:50][Connect-DbaInstance] Starting loop for 'aaaaa.bbbbb.database.windows.net': ComputerName = 'aaaaa.bbbbb.database.windows.net', InstanceName = 'MSSQLSERVER', IsLocalHost = 'False', Type = 'Default'
DEBUG: 2282 | [12:02:50][Connect-DbaInstance] sql.connection.experimental is used
DEBUG: 2305 | [12:02:50][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object
DEBUG: 2187 | [12:02:50][Test-Azure] Test for Azure is positive
DEBUG: 2367 | [12:02:50][Connect-DbaInstance] authentication method is 'azure ad'
DEBUG: 2399 | [12:02:50][Connect-DbaInstance] ApplicationName will be set to 'dbatools PowerShell module - dbatools.io'
DEBUG: 2421 | [12:02:50][Connect-DbaInstance] Authentication will be set to 'ActiveDirectoryPassword'
DEBUG: 2436 | [12:02:50][Connect-DbaInstance] ConnectionTimeout will be set to '15'
DEBUG: 2466 | [12:02:50][Connect-DbaInstance] PacketSize will be set to '4096'
DEBUG: 2485 | [12:02:50][Connect-DbaInstance] Pooled will be set to 'True'
DEBUG: 2494 | [12:02:50][Connect-DbaInstance] SecurePassword will be set
DEBUG: 2521 | [12:02:50][Connect-DbaInstance] UserName will be set to 'USER1@MFCGD.COM'
DEBUG: 2543 | [12:02:51][Connect-DbaInstance] TrueLogin is ''
DEBUG: 2566 | [12:02:51][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data Source=aaaaa.bbbbb.database.windows.net;User ID=USER1@MFCGD.COM;Password=********;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.ioUSER1@MFCGD.COM;Password=********;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io"
DEBUG: 2570 | [12:02:51][Connect-DbaInstance] We connect to the instance with server.ConnectionContext.SqlConnectionObject.Open()
DEBUG: [12:02:51][Connect-DbaInstance] Failure | Cannot open server "MFCGD.COM" requested by the login.  The login failed.
Confirm
Continue with this operation?
[Y] Yes [A] Yes to All [H] Halt Command [S] Suspend [?] Help (default is "Yes"): A
ConnectionError: C:\Users\CADBAMSQL04\Documents\PowerShell\Modules\dbatools\1.0.145\allcommands.ps1:88170:9
 Line |
88170 |          throw $records[0]
      |          ~~~~~~~~~~~~~~~~~
      | Cannot open server "MFCGD.COM" requested by the login.  The login failed.

Steps to Reproduce

Method 1

$Cred = Get-Credential
$ConnectedAz = Connect-AzAccount -Credential $Cred
$instance = "aaaaaaaaaaa.bbbbbbbbb.database.windows.net"
$connectedInstance = Connect-DbaInstance -SqlInstance $instance -Verbose -AuthenticationType 'AD Universal with MFA Support' -Database 'master' -SqlCredential $Cred -Tenant $ConnectedAz

Method 2 Using the new method of Connect-DbaInstance I get this (example 17 from the documentation page)

$Cred = Get-Credential
$ConnectedAz = Connect-AzAccount -Credential $Cred
$instance = "aaaaaaaaaaa.bbbbbbbbb.database.windows.net"

Set-DbatoolsConfig -FullName sql.connection.experimental -Value $true
Connect-DbaInstance -SqlInstance $instance -SqlCredential $Cred

Workaround

it would seem that dbatools doesn't yet support Azure SQL with Integrated Azure Active Directory ? well to get around this, I had to go outside of dbatools and use the classic SqlServer module's Invoke-SqlCmd

Found in Example 11: Connect to Azure SQL Database (or Managed Instance) using an Access Token

$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList ($credid, $credpass)
$ConnectedAz = Connect-AzAccount -Credential $Cred -WarningAction SilentlyContinue
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$instance = "aaaaa.bbbbb.database.windows.net"
Invoke-Sqlcmd -ServerInstance $instance -Database master -query "select [name] from [sys].[databases]" -AccessToken $token
name
----
master
tempdb

Expected Behavior

to connect

Actual Behavior

it didn't connect

Environmental information

🚨🚨🚨🚨🚨🚨🚨🚨🚨🚨 Please provide the output of the below script

PowerShell Version        : 7.1.3
dbatools latest installed : 1.0.145
Culture of OS             : en-US

SQL Server:

Microsoft SQL Azure (RTM) - 12.0.2000.8   Feb 20 2021 17:51:58   Copyright (C) 2019 Microsoft Corporation 

us_english

[andreasjordan]

I did some research on this, but I don't have a solution yet.

Based on the experimental code path we do the following:

$serverName = 'aaaaaaaaaaa.bbbbbbbbb.database.windows.net'
$connInfo = New-Object -TypeName Microsoft.SqlServer.Management.Common.SqlConnectionInfo -ArgumentList $serverName
$srvConn = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $connInfo
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $srvConn
$server.ConnectionContext.SqlConnectionObject.Open()

The object $connInfo has a property named "AccessToken" with type "Microsoft.SqlServer.Management.Common.IRenewableToken" and thus only takes a renewable token and not an access token generated with Get-AzAccessToken -ResourceUrl https://database.windows.net.

We have a command New-DbaAzAccessToken to generate Azure oauth2 tokens and this command is used in the old code path. But to generate a renewable token it needs a credential to get UserID and ClientSecret from.

The workaround uses an access token which is generated with Get-AzAccessToken and is valid for one hour only. The Command Invoke-Sqlcmd from the module SqlServer is accepting such an access token. Because of beeing closed source I don't know how this token is used inside of the command. @potatoqualitee : Can you use your contacts to Microsoft to get some insight in the internal usage of -AccessToken inside of Invoke-Sqlcmd?

[andreasjordan]

Maybe @shueybubbles can help?

[shueybubbles]

There are a variety of issues to sort out here. It looks like you are mixing up AAD Password auth and AAD Interactive (aka MFA). I don't know how Connect-DbaInstance works, but successful use of AAD auth for SQL depends on several factors.

The main factor is which version of SMO you have. If you are using a 161 version built on Microsoft.Data.SqlClient, you can pretty much rely on the client to do all the heavy lifting for you. M.D.S has its own fully managed stack to implement the various AAD auth types. All you have to do is make sure its dependencies like Microsoft.Identity.Client are installed with it and that you set the auth type you want in the connection string.

If you are using an older SMO based on System.Data.SqlClient, or if you prefer to manage the Azure authentication separately altogether, then you have a few options.

System.Data.SqlClient only handles Active Directory Password and Active Directory Integrated auth for SQL connections if you have also installed one of the newer SQL client drivers like msodbcsql 17 or have installed SSMS. That implementation is practically obsolete, though, as it doesn't support many conditional access policies like device id-based.

Fully interactive/MFA support for AAD can be achieved a few ways. Given that you are using Get-AzToken to login, you can simply set the SqlConnection.AccessToken before calling Open on the connection. You could also implement IRenewableToken and have your implementation simply return the token returned by Get-Aztoken. The former implementation is ok if you know the connection isn't going to stay open longer than an hour. The latter is similar to what SSMS 17 does.

With System.Data.SqlClient, though, to fully support silent token refresh and a more streamlined experience, you need to implement SqlAuthenticationProvider Again your implementation could just provide the same tokens returned by Get-AzToken. SSMS 18 provides its own implementation of AAD interactive this way. Implementing this provider will ensure that idle connections that are still in the Open state can be re-established.

There's at least one implemention of SqlAuthenticationProvider available in a nuget package that might be interesting for you, if you are still using System.Data.SqlClient. See https://azure.microsoft.com/en-us/blog/securing-azure-sql-databases-with-managed-identities-just-got-easier/

[andreasjordan]

Thanks for the quick response.

I think one problem might be that dbatools still uses an old version of the smo. The file properties show "15.0.18218.0" - is that the version?

Is there any documentation with the constructor of Microsoft.SqlServer.Management.Common.IRenewableToken?

[shueybubbles]

It's an interface so it doesn't define a constructor. The basic doc is at https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.common.irenewabletoken?view=sql-smo-160

If you provide an implementation to ServerConnection, it will call your GetAccessToken method before opening the SqlConnection.

[andreasjordan]

Thanks, now I think I understand. But that is too much .NET coding and beyond my skills. @potatoqualitee : Who in the team could help here?

[niphlod]

I think the best "connected" here in this regards are @potatoqualitee and @wsmelton

[wsmelton]

Support for MFA was pulled from the module year or so back because we had issues including the required DDL files for Azure. They had conflict with VS Code that caused users issues and it has not been revisited since then.

[wsmelton]

SMO itself does not handle direct auth to Azure AD with MFA. I'm not aware it can handle the Azure SQL auth options either, that is something SSMS deals with if I recall.

[potatoqualitee]

thanks, everyone. i have a few things backlogged that i have to work on, including integrating some much needed work done by @niphlod. I'll take a look at replacing SMO to see if it can work. @wsmelton is right, we removed mfa due to dll conflicts https://github.com/sqlcollaborative/dbatools/pull/5873

[wsmelton]

Only because I've helped a developer support this authentication type in our software at work but in order to fully support Integrated Azure Active Directory and the other authentication requires some .NET magic to be done. SMO itself does not include support for these authentication types explicitly because it is part of Azure AD and not a authentication mechanism owned by SQL Server. (Hence that DLL we tried to use was published by Microsoft.).

I would note that right now, last I had checked (it's been a while), the sqlserver module does not support these authentication types either.

[andreasjordan]

Have a look at the workaround the original poster is using:

$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList ($credid, $credpass)
$ConnectedAz = Connect-AzAccount -Credential $Cred -WarningAction SilentlyContinue
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$instance = "aaaaa.bbbbb.database.windows.net"
Invoke-Sqlcmd -ServerInstance $instance -Database master -query "select [name] from [sys].[databases]" -AccessToken $token

So Invoke-Sqlcmd is accepting an accesstoken generated by Get-AzAccessToken. I have tried that myself - it works. But Invoke-Sqlcmdis the only command from SqlServer that supports this. Sadly it is closed source - I would really like to see the source code...

[wsmelton]

That is where the use of IRenewableToken comes into play, if I recall. Want to say there is an issue or PR we used to work through some of this in the repo but I've forgotten a good portion of it and would have to go test it again.

[andreasjordan]

But the accesstoken in $token is not renewable, but only a string that is valid for one hour.

[shueybubbles]

You should be able to set $server.ConnectionContext.SqlConnectionObject.AccessToken to $token before the connection is opened. That's basically what invoke-sqlcmd does.

[andreasjordan]

I have tried that. but $token is a string, and $server.ConnectionContext.SqlConnectionObject.AccessToken needs a IRenewableToken.

[shueybubbles]

SqlConnectionObject is an instance of SqlConnection

[wsmelton]

Yeah, @shueybubbles I recall this is stuff @potatoqualitee and myself hit when we were trying to figure some of this out back in 2019 to support it all. We never could figure out the whole interface to that IRenewableToken because it also didn't make sense why that property requires an object that is for renewing a token itself.

That is what drove us to use the Azure DLLs, but then the issue with VS Code hit us.

[andreasjordan]

Ok, it is a string. I will try to find my scripts again...

[andreasjordan]

I found a script again.

I have tried this:

$serverName = "$ServerName.database.windows.net"
$connInfo = New-Object -TypeName Microsoft.SqlServer.Management.Common.SqlConnectionInfo -ArgumentList $serverName
$srvConn = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $connInfo
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $srvConn
$server.ConnectionContext.SqlConnectionObject.AccessToken = $token

And got this: Exception setting "AccessToken": "Cannot set the AccessToken property if the 'Integrated Security' connection string keyword has been set to 'true' or 'SSPI'."

[wsmelton]

This is the issue we captured most of our notes in #5445

[shueybubbles]

IRenewableToken only exists because we needed some way for SSMS 17 to provide tokens for SMO connections and because the the first implementation of AAD auth in System.Data.SqlClient didn't have any such callback mechanism of its own. In .Net 4.7.2 you can register your own token provider which is what SSMS 18 does. You could potentially implement SqlAuthenticationProvider with a tiny class that wraps the azure cmdlet.

SSMS 19 will be based on newer SMO and on Microsoft.Data.SqlClient. M.D.S comes with its own AAD implementation based on MSAL.Net.

[shueybubbles]

@andreasjordan you'll probably have to construct your ServerConnection using a SqlConnection.

        /// <summary>
        /// Constructs a new ServerConnection object from the given SqlConnection
        /// </summary>
        /// <param name="sqlConnection"></param>
        public ServerConnection(SqlConnection sqlConnection) : this(sqlConnection, null)

[andreasjordan]

Some things work...

$connString = "Data Source=$ServerName.database.windows.net;Initial Catalog=$DatabaseName;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False"
$sqlConn = [System.Data.SqlClient.SqlConnection]::new($connString)
$sqlConn.AccessToken = $token
$sqlConn.Open()

works.

$srvConn = [Microsoft.SqlServer.Management.Common.ServerConnection]::new($sqlConn)
$srvConn.TrueLogin
$srvConn.ExecuteWithResults("select @@version").Tables[0].Column1

returns the correct login who generated the token and "Microsoft SQL Azure (RTM) - 12.0.2000.8 ..." as the version.

$server = [Microsoft.SqlServer.Management.Smo.Server]::new($srvConn)
$server.Collation
$server.Edition
$server.Query("select @@version")

Displays "SQL_Latin1_General_CP1_CI_AS" and "SQL Azure", but failes the .Query() with "Exception calling "Query" with "1" argument(s): "You cannot call a method on a null-valued expression.""

[andreasjordan]

Is our [Microsoft.SqlServer.Management.Smo.Server] to old and incompatible?

[andreasjordan]

Ok, .Query() is only added by dbatools and not working here. Will try to implement this in dbatools and come back to you later. Thanks for the help.

[wsmelton]

The Query() method is added via PowerShell Types. https://github.com/sqlcollaborative/dbatools/blob/development/xml/dbatools.Types.ps1xml It is setup as an alias.

[andreasjordan]

Thanks. Now I know where the error is commming from: ($this.Databases[$Database].ExecuteWithResults($Query)).Tables[0] does not work because .Databases throws "...Failed to connect to server...". ($this.ConnectionContext.ExecuteWithResults($Query)).Tables[0] works.

[andreasjordan]

I think .Query() never worked on Azure SQL Database. Can someone test? If so, do we want to fix?

[niphlod]

prolly because azure needs a connection directly to the database and with onprem usually we connect to master and then change from there. If there's previous knowledge that this is in fact azure, an "if" sounds good. I don't think a lot of peoples use or know .Query(), but it's useful for internal function that needs elaborate batches to run as they run in SSMS .

[andreasjordan]

Is one of you able to test my new branch and give feedback?

[niphlod]

I don't have MI access but Azure SQL for sure: what should I test specifically ? will do ASAP

[andreasjordan]

Try to follow the new example. I also don't have a MI, just Azure SQL Database. @cmuncan do you have a managed instance?

[cmuncan]

I do MI. I can test it out on Monday. What exactly is the test though?

---

Chris Muncan

Canadian Engineering Services | Database Engineer

E @.**@.>

M 519 897 6292<tel:519%20897%206292>

Log A Jira Ticket To Request Assistance from Database Engineering Teamhttps://cpcnissgwp01.americas.manulife.net:23800/secure/CreateIssue.jspa?pid=31170&issuetype=3

---

From: Andreas Jordan @.> Sent: Friday, May 14, 2021 2:19:32 PM To: sqlcollaborative/dbatools @.> Cc: Chris Muncan @.>; Mention @.> Subject: [EXTERNAL] Re: [sqlcollaborative/dbatools] [Bug]Connect-DbaInstance can't connect to Azure SQL or SQL MI (#7271)

CAUTION This email is from an external sender, be cautious with links and attachments.

Try to follow the new example. I also don't have a MI, just Azure SQL Database. @cmuncanhttps://github.com/cmuncan do you have a managed instance?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/sqlcollaborative/dbatools/issues/7271#issuecomment-841419567, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AQFI7AFDIBIP3OPOA3K2X7TTNVSTJANCNFSM4345R3PA.

STATEMENT OF CONFIDENTIALITY The information contained in this email message and any attachments may be confidential and legally privileged and is intended for the use of the addressee(s) only. If you are not an intended recipient, please: (1) notify me immediately by replying to this message; (2) do not use, disseminate, distribute or reproduce any part of the message or any attachment; and (3) destroy all copies of this message and any attachments.

[andreasjordan]

If you can checkout this (https://github.com/sqlcollaborative/dbatools/tree/ConnectDbaInstance_Azure_Token) branch, you can try to follow the new example and use Connect-DbaInstance with AccessToken with the same token you use in your workaround.

[cmuncan]

@andreasjordan I was able to execute the test successfully.

$CredName = "user@domain.com"
$CredPass = ConvertTo-SecureString "superdupersecurepasswordhere" -AsPlainText -Force

$azureCredential = New-Object System.Management.Automation.PSCredential -ArgumentList ($CredName, $CredPass)
$azureAccount = Connect-AzAccount -Credential $azureCredential
$azureToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$azureInstance = "myinstance.database.windows.net"
Write-Output $azureAccount | Format-Table
Write-Output "===========`n"
Write-Output "Azure Token`n===========`n$azureToken`n==========="
$server = Connect-DbaInstance -SqlInstance $azureInstance -AccessToken $azureToken
Write-Output $server
Invoke-DbaQuery -SqlInstance $server -Query "select 1 as test"
Invoke-DbaQuery -SqlInstance $server -Query "select @@servername,@@version"

output

WARNING: TenantId 'my-tenant' contains more than one active subscription. First one will be selected for further use. To select another subscription, use Set-AzContext.

Account               SubscriptionName       TenantId  Environment
-------               ----------------       --------  -----------
user@domain.com       MySub-NonProduction-S1 my-tenant AzureCloud

===========

Azure Token
===========
crazylongtokenhere
===========

Instance Name                   Version  ProductLevel UpdateLevel HostPlatform HostDistribution
-------------                   -------  ------------ ----------- ------------ ----------------
myinstance.database.windows.net 15.0.100 RTM          n/a         Windows      Windows Server 2019 Datacenter

test : 1

Column1 : myinstance.database.windows.net
Column2 : Microsoft SQL Azure (RTM) - 12.0.2000.8
                Apr 29 2021 13:52:20
                Copyright (C) 2019 Microsoft Corporation

PS C:\Users\muncach\Code>

[andreasjordan]

@cmuncan Thank you very much.

[niphlod]

@andreasjordan : works on Azure too