Write-DbaDbTableData seems to ignore SqlCredential when Creating SqlBulkCopy object

[sau666thebeast]

Try to use Write-DbaDbTableData but it seems to ignore the given SqlCredential Object.
I try to import an Textfile into an SQL Server Table.
I'm running powershell ISE as mydom\myUserUser (has no rights to connect to the SQL Server). Connection should be done with mydom\mySQLUser( has all rights).
But getting the error that the Bulk import failed because mydom\myUserUser has no right to connect.

The Error:

Write-DbaDbTableData -SqlInstance mysqlserver -InputObject $datatable -SqlCredential $cred -Database mydatabase -Schema myschema -Table mytable -Verbose AUSFÜHRLICH: [17:17:40][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 AUSFÜHRLICH: [17:17:40][Connect-DbaInstance] authentication method is 'local ad' AUSFÜHRLICH: [17:17:40][Write-DbaDbTableData] FQTN processed: [mydatabase].[myschema].[mytable] AUSFÜHRLICH: [17:17:40][Write-DbaDbTableData] Creating SqlBulkCopy object AUSFÜHRLICH: [17:17:40][Invoke-BulkCopy] Importing in bulk to [mydatabase].[myschema].[mytable] WARNUNG: [17:17:50][Write-DbaDbTableData] Failed to bulk import to [mydatabase].[myschema].[mytable]| Die von der Anmeldung mydatabase en-Datenbank kann nicht geöffnet werden. Fehler bei der Anmeldung. Fehler bei der Anmeldung für den Benutzer "mydom\myUserUser". AUSFÜHRLICH: [17:17:50][Disconnect-DbaInstance] removing from connection hash

The script:

Import-Module dbatools
$cred = Get-Credential mydom\mySQLUser
$datatable = Import-Csv d:\text.txt
Write-DbaDbTableData -SqlInstance mysqlserver -InputObject $datatable -SqlCredential $cred -Database mydatabase -Schema myschema -Table mytable -Verbose

Myenv:

PSVersion 5.1.17763.2090
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.2090
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

The currently installed version(s) of dbatools is/are:
dbatools: 1 1 13 -1 (old befor 2021-09-03 )
dbatools: 1 1 15 -1 (update to on 2021-09-03)

Microsoft SQL Server Standard (64-bit) 13.0.5103.6

I confirmed with
Connect-DbaInstance -SqlInstance mysqlserver -SqlCredential $cred
that I can connect to the Server and I’m logged in as mydom\mySQLUser

Edit 2021-09-01:
When I start the ISE Process with my mydom\mySQLUser it works, but this is not allowed in our enviroment.
For me there is a Problem when "Creating SqlBulkCopy object" the credentials providet by SqlCredential are not given to the SqlBulkCopy object

Edit 2021-09-03:
checked
[Bug] - Write-DbaDbTableData - Fails to bulk insert when using windows credentials in -SqlCredential #6618
Write-DbaDbTableData - Use better contructor for SqlBulkCopy #7460
but did not help.

Did I get something wrong or messed something up using Write-DbaDbTableData

regards

Michael

[andreasjordan]

See #7762

[sau666thebeast]

thanks for your answer Andreas.
#7762 (comment) yes this would be more clear

[andreasjordan]

If #7764 is merged, it will work again - so we don't have to change the documentation.