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

Ranjith Reddy G 1 Reputation point
2022-11-03T08:58:20.267+00:00

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
Windows
Windows
A family of Microsoft operating systems that run across personal computers, tablets, laptops, phones, internet of things devices, self-contained mixed reality headsets, large collaboration screens, and other devices.
4,638 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Limitless Technology 43,996 Reputation points
    2022-11-08T09:09:04.893+00:00

    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–

    0 comments No comments