Invoke-D365SqlScript error: Login failed for user ''."

[devax]

Hey all, I'm probably missing something obvious here. I'd be thankful if someone could help me out. I'm trying to use the Invoke-D365SqlScript commander. My script looks something like this:

$OldValue = "old"
$NewValue = "new"
$sqlQuery = "
UPDATE MYTABLE
    SET MYFIELD = REPLACE(MYFIELD, '$OldValue', '$NewValue')
    WHERE MYFIELD like '%$OldValue%'
"
Invoke-D365SqlScript -Command $sqlQuery -EnableException -Verbose -Debug

This is the output:

PS SQLSERVER:\> Invoke-D365SqlScript -Command $sqlQuery -EnableException -Verbose -Debug
VERBOSE: [12:24:36][Invoke-TimeSignal] The command 'Invoke-D365SqlScript' was already taking part in time measurement. The entry has been update with current date and time.
DEBUG: 3893 | [12:24:36][Invoke-TimeSignal] The command 'Invoke-D365SqlScript' was already taking part in time measurement. The entry has been update with current date and time.
VERBOSE: [12:24:36][Test-TrustedConnection] Capabilities based on the centralized logic in the psm1 file.
DEBUG: 5816 | [12:24:36][Test-TrustedConnection] Capabilities based on the centralized logic in the psm1 file.
DEBUG: 1958 | [12:24:36][Get-SQLCommand] Writing the bound parameters
VERBOSE: [12:24:36][Get-SQLCommand] Building the SQL connection string.
DEBUG: 1974 | [12:24:36][Get-SQLCommand] Building the SQL connection string.
DEBUG: 19907 | [12:24:36][Invoke-D365SqlScript] Executing a script against the database.
[12:24:36][Invoke-D365SqlScript] Something went wrong while executing custom sql script against the database. | Exception calling "Open" with "0" argument(s): "Login failed for user ''."
DEBUG: 19915 | [12:24:36][Invoke-D365SqlScript] Something went wrong while executing custom sql script against the database. | Exception calling "Open" with "0" argument(s): "Login failed for user ''."
WARNING: [12:24:36][Invoke-D365SqlScript] Stopping because of errors. | Something went wrong while executing custom sql script against the database.

Isn't that supposed to work that way? I'm using tools version 0.6.16 on a MS hosted tier 2 sandbox environment (connected per RDP).

As a workaround, I tried to pass the parameters manually after extracting them from the settings, like so:

$settings = Get-D365EnvironmentSettings

$user   = $settings.DataAccess.SqlUser
$pass   = $settings.DataAccess.SqlPwd
$server = $settings.DataAccess.DbServer
$db     = $settings.DataAccess.Database

Invoke-D365SqlScript -Command $sqlQuery -DatabaseServer $server -DatabaseName $db -SqlUser $user -SqlPwd "$pass" -EnableException -Verbose -Debug

This fails as well, the console output contains this message: Exception setting "ConnectionString": "The value's length for key 'password' exceeds it's limit of '128'."

The value for $pass is indeed very long, looks like an unencrypted value. Isn't that variable supposed to hold the unencrypted password?

Hope someone can help me clear my confusion. Thank you!

EDIT: This is D365 10.0.8/PU32.

[Splaxi]

Did you run from an elevated powershell session? 🤔

I firmly believe that if you filled in the user name and password as strings from lcs, by hand it works.

Did you try to use the Get-D365DatabaseAccess cmdlet?

https://github.com/d365collaborative/d365fo.tools/blob/master/d365fo.tools/functions/get-d365databaseaccess.ps1

[devax]

So embarrassing. Running in an elevated shell now and everything works as expected. I could have sworn last time I tried to run an elevated shell on these VMs it was not possible (asking for an admin account user/password). I must be getting old.

Sorry for bothering and thank you for your help!

[Splaxi]

Happy to hear that you got it working and could get going 🤘😀

You know where to find us if you need any more help 😉