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
– vonPryz, Commented Jun 26, 2018 at 8: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
– mjsqu, Commented Jun 26, 2018 at 8:46
Check by adding Write-Host $SQLQuery, that'll show you what the variable contains — mjsqu
– mjsqu, Commented Jun 26, 2018 at 8:47
Where do you specify the database to which you connect? Are you connecting to a specific SQL Server instance ? — David Brabant
– David Brabant, Commented Jun 26, 2018 at 8:47
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
– mjsqu, Commented Jun 26, 2018 at 8:57

Andrea Visnenza Andy · Accepted Answer · 2018-06-26 09:15:59Z

1

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

answered Jun 26, 2018 at 9:15

Andrea Visnenza Andy

1633 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Can't connect to database using PowerShell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related