How can connect to sql server from Powershell without password?

Question

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?

-SqlCredential accepts a credentials object i.e. it's supports more than a username. Check out New-DbaCredential — gvee
– gvee, Commented Feb 21, 2019 at 9:07

Akhilesh · Accepted Answer · 2019-02-21 09:40:18Z

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

answered Feb 21, 2019 at 9:40

Akhilesh

5624 silver badges8 bronze badges

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

Comments

vonPryz · Accepted Answer · 2019-02-21 09:06:40Z

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.

answered Feb 21, 2019 at 8:39

vonPryz

24.3k10 gold badges58 silver badges69 bronze badges

Thank you for your help