To start my question, I would like to be clear that I am bound my systems security settings that are beyond my control <sigh>, I am using Enterprise Guide 8.1 and connecting to a Redshift Database via a DSN-less ODBC connnection. Such as: proc sql outobs = 10 ;
connect to odbc (noprompt= "Driver={Amazon Redshift (x64)}; Server=MyServer;Database=MyDb; UID=MyUser; PWD=MyPassword; Port=0000");
select *
from connection to odbc (
select distinct TableName
from pg_tables
where schemaname = 'public'
)
;
quit; This method works, but has the downside of displaying my password in clear text i the log.I have been experimenting with PROC PWENCODE and have successfully connected to ORACLE and Netezza databases using the results from PROC PWENCODE. I have not been able to connect to redshift using the PWENCODE value. I tried simply replacing my password in the above code with the PWENCODE string, which did not work. I tried enclosing in single quotes which did not work either. I tried a set of double quotes, which did not work either. I know using PWENCODE is NOT encryption, but our current system does not support integrated authentication, so I'm stuck using passwords. Has anyone been able to use PWENCODE with the Redshift ODBC driver without creating a DSN file? If so, how did you construct your connection string? Thanks!
... View more
