Powershell to read from database using ODBC DSN instead of connection string

Question

I know how to read a value from a database using connectionstring, i.e.

Establish a connection to the database for reading

$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Server=10.10.10.10;Initial Catalog=database_name;User Id=$username;Password=$password;"

$SQL = "..."

$conn.Open()

# Create and execute the SQL Query

$cmd = New-Object System.Data.SqlClient.SqlCommand($sql,$conn)

$count=0
do{
    try{
        $rdr = $cmd.ExecuteReader()



        while ($rdr.read()){
            $sql_output += ,@($rdr.GetValue(0), $rdr.GetValue(1))
            $count=$count + 1
        }
        $transactionComplete = $true

    }
    catch{
        $transactionComplete = $false
    }
}until ($transactionComplete)



# Close the database connection

$conn.Close()

How can I do the same with ODBC, i.e. Do I have a DSN (data source name) configured on the server?

Answer 1

According to https://www.connectionstrings.com/odbc-dsn/ you should use something like ...

DSN=myDsn;Uid=myUsername;Pwd=;

Perhaps just go with DSN=...if no accounts are required.

Answer 2

, ODBC User DSN, DSN. DSN.

$conn = new-object System.Data.Odbc.OdbcConnection
$conn.connectionstring = "DSN=DSNNAME"
$conn.open()

$cmd = New-object System.Data.Odbc.OdbcCommand($sqlCommand,$conn)
$dataset = New-Object System.Data.DataSet
(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet) | Out- Null
$conn.Close()