dbatools
dbatools copied to clipboard
dataplat
Azure Function App - Connect-DbaInstance fails with Managed Identity
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
We're attempting to connect from a Windows based Azure Function to an Azure SQL DB using the Function's system managed identity. We're already doing this from the same Function App to the same SQL Server using the SQL output binding. So, permissions and network are fine. For various reasons in this Function we can't use the output binding and must use dbatools.
$token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$server = Connect-DbaInstance -SqlInstance $env:sqlServerFqdn -Database $customerId -AccessToken $token -Verbose
We weren't able to use New-DbaAzAccessToken (failed with a network error) and are now using Get-AzAccessToken instead.
We're running on the latest Windows PowerShell Functions platform:
{
name: 'FUNCTIONS_EXTENSION_VERSION'
value: '~4'
}
{
name: 'FUNCTIONS_WORKER_RUNTIME'
value: 'powershell'
}
{
name: 'FUNCTIONS_WORKER_RUNTIME_VERSION'
value: '7.2'
}
The error we're getting is below:
2023-08-01T10:20:43.673 [Warning] WARNING: [10:20:43][Connect-DbaInstance] Failure |
Error connecting to [<serverName>.database.windows.net]: An attempt was made to load a program with an incorrect format. (0x8007000B)
2023-08-01T10:20:43.830 [Error] EXCEPTION: Error connecting to [<serverName>.database.windows.net]: An attempt was made to load a program with an incorrect format.
(0x8007000B)Exception :
Type : System.ExceptionMessage :
Error connecting to [<serverName>.database.windows.net]: An attempt was made to load a program with an incorrect format. (0x8007000B)InnerException :
Type : System.Management.Automation.MethodInvocationExceptionErrorRecord :Exception :
Type : System.Management.Automation.ParentContainsErrorRecordExceptionMessage :
Exception calling "ExecuteWithResults" with "1" argument(s): "Failed to connect to server <serverName>.database.windows.net.
"HResult : -2146233087
CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordExceptionFullyQualifiedErrorId :
ConnectionFailureExceptionInvocationInfo :
ScriptLineNumber : 5282
OffsetInLine : 21
HistoryId : -1
Line : $null = $server.ConnectionContext.ExecuteWithResults("SELECT 'dbatools is opening a new connection'")
PositionMessage :
At line:5282 char:21+ …
$null = $server.ConnectionContext.ExecuteWithResults("SEL …+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~CommandOrigin :
InternalScriptStackTrace :
at Connect-DbaInstance<Process>, <No file>: line 5282at <ScriptBlock>, C:\home\site\wwwroot\functionName\run.ps1: line 14TargetSite :
Name : CheckActionPreferenceDeclaringType : System.Management.Automation.ExceptionHandlingOps, System.Management.Automation, Version=7.2.11.500, Culture=neutral, PublicKeyToken=31bf3856ad364e35MemberType : MethodModule : System.Management.Automation.dllMessage : Exception calling "ExecuteWithResults" with "1" argument(s): "Failed to connect to server <serverName>.database.windows.net."Data : System.Collections.ListDictionaryInternalInnerException :Type : Microsoft.SqlServer.Management.Common.ConnectionFailureExceptionTargetSite :Name : ConnectDeclaringType : Microsoft.SqlServer.Management.Common.ConnectionManagerMemberType : MethodModule : Microsoft.SqlServer.ConnectionInfo.dllMessage : Failed to connect to server <serverName>.database.windows.net.InnerException :Type : System.TypeInitializationExceptionTypeName : Microsoft.Data.SqlClient.TdsParserTargetSite : Void .ctor(Boolean, Boolean)Message : The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception.InnerException :Type : System.TypeInitializationExceptionTypeName : Microsoft.Data.SqlClient.SNILoadHandleTargetSite :Name : get_EncryptionOptionsDeclaringType : Microsoft.Data.SqlClient.TdsParserStateObjectFactory, Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5MemberType : MethodModule : Microsoft.Data.SqlClient.dllMessage : The type initializer for 'Microsoft.Data.SqlClient.SNILoadHandle' threw an exception.InnerException :Type : System.BadImageFormatExceptionMessage : An attempt was made to load a program with an incorrect format. (0x8007000B)TargetSite :Name : SNIInitializeDeclaringType : Microsoft.Data.SqlClient.SNINativeMethodWrapper, Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5MemberType : MethodModule : Microsoft.Data.SqlClient.dllSource : Microsoft.Data.SqlClientHResult : -2147024885StackTrace :at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize(IntPtr pmo)at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize()at Microsoft.Data.SqlClient.SNILoadHandle..ctor()at Microsoft.Data.SqlClient.SNILoadHandle..cctor()Source : Microsoft.Data.SqlClientHResult : -2146233036StackTrace :at Microsoft.Data.SqlClient.TdsParserStateObjectFactory.get_EncryptionOptions()at Microsoft.Data.SqlClient.TdsParser..cctor()Source : Microsoft.Data.SqlClientHResult : -2146233036StackTrace :at Microsoft.Data.SqlClient.TdsParser..ctor(Boolean MARS, Boolean fAsynchronous)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, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)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.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)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()Source : Microsoft.SqlServer.ConnectionInfoHResult : -2146233087StackTrace :at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)at CallSite.Target(Closure , CallSite , Object , String )Source : System.Management.AutomationHResult : -2146233087StackTrace :at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)HResult : -2146233088TargetObject : <serverName>.database.windows.netCategoryInfo : ConnectionError: (serverName…atabase.windows.net:String) [], ExceptionFullyQualifiedErrorId : dbatools_Connect-DbaInstanceInvocationInfo :ScriptLineNumber : 151461OffsetInLine : 9HistoryId : -1Line : throw $records[0]PositionMessage : At line:151461 char:9+ throw $records[0]+ ~~~~~~~~~~~~~~~~~CommandOrigin : InternalScriptStackTrace : at Stop-Function, <No file>: line 151461at Connect-DbaInstance<Process>, <No file>: line 5285at <ScriptBlock>, C:\home\site\wwwroot\functionName\run.ps1: line 14
2023-08-01T10:20:43.856 [Error] Executed 'Functions.functionName' (Failed, Id=06f36aef-3353-41ca-ae0d-373c13cb1486, Duration=253ms)Result: FailureException: Error connecting to [<serverName>.database.windows.net]: An attempt was made to load a program with an incorrect format. (0x8007000B)Stack: at System.Management.Automation.Runspaces.PipelineBase.Invoke(IEnumerable input)at System.Management.Automation.PowerShell.Worker.ConstructPipelineAndDoWork(Runspace rs, Boolean performSyncInvoke)at System.Management.Automation.PowerShell.Worker.CreateRunspaceIfNeededAndDoWork(Runspace rsToUse, Boolean isSync)at System.Management.Automation.PowerShell.CoreInvokeHelper[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)at System.Management.Automation.PowerShell.CoreInvoke[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)at System.Management.Automation.PowerShell.CoreInvoke[TOutput](IEnumerable input, PSDataCollection`1 output, PSInvocationSettings settings)at System.Management.Automation.PowerShell.Invoke[T](IEnumerable input, IList`1 output, PSInvocationSettings settings)at System.Management.Automation.PowerShell.Invoke[T]()at Microsoft.Azure.Functions.PowerShellWorker.PowerShell.PowerShellExtensions.InvokeAndClearCommands[T](PowerShell pwsh) in /mnt/vss/_work/1/s/src/PowerShell/PowerShellExtensions.cs:line 45at Microsoft.Azure.Functions.PowerShellWorker.PowerShell.PowerShellManager.ExecuteUserCode(Boolean addPipelineOutput, IDictionary outputBindings) in /mnt/vss/_work/1/s/src/PowerShell/PowerShellManager.cs:line 314at Microsoft.Azure.Functions.PowerShellWorker.PowerShell.PowerShellManager.InvokeFunction(AzFunctionInfo functionInfo, Hashtable triggerMetadata, TraceContext traceContext, RetryContext retryContext, IList`1 inputData, FunctionInvocationPerformanceStopwatch stopwatch) in /mnt/vss/_work/1/s/src/PowerShell/PowerShellManager.cs:line 262at Microsoft.Azure.Functions.PowerShellWorker.RequestProcessor.InvokeFunction(AzFunctionInfo functionInfo, PowerShellManager psManager, FunctionInvocationPerformanceStopwatch stopwatch, InvocationRequest invocationRequest) in /mnt/vss/_work/1/s/src/RequestProcessor.cs:line 352at Microsoft.Azure.Functions.PowerShellWorker.RequestProcessor.ProcessInvocationRequestImpl(StreamingMessage request, AzFunctionInfo functionInfo, PowerShellManager psManager, FunctionInvocationPerformanceStopwatch stopwatch) in /mnt/vss/_work/1/s/src/RequestProcessor.cs:line 319
Steps to Reproduce
see above
Please confirm that you are running the most recent version of dbatools
Functions are configured to install the latest version: 'dbatools' = '2.*'
Other details or mentions
No response
What PowerShell host was used when producing this error
Windows PowerShell (powershell.exe)
PowerShell Host Version
7.2
SQL Server Edition and Build number
Azure SQL
.NET Framework Version
Whatever Azure Functions v4 now come with. https://learn.microsoft.com/en-us/azure/azure-functions/functions-versions?tabs=v4&pivots=programming-language-powershell#languages
New-AzDbaAccessToken hits a localized endpoint that I do not believe exist in the Function's runtime environment, not sure we have anyone else using that successful with Functions.
If you are authenticating as a managed identity can you give this a try and see if the same error is produced?
$sqlcn = Connect-DbaInstance -SqlInstance '<servername>.<uniqueID>.database.windows.net,1433' -EncryptConnection -Tenant <prod tenant ID>
You might also try using the environment variables noted here #8694 that can help cut down on execution time of dbatools commands.
I tried that with the -EncryptConnection switch, which, according to the docs is a switch, but it behaves like a String param it seems.
$server = Connect-DbaInstance -SqlInstance $env:sqlServerFqdn -EncryptConnection -Tenant $env:ARM_TENANT_ID
Missing an argument for parameter 'EncryptConnection'. Specify a parameter of type 'System.String' and try again.
https://github.com/dataplat/dbatools/blob/master/public/Connect-DbaInstance.ps1#L304
Not a switch :)
Okay, new error when using this:
$server = Connect-DbaInstance -SqlInstance $env:sqlServerFqdn -EncryptConnection 'Mandatory' -Tenant $env:ARM_TENANT_ID
Just to mention again. This is on a version ~4 Windows Azure Functions Host. That means the following dotnet versions should be installed: https://learn.microsoft.com/en-us/azure/azure-functions/functions-versions?tabs=v4&pivots=programming-language-powershell#languages
2023-08-09T08:20:41Z [Error] EXCEPTION: Error connecting to [<servername>.database.windows.net]: An attempt was made to load a program with an incorrect format. (0x8007000B)
Exception :
Type : System.Exception
Message : Error connecting to [<servername>.database.windows.net]: An attempt was made to load a program with an incorrect format. (0x8007000B)
InnerException :
Type : System.Management.Automation.MethodInvocationException
ErrorRecord :
Exception :
Type : System.Management.Automation.ParentContainsErrorRecordException
Message : Exception calling "ExecuteWithResults" with "1" argument(s): "Failed to connect to server <servername>.database.windows.net."
HResult : -2146233087
CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordException
FullyQualifiedErrorId : ConnectionFailureException
InvocationInfo :
ScriptLineNumber : 5282
OffsetInLine : 21
HistoryId : -1
Line : $null = $server.ConnectionContext.ExecuteWithResults("SELECT 'dbatools is opening a new connection'")
PositionMessage : At line:5282 char:21
+ … $null = $server.ConnectionContext.ExecuteWithResults("SEL …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CommandOrigin : Internal
ScriptStackTrace : at Connect-DbaInstance<Process>, <No file>: line 5282
at <ScriptBlock>, C:\home\site\wwwroot\<functionName>\run.ps1: line 14
TargetSite :
Name : CheckActionPreference
DeclaringType : System.Management.Automation.ExceptionHandlingOps, System.Management.Automation, Version=7.2.11.500, Culture=neutral, PublicKeyToken=31bf3856ad364e35
MemberType : Method
Module : System.Management.Automation.dll
Message : Exception calling "ExecuteWithResults" with "1" argument(s): "Failed to connect to server <servername>.database.windows.net."
Data : System.Collections.ListDictionaryInternal
InnerException :
Type : Microsoft.SqlServer.Management.Common.ConnectionFailureException
TargetSite :
Name : Connect
DeclaringType : Microsoft.SqlServer.Management.Common.ConnectionManager
MemberType : Method
Module : Microsoft.SqlServer.ConnectionInfo.dll
Message : Failed to connect to server <servername>.database.windows.net.
InnerException :
Type : System.TypeInitializationException
TypeName : Microsoft.Data.SqlClient.TdsParser
TargetSite : Void .ctor(Boolean, Boolean)
Message : The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception.
InnerException :
Type : System.TypeInitializationException
TypeName : Microsoft.Data.SqlClient.SNILoadHandle
TargetSite :
Name : get_EncryptionOptions
DeclaringType : Microsoft.Data.SqlClient.TdsParserStateObjectFactory, Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5
MemberType : Method
Module : Microsoft.Data.SqlClient.dll
Message : The type initializer for 'Microsoft.Data.SqlClient.SNILoadHandle' threw an exception.
InnerException :
Type : System.BadImageFormatException
Message : An attempt was made to load a program with an incorrect format. (0x8007000B)
TargetSite :
Name : SNIInitialize
DeclaringType : Microsoft.Data.SqlClient.SNINativeMethodWrapper, Microsoft.Data.SqlClient, Version=5.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5
MemberType : Method
Module : Microsoft.Data.SqlClient.dll
Source : Microsoft.Data.SqlClient
HResult : -2147024885
StackTrace :
at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize(IntPtr pmo)
at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize()
at Microsoft.Data.SqlClient.SNILoadHandle..ctor()
at Microsoft.Data.SqlClient.SNILoadHandle..cctor()
Source : Microsoft.Data.SqlClient
HResult : -2146233036
StackTrace :
at Microsoft.Data.SqlClient.TdsParserStateObjectFactory.get_EncryptionOptions()
at Microsoft.Data.SqlClient.TdsParser..cctor()
Source : Microsoft.Data.SqlClient
HResult : -2146233036
StackTrace :
at Microsoft.Data.SqlClient.TdsParser..ctor(Boolean MARS, Boolean fAsynchronous)
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, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
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.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
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()
Source : Microsoft.SqlServer.ConnectionInfo
HResult : -2146233087
StackTrace :
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry)
at CallSite.Target(Closure , CallSite , Object , String )
Source : System.Management.Automation
HResult : -2146233087
StackTrace :
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
HResult : -2146233088
TargetObject : <servername>.database.windows.net
CategoryInfo : ConnectionError: (<serverName>…atabase.windows.net:String) [], Exception
FullyQualifiedErrorId : dbatools_Connect-DbaInstance
InvocationInfo :
ScriptLineNumber : 151461
OffsetInLine : 9
HistoryId : -1
Line : throw $records[0]
PositionMessage : At line:151461 char:9
+ throw $records[0]
+ ~~~~~~~~~~~~~~~~~
CommandOrigin : Internal
ScriptStackTrace : at Stop-Function, <No file>: line 151461
at Connect-DbaInstance<Process>, <No file>: line 5285
at <ScriptBlock>, C:\home\site\wwwroot\<functionName>\run.ps1: line 14
2023-08-09T08:20:41Z [Error] Executed 'Functions.<functionName>' (Failed, Id=d6f55730-2f31-4806-b208-632de5f3e37d, Duration=2808ms)
@wsmelton any idea what could be happening here? Otherwise I'll have to see if I can find an alternative way to talk to this database. Thanks
Not directly. I don't have an environment I can try to replicate.
If you are just querying data I'd recommend just using the sqlserver module. It is a bit lighter weight for just querying data.
Hmm, okay, thanks. We're writing data to multiple databases, conditional to certain input, otherwise I'd just use the SQL output binding.
@davidobrien1985 If you've tried it, please ignore me.... But I'm going down the same route and my Azure Function App has been fine since switching to:
Make sure the App Function is running 64 Bit PowerShell vs 32 Bit (this might've been a red haring though, but I think when running the App Function in 32 bits it gave me a lot of errors when using Invoke-dbaQuery, similar to yours)
Requirements
@{
'Az.Accounts' = '2.*'
'Az.Profile' = '0.*'
'DBATools' = '2.*'
}
In the PowerShell Function:
$Token = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net/').Token
$Server = Connect-DbaInstance -SqlInstance 'SERVER.windows.net' -Database 'DATABASE' -AccessToken $token
Invoke-DbaQuery -SqlInstance $Server -Database 'DATABASE' -Query $DBAToolsQuery -SqlParameter $DBAToolsQueryParams -ErrorAction stop
