Invoke-DbaQuery SqlCredential Issues

[ghost]

This may be more user education than actual issue, but going to start here anyway - I am having trouble getting 'Invoke'DbaQuery' to use the credential object I am passing to it. here's an example of my code:

$user = "foo\bar"
$pass = "blahblah!"
$Instance = "SOMEINSTANCE"
$Port = "1433"
$ss = $pass | ConvertTo-SecureString -AsPlainText -Force
$Creds = New-Object System.Management.Automation.PSCredential ($user, $ss)
Invoke-DbaQuery -SqlInstance "$Instance,$Port" -Query "Select @@Version" -SqlCredential $Creds

I was able to get this code working successfully when using version 1.0.40 of the module, but after realizing I am way behind on version updates I decided to update, and on the newer version this code/commands keeps defaulting the login attempts to the user running the powershell session, not the user specified in the PSCredential object. Any help would be greatly appreciated!

[potatoqualitee]

hey @ghsjddemcher ! That doesn't seem right. Your code seems right, but not the results 😅 What version are you using now? What is your OS? Version 1.1.4 is our latest release.

If you run

$DebugPreference = "Continue"
$user = "foo\bar"
$pass = "blahblah!"
$Instance = "SOMEINSTANCE"
$Port = "1433"
$ss = $pass | ConvertTo-SecureString -AsPlainText -Force
$Creds = New-Object System.Management.Automation.PSCredential ($user, $ss)
Invoke-DbaQuery -SqlInstance "$Instance,$Port" -Query "Select @@Version" -SqlCredential $Creds -Debug

You will see a ton of info that could help you figure it out.

This is what mine looks like

$DebugPreference = "Continue"
$user = "sqladmin"
$pass = "dbatools.IO"
$Instance = "localhost"
$Port = "1433"
$ss = $pass | ConvertTo-SecureString -AsPlainText -Force
$Creds = New-Object System.Management.Automation.PSCredential ($user, $ss)
Invoke-DbaQuery -SqlInstance "$Instance,$Port" -Query "Select @@Version" -SqlCredential $Creds -Debug

DEBUG: 176 | [14:29:21][Invoke-DbaQuery] Bound parameters: SqlInstance, Query, SqlCredential, Debug
DEBUG: 372 | [14:29:21][Invoke-DbaQuery] SqlInstance passed in, will work on: localhost,1433
DEBUG: 463 | [14:29:21][Connect-DbaInstance] Starting process block
DEBUG: 474 | [14:29:21][Connect-DbaInstance] Immediately checking for Azure
DEBUG: 365 | [14:29:21][Test-Azure] Test for Azure is negative
DEBUG: 481 | [14:29:21][Connect-DbaInstance] Starting loop for 'localhost,1433': ComputerName = 'localhost',
InstanceName = 'MSSQLSERVER', IsLocalHost = 'True', Type = 'Default'
DEBUG: 592 | [14:29:21][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
DEBUG: 365 | [14:29:21][Test-Azure] Test for Azure is negative
DEBUG: 713 | [14:29:21][Connect-DbaInstance] authentication method is 'local sql'
DEBUG: 736 | [14:29:21][Connect-DbaInstance] AdditionalParameters will be appended by 'MultiSubnetFailover=True;'
DEBUG: 748 | [14:29:21][Connect-DbaInstance] ApplicationName will be set to 'dbatools PowerShell module -
dbatools.io'
DEBUG: 780 | [14:29:21][Connect-DbaInstance] ConnectionTimeout will be set to '15'
DEBUG: 810 | [14:29:21][Connect-DbaInstance] PacketSize will be set to '4096'
DEBUG: 829 | [14:29:21][Connect-DbaInstance] Pooled will be set to 'True'
DEBUG: 838 | [14:29:21][Connect-DbaInstance] SecurePassword will be set
DEBUG: 864 | [14:29:21][Connect-DbaInstance] UserName will be set to 'sqladmin'
DEBUG: 888 | [14:29:21][Connect-DbaInstance] Building ServerConnection from SqlConnectionInfo
DEBUG: 890 | [14:29:21][Connect-DbaInstance] ServerConnection was built
DEBUG: 908 | [14:29:21][Connect-DbaInstance] Building Server from ServerConnection
DEBUG: 910 | [14:29:21][Connect-DbaInstance] Server was built
DEBUG: 929 | [14:29:21][Connect-DbaInstance] Setting ConnectionContext.StatementTimeout to '0'
DEBUG: 934 | [14:29:21][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data
Source=localhost,1433;User ID=sqladmin;Password=********;Multiple Active Result Sets=False;Encrypt=False;Trust Server
Certificate=False;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io";Multi Subnet
Failover=True
DEBUG: 944 | [14:29:21][Connect-DbaInstance] We connect to the instance by running SELECT 'dbatools is opening a new
connection'
DEBUG: 953 | [14:29:21][Connect-DbaInstance] We have a connected server object
DEBUG: 999 | [14:29:21][Connect-DbaInstance] We will set server.ComputerName to server.NetName
DEBUG: 1005 | [14:29:21][Connect-DbaInstance] ComputerName will be set to workstation
DEBUG: 365 | [14:29:21][Test-Azure] Test for Azure is negative
DEBUG: 1015 | [14:29:21][Connect-DbaInstance] We added IsAzure = 'False', DbaInstanceName = instance.InstanceName =
'MSSQLSERVER', SqlInstance = server.DomainInstanceName = '', NetPort = instance.Port = '1433', ConnectedAs =
server.ConnectionContext.TrueLogin = 'sqladmin'
DEBUG: 1018 | [14:29:21][Connect-DbaInstance] We return the server object
DEBUG: 1046 | [14:29:21][Connect-DbaInstance] SetDefaultInitFields will be used
DEBUG: 8 | [14:29:21][Add-ConnectionHashValue] Adding to connection hash
DEBUG: 1071 | [14:29:21][Connect-DbaInstance] We are finished with this instance
DEBUG: 171 | [14:29:21][Invoke-DbaAsync] Stripping GOs from source

The line with the connection string is probably the most important (note that the password is always blanked out in the output)

DEBUG: 934 | [14:29:21][Connect-DbaInstance] The masked server.ConnectionContext.ConnectionString is Data
Source=localhost,1433;User ID=sqladmin;Password=********;Multiple Active Result Sets=False;Encrypt=False;Trust Server
Certificate=False;Packet Size=4096;Application Name="dbatools PowerShell module - dbatools.io";Multi Subnet
Failover=True

That shows you that it's using the proper username and password.

Alternatively, make sure that you can login via SSMS. You can create a SQL Auth user w/o SQL Auth being enabled on the SQL Server instance, which will always say login failed.