SQL Connection From powershell

Question

I'm trying to connect my script to a Database. Previously I made it with:

psexec \\servername -accepteula sqlcmd.exe -U username -P password -S database -Q query

Now, I want to convert it into powershell code. I'm trying this:

Enter-PSSession servername
$SQLCred = New-Object System.Data.SqlClient.SqlCredential("username","password")
$Connection = New-Object System.Data.SqlClient.SqlConnection("Database",$SQLCred)
$Connection.Open()

But I always get this error:

Exception calling "Open" with "0" argument(s): "Login failed for user 'username'."

Of course I'm sure the credentials are correct. Which could be the difference between the two modes of connection?

David Brabant · Accepted Answer · 2017-08-24 11:19:49Z

1

Credentials are provided in the connection string:

$instance = "myInstance"
$userId = "myUserId"
$password = "myPassword"

$connectionString = "Data Source=$instance;Integrated Security=SSPI;Initial Catalog=master; User Id=$userId; Password=$password;"

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

If you have an existing credential object, you can use it like this:

$cred = New-Object System.Data.SqlClient.credential($userId, $password)
$connectionString = "Data Source=$Instance;Integrated Security=SSPI;Initial Catalog=master; User Id = $($cred.username); Password = $($cred.GetNetworkCredential().password);"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

answered Aug 24, 2017 at 11:19

David Brabant

44k16 gold badges93 silver badges119 bronze badges

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

Comments

Ron Clarke · Accepted Answer · 2019-09-26 19:25:50Z

0

    $SQLCredential = New-Object System.Data.SqlClient.SqlCredential($SQLUserName, $SQLPassword)
    $SQLServerConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$SQLInstanceName; Initial Catalog=MASTER")
    $SQLServerConnection.Credential = $SQLCredential
    try
    {
        $SQLServerConnection.Open()
    }

answered Sep 26, 2019 at 19:25

Ron Clarke

1

1 Comment

szmate1618 Over a year ago

I can see how this is different from the code in the question, but a short explanation why this code works, while the above one doesn't, would greatly benefit the uninitiated.

Collectives™ on Stack Overflow

SQL Connection From powershell

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related