majst32 commented 2 weeks ago

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Login failed for user ''. At line:97970 char:9

throw $records[0]


+ CategoryInfo          : NotSpecified: (:) [], Exception
+ FullyQualifiedErrorId : dbatools_Write-DbaDbTableData

Steps to Reproduce

$SQLCredential = get-credential #(this is a Service Principal / Azure application)

this works and I get an object of type Microsoft.SqlServer.Management.Smo.Server in $server

$azureAccount = Connect-AzAccount -Credential $SQLCredential -ServicePrincipal -Tenant "redacted" $azureToken = Get-AzAccessToken -ResourceUrl https://database.windows.net $azureInstance = "" $azureDatabase = "" $server = Connect-DbaInstance -SqlInstance $azureInstance -Database $azureDatabase -AccessToken $azureToken

$StepID = 1 $StepStatus = "pending" $ThingID = 999

using the connection in $server, this successfully inserts a row into the database

$queryparams = @{ Query = 'INSERT INTO redactedTable (StepID, StepStatus, ThingID) VALUES (@StepID, @StepStatus, @ThingID); SELECT SCOPE_IDENTITY() AS ID;' SqlParameter = @{ StepID = $StepId; StepStatus = $StepStatus; ThingID = $ThingID} SQLInstance = $server } Invoke-DbaQuery @queryparams -EnableException -ErrorAction Stop

using the same connection in $server, neither one of the below Write-DBADBTableData commands work to insert a row

$Object = [pscustomObject]@{ StepId = 2 StepStatus = "pending" ThingID = 999 }

$writeparams = @{ SQLInstance = $server Table = "redactedTable" }

Write-DbaDbTableData @writeparams -InputObject $Object -EnableException -ErrorAction Stop

Write-DbaDbTableData -SqlInstance $server -table "redactedTable" -InputObject $object -EnableException -ErrorAction Stop

Login failed for user ''.

Please confirm that you are running the most recent version of dbatools

2.1.16

Other details or mentions

Background: I am connecting to an Azure SQL instance using an Azure Service Principal. I connect to the instance using connect-DBAInstance and have a connection object in $server. I am able to use $server as the value for the SQLInstance parameter for invoke-dbaquery and insert a row into the database. I am not able to use that same instance of $server to insert a row using write-dbadbdatatable. When I attempt it, I get the Login failed for user ''. message above.

What PowerShell host was used when producing this error

Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Name Value

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

SQL Server Edition and Build number

.NET Framework Version

.NET Framework 4.8.4729.0

andreasjordan commented 2 weeks ago

Can you run the Write- command with -Verbose and provide the output?

majst32 commented 2 weeks ago

VERBOSE: [17:57:03][Write-DbaDbTableData] FQTN processed: [dbo].[redactedTable]

andreasjordan commented 2 weeks ago

Ok, I hoped for more output. I will have a look at the code in the next days and try to provide a workaround or a fix.

majst32 commented 2 weeks ago

Thank you!

majst32 commented 2 weeks ago

As my workaround I converted my functions to use invoke-dbaquery. It works, but it's messier than I'd like it to be when I have tables with lots of columns.

andreasjordan commented 2 weeks ago

Looking at the code I would expect more verbose output. You are using a pooled connection, so inside of Write-DbaDbTableData, Connect-DbaInstece is called and that command has also verbose output. Do you really only get this one line?

The problem in your case might be, that inside of Write-DbaDbTableData a new connection is opened and not the connection from $server is reused.

It might help to open the connection as non-pooled ($server = Connect-DbaInstance -SqlInstance $azureInstance -Database $azureDatabase -AccessToken $azureToken -NonPooledConnection) because then there is a different code path used inside of Write-DbaDbTableData.

majst32 commented 2 weeks ago

I tried creating the $server object as both pooled and non-pooled with the same result. I also used -verbose and -debug and received the same output.

VERBOSE: [14:56:20][Write-DbaDbTableData] FQTN processed: [dbo].[redactedTable] DEBUG: 88670 | [14:56:20][Write-DbaDbTableData] FQTN processed: [dbo].[redactedTable] DEBUG: [14:56:20][Write-DbaDbTableData] Failure | Login failed for user ''.

andreasjordan commented 2 weeks ago

I'll try to setup my azure lab to be able to use a token - will probably take some days.

Most probably it has something to do with the way we create the server SMO when using a token. @potatoqualitee do you have a lab where can test that?