How to query SQL Server using PowerShell?

Question

I found some code online but so far I can't get it to connect to my SQL Server database. I have followed this website to the letter: https://blogs.msdn.microsoft.com/walzenbach/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008/

I have allowed remote connections, added port 1433 to my firewall etc. I then run this code from PowerShell ISE:

    $dataSource = “\\SCCM12-01\MSSQLSERVER”
    $user = “MyID\OurDomain.org”
    $pwd = “MyPassword”
    $database = “CM1”
    $connectionString = “Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;”

    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    $connection.Open()

when I run this I get the following error.

Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)"

No, that does not work either. When I remove the \\ I get this error: Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)" At line:13 char:1 + $connection.Open() — mqh7
– mqh7, Commented Sep 20, 2016 at 17:02
This is not usually a problem but I see many smart quotes “” in the code you have. Consider replacing those with real quotes "and let us know if that changes anything? — Matt
– Matt, Commented Sep 20, 2016 at 18:30

pawel wujczyk · Accepted Answer · 2019-01-28 21:23:30Z

1

If you have simple query to do I recommend Select-SQLView powershell module. It allows to quickly select rows from table or view. It stores your database and server name so you do not have to provide this values every time.

As usual You can push results to table or to GridView.

If more complex queries are needed use SQLCommands module.

edited Jan 28, 2019 at 21:23

answered Jun 23, 2018 at 19:48

pawel wujczyk

5383 silver badges12 bronze badges

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

Comments

Aatif Akhter · Accepted Answer · 2016-09-20 17:31:26Z

0

Not sure why you are getting this error. You can refer to this link https://github.com/Tervis-Tumbler/InvokeSQL

You can try this one-

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables  
}

answered Sep 20, 2016 at 17:31

Aatif Akhter

2,2061 gold badge28 silver badges52 bronze badges

1 Comment

vonPryz Over a year ago

The OP is trying to use Sql Server authentication whilst your example is based on integrated Windows security. (Which is smarter, but not what's being asked.)

vonPryz · Accepted Answer · 2016-09-21 05:37:06Z

0

The error message actually explains what's wrong:

"SQL Network Interfaces, error: 25 - Connection string is not valid".

There is something amiss on the connection string. What exactly is hard to say as you have masked most of the details. Maybe the smart quotes wreck things? Maybe you got a quote character in the password? Anyway, it looks like you have invalid parameter for the user id:

$connectionString = “Server=$dataSource;uid=$user; pwd=$pwd;Database=$database;Integrated Security=False;”

Try User Id instead of uid like so,

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

answered Sep 21, 2016 at 5:37

vonPryz

24.3k10 gold badges58 silver badges69 bronze badges

Comments

Eric Aya · Accepted Answer · 2019-02-04 12:56:24Z

0

You properly just need to change 'Integrated Security' to 'true' when not using a db login

**Integrated Security=True**

edited Feb 4, 2019 at 12:56

Eric Aya

70.2k36 gold badges190 silver badges266 bronze badges

answered Feb 4, 2019 at 11:49

Anders Larsen

1

Collectives™ on Stack Overflow

How to query SQL Server using PowerShell?

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related