Export-DbaDbTableData and credentials

[bytepl]

Hi,

I am working on Azure DevOps pipeline which - among other things - exports some tables from a database. I installed dbatools on a server where pipeline agent runs (as Network Service, this is important). Agent executes this code:

param (
    [string]$Username,
    [string]$Password
)

[securestring]$secStringPassword = ConvertTo-SecureString $Password -AsPlainText -Force
[pscredential]$credObject = New-Object System.Management.Automation.PSCredential ($userName, $secStringPassword)


$DestinationDBServer = 'mysqlserver.mydomain.local'
$DestinationDatabase = 'mydatabase'

$table = Get-DbaDbTable -SqlInstance $DestinationDBServer -Database $DestinationDatabase -Table 'dbtable' -SqlCredential $credObject
$table | Export-DbaScript -FilePath 'C:\temp\export\dbtable.sql'
$table | Export-DbaDbTableData -FilePath 'C:\temp\export\dbtable.sql' -Append

It fails with:

2021-01-28T18:38:29.5786905Z WARNING: [19:38:29][Export-DbaScript] Failure on mysqlserver.mydomain.local | 
2021-01-28T18:38:29.5787874Z System.Management.Automation.MethodInvocationException: Exception calling "EnumScript" with "1" argument(s): "Script 
2021-01-28T18:38:29.5789321Z failed for Table 'dbo.mydatabase'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Script
2021-01-28T18:38:29.5889488Z  failed for Table 'dbo.mydatabase'.  ---> System.Data.SqlClient.SqlException: Login failed for user 
2021-01-28T18:38:29.5890740Z 'DOMAIN\JUMPHOST$'.
2021-01-28T18:38:29.5899463Z 
2021-01-28T18:38:29.5901004Z    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString 
2021-01-28T18:38:29.5902481Z connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, 
2021-01-28T18:38:29.5904037Z Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, 
2021-01-28T18:38:29.5920015Z DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager 
2021-01-28T18:38:29.5928597Z sqlAuthProviderManager)
2021-01-28T18:38:29.5948074Z 
2021-01-28T18:38:29.5952719Z    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey 
2021-01-28T18:38:29.5957276Z poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions 
2021-01-28T18:38:29.5960137Z userOptions)
2021-01-28T18:38:29.5961195Z 
2021-01-28T18:38:29.5962580Z    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, 
2021-01-28T18:38:29.5964465Z DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
2021-01-28T18:38:29.5966984Z 
2021-01-28T18:38:29.5983215Z    at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, 
2021-01-28T18:38:29.5994402Z TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, 
2021-01-28T18:38:29.6005366Z DbConnectionInternal& connection)
2021-01-28T18:38:29.6015318Z 
2021-01-28T18:38:29.6030307Z    at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, 
2021-01-28T18:38:29.6039445Z DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
2021-01-28T18:38:29.6058031Z 
2021-01-28T18:38:29.6081836Z    at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
2021-01-28T18:38:29.6083723Z 
2021-01-28T18:38:29.6084829Z    at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
2021-01-28T18:38:29.6089636Z 
2021-01-28T18:38:29.6093063Z    at System.Data.SqlClient.SqlConnection.Open()
2021-01-28T18:38:29.6094184Z 
2021-01-28T18:38:29.6094712Z    at Microsoft.SqlServer.Management.Smo.DataEnumerator.get_Connection()
2021-01-28T18:38:29.6095196Z 
2021-01-28T18:38:29.6095532Z    at Microsoft.SqlServer.Management.Smo.DataEnumerator.MoveNext()
2021-01-28T18:38:29.6095812Z 
2021-01-28T18:38:29.6096199Z    at Microsoft.SqlServer.Management.Smo.ExtensionClass.AddCollection(StringCollection collection, IEnumerable`1 
2021-01-28T18:38:29.6096582Z enumerableString)
2021-01-28T18:38:29.6096794Z 
2021-01-28T18:38:29.6097166Z    at Microsoft.SqlServer.Management.Smo.SmoStringWriter.ScriptData(IEnumerable`1 dataScript, Urn table)
2021-01-28T18:38:29.6097493Z 
2021-01-28T18:38:29.6097870Z    at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateSpecialUrn(Urn urn, ScriptingPreferences sp, 
2021-01-28T18:38:29.6098268Z ObjectScriptingType& scriptType)
2021-01-28T18:38:29.6098529Z 
2021-01-28T18:38:29.6098873Z    at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, 
2021-01-28T18:38:29.6099255Z ObjectScriptingType& scriptType)
2021-01-28T18:38:29.6099514Z 
2021-01-28T18:38:29.6099848Z    at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
2021-01-28T18:38:29.6100158Z 
2021-01-28T18:38:29.6100594Z    at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns)
2021-01-28T18:38:29.6100929Z 
2021-01-28T18:38:29.6101286Z    at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
2021-01-28T18:38:29.6101616Z 
2021-01-28T18:38:29.6102005Z    at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
2021-01-28T18:38:29.6102353Z 
2021-01-28T18:38:29.6102742Z    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] 
2021-01-28T18:38:29.6103185Z objects, Boolean discoveryRequired)
2021-01-28T18:38:29.6103455Z 
2021-01-28T18:38:29.6103938Z    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, 
2021-01-28T18:38:29.6104376Z Boolean discoveryRequired)
2021-01-28T18:38:29.6104623Z 
2021-01-28T18:38:29.6104912Z    --- End of inner exception stack trace ---
2021-01-28T18:38:29.6105158Z 
2021-01-28T18:38:29.6105529Z    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, 
2021-01-28T18:38:29.6105927Z Boolean discoveryRequired)
2021-01-28T18:38:29.6106168Z 
2021-01-28T18:38:29.6106509Z    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(Urn[] urns, SqlSmoObject[] objects)
2021-01-28T18:38:29.6106827Z 
2021-01-28T18:38:29.6107421Z    at Microsoft.SqlServer.Management.Smo.Scripter.Script(Urn[] urns, SqlSmoObject[] objects)
2021-01-28T18:38:29.6107813Z 
2021-01-28T18:38:29.6108121Z    at CallSite.Target(Closure , CallSite , Object , Object )
2021-01-28T18:38:29.6108388Z 
2021-01-28T18:38:29.6108675Z    --- End of inner exception stack trace ---
2021-01-28T18:38:29.6108922Z 
2021-01-28T18:38:29.6109287Z    at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception 
2021-01-28T18:38:29.6110443Z exception)
2021-01-28T18:38:29.6111482Z 
2021-01-28T18:38:29.6114968Z    at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
2021-01-28T18:38:29.6115820Z 
2021-01-28T18:38:29.6116310Z    at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
2021-01-28T18:38:29.6116682Z 
2021-01-28T18:38:29.6117487Z    at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

