#sqlps - Problems connecting to the server to query

Question

I'm trying to connect to a server\instance hosted remotely. The server is in a domain to which I'm connected as well (I'm able to login using SQL Server auth with the provided credentials from SSMS, locally)

Import-Module SQLPS

$query = 'select GETDATE() as date'

$op = invoke-Sqlcmd  -HostName 'servername' -Database  'DBName' -Username 'UN' -Password 'PWD' -Query "$query"

$op | Format-Table

Expected output woud be

2016-02-12 06:54:26.410

But what I get is

PS SQLSERVER:> Import-Module SQLPS
$query = 'select GETDATE() as date'
$op = invoke-Sqlcmd -HostName 'servername' -Database 'DBName' -Username 'UN' -Password 'Pwd' -Query "$query" #-IgnoreProviderContext
$op | Format-Table
WARNING: The names of some imported commands from the module 'SQLPS' include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.
invoke-Sqlcmd : Login failed for user 'UN'.
At line:3 char:7
+ $op = invoke-Sqlcmd -HostName 'servername' -Dat ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
+ FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

invoke-Sqlcmd :
At line:3 char:7
+ $op = invoke-Sqlcmd -HostName 'servername' -Dat ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException
+ FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Can you all help me in finding what is this due to ?

Do I have to indicate anywhere that this is a SQL Server auth ?

In the title you mentioned an instance. Though in you code example you don't mention an instance. Is there an instance? — Dbloch
– Dbloch, Commented Feb 12, 2016 at 15:33
If you pass username/password, then this is SQL Server auth. — marc_s
– marc_s, Commented Feb 12, 2016 at 15:34

Dbloch · Accepted Answer · 2016-02-12 15:37:16Z

0

So I'm guessing that you need to use the ServerInstance parameter instead of the HostName parameter.

Import-Module SQLPS

$query = 'select GETDATE() as date'

$op = invoke-Sqlcmd -ServerInstance 'servername' -Database 'DBName' -Username 'UN' -Password 'PWD' -Query "$query"

$op | Format-Table

answered Feb 12, 2016 at 15:37

Dbloch

2,4061 gold badge16 silver badges16 bronze badges

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

1 Comment

Niranjan Raguraman Over a year ago

Wow ! Thank you.. That worked Technically, what difference dose it make ?

Collectives™ on Stack Overflow

#sqlps - Problems connecting to the server to query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related