Connecting to SQL via PowerShell

Question

I´m trying to connect to SQL server via PowerShell. I tried using the code below,but I keep getting the same error. Can anyone explain why is this happening?

param
(   
    [Parameter(Mandatory)]  #servername
    [string]$SQLServer,

    # [Parameter(Mandatory)]  #database
    # [string]$SQLDBName,

    [Parameter(Mandatory)]  #user ID
    [string]$uid

)

$pwd = ConvertTo-SecureString "xxx"-AsPlainText -Force #heslo
$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 = 'StoredProcName'
$SqlCmd.Connection = $SqlConnection 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) 
$SqlConnection.Close()

The error looks like this:

Exception calling "Fill" with "1" argument(s): "Login failed for user 'aims'." At C:\Users\salema\Documents\SQL.ps1:24 char:1 + $SqlAdapter.Fill($DataSet) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

The error is pretty blatant. Does the user "aims" have proper SQL permissions to login/perform the actions? — AutomatedOrder

Peter Kay Peter Kay · Accepted Answer · 2020-01-24T21:04:16

You need to call the Open() method to open the SQL connection. Try adding

$pwd = ConvertTo-SecureString "xxx"-AsPlainText -Force #heslo
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID= $uid; Password= $pwd" 

# Open the SQL connection
$SqlConnection.Open()

Connecting to SQL via PowerShell

1 Answers