write-dbadbdatatable - cannot use connection created by connect-dbainstance

[majst32]

write-dbadbdatatable - cannot use connection created by connect-dbainstance

[andreasjordan]

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

[majst32]

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

[andreasjordan]

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]

Thank you!

[majst32]

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]

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]

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]

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?

[andreasjordan]

OK, I can reproduce the error. The line $databaseObject.Tables.Refresh() throws the error. It looks like the "Database" part of the SMO is not filled at all (only "Name" is set). Will do more investigations...

[andreasjordan]

The problem is not inside of Write-DbaDbDatatable but inside of Connect-DbaInstance when using -AccessToken. If I connect to the same Azure SQL Database with username and password, it works perfectly.

To test, run $server.Databases.Tables.Count. It should return the number of tables in the database, but returns nothing if the connection is made with access token.

[andreasjordan]

For more information about AccessToken see this issue from 2019: #5445

As we only use the SMO inside of Write-DbaDbDatatable to test if the table (and the schema) exists, I changed this to just test the table by running a select against it.

Will open a PR soon.

[andreasjordan]

My PR has some side effects - other tests fail now. I have to do more tests. Will do this in the next days...

[andreasjordan]

I now have a working version of the pull request. Waiting for the review and merge...