0

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Niranjan Raguraman
  • 120
  • 1
  • 6
  • In the title you mentioned an instance. Though in you code example you don't mention an instance. Is there an instance? – Dbloch Feb 12 '16 at 15:33
  • If you pass username/password, then this **is** SQL Server auth. – marc_s Feb 12 '16 at 15:34

1 Answers1

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
Dbloch
  • 2,326
  • 1
  • 13
  • 15