CODEWITHSUNDEEP
sql-serverdatabasepowershell
Louis 'LYRO' Dupont
Louis 'LYRO' Dupont

Reputation: 1112

How to connect to a SQL Server database using PowerShell

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 :

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

Upvotes: 0

Views: 8507

Answers (1)

warda54
warda54

Reputation: 26

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!

Upvotes: 1

Related Questions