Can't connect to database using PowerShell

Question

I'm having issues with sqlps in my PowerShell script. I'm trying to select rows from a table and save them to a .csv file.

The query is

$TABLE="events"

Import-Module sqlps
$SQLquery='SELECT * FROM dbo.$TABLE'
$result=invoke-sqlcmd -query $SQLquery -HostName LOCALHOST -Password test -Username test
$result |export-csv c:\TEST.csv -notypeinformation

I get this error:

invoke-sqlcmd : Login failed for user 'test'.
At line:6 char:9
+ $result=invoke-sqlcmd -query $SQLquery -HostName LOCALHOST -Password  ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management. 
   PowerShell.GetScriptCommand

invoke-sqlcmd : 
At line:6 char:9
+ $result=invoke-sqlcmd -query $SQLquery -HostName LOCALHOST -Password  ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], ParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Could anyone be able to help?

Take a look at Sql Server's log file. It will contain more detailed an error message. — vonPryz, Jun 26 '18 at 08:45
Single quotes for the `$SQLQuery` variable mean that it literally looks for `$TABLE`, not the `events` table I think you meant. Oh, and the password is probably wrong, I didn't read that bit properly! — mjsqu, Jun 26 '18 at 08:46
Check by adding `Write-Host $SQLQuery`, that'll show you what the variable contains — mjsqu, Jun 26 '18 at 08:47
Where do you specify the database to which you connect? Are you connecting to a specific SQL Server instance ? — David Brabant, Jun 26 '18 at 08:47
when i use write-host it returns SELECT * FROM dbo.$TABLE. cool thanks alot. lemme try fix that quick — Andrea Visnenza Andy, Jun 26 '18 at 08:50
ok so changed the single quotes to double quotes and now its getting the variable in the query :) thanks @mjsqu — Andrea Visnenza Andy, Jun 26 '18 at 08:52
Where's the database located? The example you're running assumes the database is on the same machine that the code is running on. If that's correct then the credentials must be wrong. Also the `-Database` switch is usually required as @DavidBrabant said — mjsqu, Jun 26 '18 at 08:57
ok so the issue was that even tho i had a seperate user on sql auth, the db only allowed windows authentication. thanks for telling me about the logs. didnt know that they logged login errors aswell — Andrea Visnenza Andy, Jun 26 '18 at 09:06
But now im getting a permission error when i try write the result to csv. any reason for that? — Andrea Visnenza Andy, Jun 26 '18 at 09:07

score 1 · Answer 1 · answered Jun 26 '18 at 09:15

So the main issue was the query needed to be in "" quotes not '' quotes and the server needs to be told to explicitly allow Microsoft account logins and SQL logins. even if you have a user setup for sql login. the option is under properties/security/server authentication.

Im leaving this answer here for if anyone ever runs into this beginner issue as i have. Thanks for the help from the community to get this sorted so quickly

Can't connect to database using PowerShell

1 Answers1