CODEWITHSUNDEEP
sql-serverpowershellsqlcmd
Alfredo Liardo
Alfredo Liardo

Reputation: 115

SQL Connection From powershell

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?

Upvotes: 2

Views: 13751

Answers (2)

Ron Clarke
Ron Clarke

Reputation: 1

    $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()
    }

Upvotes: 0

David Brabant
David Brabant

Reputation: 43499

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

Upvotes: 1

Related Questions