CODEWITHSUNDEEP
sql-serverpowershell
mqh7
mqh7

Reputation: 11

How to query SQL Server using PowerShell?

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)"

Upvotes: 0

Views: 6685

Answers (4)

Anders Larsen
Anders Larsen

Reputation: 1

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

**Integrated Security=True**

Upvotes: 0

pawel wujczyk
pawel wujczyk

Reputation: 521

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.

Select-SQLView

Upvotes: 1

vonPryz
vonPryz

Reputation: 24071

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;

Upvotes: 0

Aatif Akhter
Aatif Akhter

Reputation: 2206

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  
}

Upvotes: 0

Related Questions