As you can see, Export-DbaDbTableData tries to connect to SQL server and fails because it's using computer account (as NetworkService does) to connect to this machine. Unfortunately, Export-DbaDbTableData does not have -SqlCredential parameter so I cannot provide alternate credentials for this connection.

dbatools is installed only on the machine on which pipeline agent runs, so I cannot just use Invoke-Command -Credential $credObject -ComputerName $DestinationDBServer to run Export-DbaDbTableData directly on SQL server (with alternate credentials).

Any ideas how I can make it work? I strongly suspect that I am just doing this in a wrong way :)

[wsmelton]

There is no credential parameter for that command because the scripting object can't have a credential passed to it to connect as a different login/user.

Can you not have an AD account that has rights to that database server and can connect to the agent server as Invoke-Command -ComputerName <agentserver> -Credential $AdCred ?

[bytepl]

Object $credObject contains credentials of such an AD account. I tried to do this (basically invoking Export-DbaDbTableData in the context of this account on the same machine, doing a loopback connection):

$table = Get-DbaDbTable -SqlInstance $DestinationDBServer -Database $DestinationDatabase -Table 'dbtable' -SqlCredential $credObject
$table | Export-DbaScript -FilePath 'C:\temp\export\dbtable.sql'
Invoke-Command -ComputerName jumphost.domain.local -Credential $credObject -ScriptBlock {
    Export-DbaDbTableData -FilePath 'C:\temp\export\dbtable.sql' -InputObject $using:table -Append -ErrorAction Stop
}

but it fails:

2021-01-29T06:49:09.9982680Z Cannot process argument transformation on parameter 'InputObject'. Cannot convert value "[dbo].[dbtable]" 
2021-01-29T06:49:09.9983947Z to type "Microsoft.SqlServer.Management.Smo.Table[]". Error: "Cannot convert value "[dbo].[dbtable]" to 
2021-01-29T06:49:09.9985305Z type "Microsoft.SqlServer.Management.Smo.Table". Error: "Cannot convert the "[dbo].[dbtable]" value of 
2021-01-29T06:49:09.9987036Z type "Deserialized.Microsoft.SqlServer.Management.Smo.Table" to type "Microsoft.SqlServer.Management.Smo.Table".""