How to connect to a SQL Server database using PowerShell

Question

I am currently working on a PowerShell script that is supposed to connect every morning to my company database, checking for the pending Software Approval requests in SCCM, and sending emails to concerned people which allow them to accept or refuse the request with a link which executes a PowerShell script.

My question is currently about of the first step, which is connecting to the database.

I created a SQL Server account, with which I am able to connect to Microsoft SQL Server Management Studio on the server, but which doesn't connect with my script...

I found this code on this website, and people are apparently saying that it is working, But I always get a timeout error when I run it :

Function GetPendingRequests {
param (
    [string]$SQLServer,
    [string]$SQLDBName,
    [string]$uid,
    [string]$pwd
)
$SqlQuery = "select * from [Database].[dbo].[UserApplicationRequests]"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User Id = $uid; Password = $pwd;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

return $DataSet.Tables[0] 
}

By the way, the [Database] contains the real database name, and variables are correct... I already checked the following points :

The server accepts Windows and SQL Server authentication
The account can access the database in Microsoft SQL Server Management Studio, and read the data the way I want
The computer retrieves the server IP successfully

Does anyone have an idea of what I did wrong, and why this PowerShell function can't connect to the database?

Can you run the query using SSMS from the client machine, using the SQL account? — David Rushton
– David Rushton, Commented Jul 25, 2017 at 12:21
I can't add any package to powershell, I would like to just do it with 100% PowerShell. — Louis 'LYRO' Dupont
– Louis 'LYRO' Dupont, Commented Jul 25, 2017 at 12:46

muneebShabbir · Accepted Answer · 2017-07-25 12:54:06Z

1

Are you getting any sort of error when you run the script?

It's possible that you need to specify the server that the database is on (especially if it's on a remote machine)

For example:

$SqlConnection.ConnectionString = "Server = localhost\MySqlServerInstance; Database = $SQLDBName; User Id = $uid; Password = $pwd;

This can of course be done as a parameter, this is just an example.

Take at look at https://www.connectionstrings.com/sql-server/ and https://msdn.microsoft.com/en-us/library/jj653752(v=vs.110).aspx for more information on connection strings.

This is just a guess since we don't have a lot of information on what is actually happening when you run the script (nor how you are invoking it), so feel free to respond with additional details and we'll go from there!

edited Jul 25, 2017 at 12:54

muneebShabbir

2,5384 gold badges30 silver badges49 bronze badges

answered Jul 25, 2017 at 11:09

warda54

261 bronze badge

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

1 Comment

Louis 'LYRO' Dupont Over a year ago

I'll try to be as explicit as possible : I run the script in the Powershell console, on a client computer. I try to login with the SQL Account I created on the SQL Server directly, and it can login when I use directly the Microsoft SQL Server Manager. But when I run this script, I get a timeout error, saying that the server has not send any response in arround 14 seconds...

Collectives™ on Stack Overflow

How to connect to a SQL Server database using PowerShell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related