Not able to create Redshift DSN connection through the Add-OdbcDsn cmdlet

Question

Hello all,

When I'm trying to create the DSN connection for Redshift facing the below error, tried many articles, but didn't get proper help, Can you please someone help to close my issue?

Articles followed :

https://community.spiceworks.com/topic/2107667-powershell-odbc-connection-with-username-password https://learn.microsoft.com/en-us/answers/questions/48993/add-odbcdsn-timeout-with-amazon-redshift-x64-drive.html https://support.jda.com/s/article/How-to-save-password-for-an-ODBC-connection-to-suppress-the-constant-prompting?language=en_US https://social.msdn.microsoft.com/Forums/en-US/27da981f-2f59-4848-9e7d-eb5cde4f5fd2/creating-odbc-dsn-for-sql-native-client-fails-for-notintegrated-authentication?forum=adodotnetdataproviders https://community.progress.com/s/article/21227

Below is the Query which I tried :

Add-OdbcDsn -DriverName "Amazon Redshift (x64)" -DsnType "System" -Platform "64-bit" -Name "myName" -SetPropertyValue @("Server=redshiftserver.eu-west-1.redshift.amazonaws.com", "Database=dev", "Port=5439", "Trusted_Connection=Yes", "UID=Hello", "EPWD=world" )

Add-OdbcDsn : Attempt to set the {UID or PWD} key of a DSN. These keys should not be stored in the registry for

security reason. Provide the credential information at runtime via SQLDriverConnect, SQLConnect or SQLBrowseConnect.

At line:1 char:1

• Add-OdbcDsn -DriverName "Amazon Redshift (x64)" -DsnType "System" -Pl ...
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • CategoryInfo : InvalidArgument: (MSFT_OdbcDsnTask:Root/Microsoft/...SFT_OdbcDsnTask) [Add-OdbcDsn], Cim
  Exception
  • FullyQualifiedErrorId : MI RESULT 4,Add-OdbcDsn

Answer 1

Hello there,

This is the source code for the function Test-ODBCConnection which tests if the ODBC connection can connect to the database.

Function Test-ODBCConnection {
[CmdletBinding()]
param(
[Parameter(Mandatory=$True,
HelpMessage="DSN name of ODBC connection")]
[string]$DSN
)
$conn = new-object system.data.odbc.odbcconnection
$conn.connectionstring = "(DSN=$DSN)"

try {  
    if (($conn.open()) -eq $true) {  
        $conn.Close()  
        $true  
    }  
    else {  
        $false  
    }  
} catch {  
    Write-Host $_.Exception.Message  
    $false  
}  

}

Meanwhile, this seems to be a topic that is much related to AWS services, and posting it on their support page might help you in getting this sorted.

--------------------------------------------------------------------------------------------------------------------------------------------------

--If the reply is helpful, please Upvote and Accept it as an answer–