0

My network 10.10.10.0/24

The SQL server is in other network and other domain. I connect to it via vpn

IP address sql 192.168.1.1

I have access only sql without RDP

I try to connect to SQL server from Powershell.

invoke-dbaquery -SQLInstance 192.168.1.1 -sqlcredential 'sa' -query "select @@servername"

Work fine but appear a window so that input password. If we input the password then command work and output sql verssion.

How can do so that work without input the password?

testesxi
  • 41
  • 1
  • 7
  • `-SqlCredential` accepts a credentials object i.e. it's supports more than a username. Check out `New-DbaCredential` – gvee Feb 21 '19 at 09:07

2 Answers2

1

First, you need to export your credential to a file using the below cmdlets. (One-time process)

$Cred = Get-Credential -Credential "sa"
$Cred | Export-Clixml -Path "C:\Temp\SQLCred.cred"

And then you can import the credential file and use it in your script whenever you want.

$Cred = Import-Clixml -Path "C:\Temp\SQLCred.cred"

Invoke-DbaQuery -SQLInstance 192.168.1.1 -sqlcredential $Cred -query "select @@servername"

Note: The exported credential will be fully encrypted and can be used only in your machine

Akhilesh
  • 542
  • 4
  • 7
0

To use integrated security, simply omit the -sqlcredential part. Like so,

invoke-dbaquery -SQLInstance 192.168.1.1 -query "select @@servername"

In case you can't use integrated authentication (no domain trust or whatever), use PSCredential object to store and the credentials. Like so,

$cred = get-credential -credential 'sa'
invoke-dbaquery -SQLInstance 192.168.1.1 -query "select @@servername" -SqlCredential $cred

If you want to save the password in a file (mind the security issues though!), one can use Export-Clixml to store and retrieve the credential object.

vonPryz
  • 22,996
  • 7
  • 54
  • 65