dataplat
Get-DbaDatabase is not properly using connection object from Connect-DbaInstance when MFA is required
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
InnerException : False
Exception : System.Management.Automation.ExtendedTypeSystemException: The following exception occurred while trying to enumerate the
collection: "Failed to connect to server <redacted_server>.database.windows.net.". --->
Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server
<redacted_server>.database.windows.net. ---> Microsoft.Data.SqlClient.SqlException: Login failed for user ''.
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1
wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean
callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword,
SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean
isFirstTransparentAttempt, Boolean disableTnir)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword,
SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential
credential, TimeoutTimer timeout)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString
connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean
redirectedUserInstance)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString
connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword,
Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData,
ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool
pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean
applyTransientFaultHandling)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey
poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions
userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection,
DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection,
TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal&
connection)
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection,
DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.get_IsContainedAuthentication()
at Microsoft.SqlServer.Management.Sdk.Sfc.ConnectionHelpers.UpdateConnectionInfoIfContainedAuthentication(Object&
connectionInfo, Urn urn)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean
forScripting, IEnumerable`1 extraFields)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh, ScriptingPreferences
sp, String filterQuery, IEnumerable`1 extraFields)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetEnumerator(ScriptingPreferences sp)
at System.Management.Automation.EnumerableOps.GetEnumerator(IEnumerable enumerable)
--- End of inner exception stack trace ---
at System.Management.Automation.EnumerableOps.GetEnumerator(IEnumerable enumerable)
at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
at System.Management.Automation.LanguagePrimitives.GetEnumerator(Object obj)
at System.Management.Automation.Internal.PipelineProcessor.Inject(Object input, Boolean enumerate)
at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input)
at System.Management.Automation.PipelineOps.InvokePipeline(Object input, Boolean ignoreInput,
CommandParameterInternal[][] pipeElements, CommandBaseAst[] pipeElementAsts, CommandRedirection[][] commandRedirections,
FunctionContext funcContext)
at System.Management.Automation.Interpreter.ActionCallInstruction`6.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
Message : The following exception occurred while trying to enumerate the collection: "Failed to connect to server
<redacted_server>.database.windows.net.".
StackTrace : at System.Management.Automation.EnumerableOps.GetEnumerator(IEnumerable enumerable)
at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
at System.Management.Automation.LanguagePrimitives.GetEnumerator(Object obj)
at System.Management.Automation.Internal.PipelineProcessor.Inject(Object input, Boolean enumerate)
at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input)
at System.Management.Automation.PipelineOps.InvokePipeline(Object input, Boolean ignoreInput,
CommandParameterInternal[][] pipeElements, CommandBaseAst[] pipeElementAsts, CommandRedirection[][] commandRedirections,
FunctionContext funcContext)
at System.Management.Automation.Interpreter.ActionCallInstruction`6.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
HelpLink :
ErrorDetails :
ErrorCategory : NotSpecified: (:) [], ExtendedTypeSystemException
InvocationInfo : System.Management.Automation.InvocationInfo
ScriptStackTrace : at <ScriptBlock>, <No file>: line 28
Steps to Reproduce
The following code snippet should sufficiently reproduce the error/issue. Replace all redacted information with that of your choosing and run the code. PowerShell 5.1.19041.3031 is used, but PowerShell 7 can be used as well with the same results, just switch out Resolve-Error with Get-Error (refer to comments below):
# Specify Tenet and Subscription Info
$azureTenet = "<redacted_tenet>"
$azureSubscription = "<redacted_sub>"
# Specify Azure Instance and Database Information (in my case this is one of a number of databses in an Elastic Pool)
$azureInstance = "<redacted_server>.database.windows.net"
$azureDatabase = "<redacted_db>"
# Initiate Pop-up window asking for MFA authentication
$azureAccount = Connect-AzAccount -Tenant $azureTenet -Subscription $azureSubscription
# Get AzAccessToken
$azureToken = Get-AzAccessToken -ResourceUrl https://database.windows.net
# Pass token to Connect-DbaInstance. Including -Tenant parameter doesn't seem to matter
$azureServer = Connect-DbaInstance -SqlInstance $azureInstance -Database $azureDatabase -AccessToken $azureToken -Tenant $azureTenet
# Confirm Connectivity, Huzzah, returns <redacted_server>, as expected
Invoke-DbaQuery -SqlInstance $azureServer -Query "SELECT @@SERVERNAME" | Format-Table -AutoSize
# Try to pull Database Info via Get-DbaDatabase command
# do not specify -SqlCredential or -SqlDatabase per docs. Including -Database paramater doesn't seem to matter
$azureDBObjects = Get-DbaDatabase -SqlInstance $azureServer -Database $azureDatabase;
Write-Host $azureDBObjects
#enumerate Tables
$azureDBObjects.Tables | Select-Object Schema,Name;
#Uncomment Get-Error and comment out #Resolve-Error if PS 7/Core is being used
#Get-Error | Select-Object -First 1
Resolve-Error | Select-Object -First 1
# failure pops when trying to enumerate the collection, here:
#$azureDBObjects.Tables | Select-Object Schema,Name;
# Still works, so the connection is still able to be passed along to subsequent Invoke-DbaQuery calls, just not Get-DbaXXX calls... :(
#Invoke-DbaQuery -SqlInstance $azureServer -Query "SELECT OBJECT_SCHEMA_NAME([object_id]) AS [schema], [name] FROM sys.tables" | Format-Table -AutoSize
Please confirm that you are running the most recent version of dbatools
2.1.1
Other details or mentions
MFA is a hard requirement in my use-case
What PowerShell host was used when producing this error
Windows PowerShell ISE (powershell_ise.exe)
PowerShell Host Version
Name Value
PSVersion 5.1.19041.3031
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.19041.3031
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
SQL Server Edition and Build number
Microsoft SQL Azure (RTM) - 12.0.2000.8 Jul 17 2023 18:40:52 Copyright (C) 2022 Microsoft Corporation
.NET Framework Version
.NET Framework 4.8.4645.0
This is functionality in SMO because you are using a nested class of the Database class. https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.database.tables?view=sql-smo-160#microsoft-sqlserver-management-smo-database-tables
We would have no control over passing the MFA token unless Microsoft choses to have the class inherit the token object; which none of them do that right now that I'm aware.
Alternative is try using Get-DbaDbTable instead.
I could just reproduce the issue (partially)...
The Get-DbaDatabase runs fine on my script, but the SMO is very empty because of lazy loading build in the SMO. When I want to get all properties by $azureDBObjects | Format-List -Property *I get the error "Failed to connect to server".
To show that it's not the command Get-DbaDatabase, please try to use the server SMO directly by running $azureServer.Databases | Format-List -Property *. That should give you all the properties of the connected database.
If this failes, we can do nothing about it. I could also reproduce it with version 1.1.145 of dbatools, so it's not new with the new libraries. Maybe SMO is currently just incompatible with SqlDatabase.
Sorry, I don't see a way to help here - maybe open an issue against the SMO repository.
Thanks for taking a look into it and confirming I'm taking the right approach here, albeit in vain. My only suggestion as a result of this is to update the documentation on the site to mention this deficiency with SMO at the moment. Personally, I find the current documentation slightly ambiguous and implying that my approach is in fact viable and supported.
Let me reopen the issue as this is a big problem for all users that use dbatools with SqlDatabase.
@potatoqualitee / @wsmelton - can you have a look at this and test in your environment?
Our Azure lab does not have MFA configuration available to test, it requires a good bit of setup. I do not have any Azure SQL DBs deployed at work to test against right now.
Overall, the only fix is to update the parameter help on the Connect command
https://github.com/dataplat/dbatools/blob/73ff74e4efe569025bd4c544837ff71b790c8ed7/public/Connect-DbaInstance.ps1#L143
This should call out if your environment requires MFA authentication for user accounts we recommend you use a Service Principal for token authentication to Azure. Most teams at Microsoft are going to recommend this approach as well.
Service Principal tokens are fine for stuff like pipeline work where it's all gated behind automation, secrets, key-vaults, etc, but for one-off work against an Azure SQL database, you're not going to easily get around the MFA requirement. My only ask is that the documentation properly reflects this current shortfall. I assume it will eventually be resolved, but until then, the documentation shouldn't imply MFA is supported.