Proper syntax to access a Redshift server in libname statement?

[RobertWF1]

The data management people in my company (who are not SAS users) have sent me information to connect to one of our Redshift servers:

User ID: user

Password: pwd

Database Server: svr123

Database Port: 1234

Database: db123

Schema: data_schema_123

My libname statement is as follows:

libname newlib redshift 
user="user" 
password="pwd" 
READBUFF=10000 SUB_CHAR=QUESTIONMARK 
schema="data_schema_123"
database="db123"
server="svr123"
port=1234;

I'm seeing the following error message after executing:

 ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified
 ERROR: Error in the LIBNAME statement.

Is there anything obviously wrong with my syntax or is this more of an IT issue?

[sbxkoenk]

@IlBoon ... can you help?

Koen

[SASKiwi]

Has your SAS installation ever been successfully used with Redshift before or is this the first time such a database connection has been tried? Does your SAS run on a PC or a server? If it is on a PC, for example, then you should check to see if you have a Redshift ODBC driver installed via the Windows ODBC Administrator tool. 

[RobertWF1]

My company is running SAS Studio in a web browser on a remote desktop.

Not sure about the Redshift ODBC driver - I'll check or ask the IT folks. 

I've never had trouble connecting before with SAS, but this is the first time I've accessed data via AWS Redshift.

Thanks for your help!

[RobertWF1]

Update:
I do see Amazon Redshift ODBC Driver folders (32 and 64-bit) in Windows>Start Menu>Programs.

[SASKiwi]

SAS Studio typically uses a remote SAS server. You can find out what that is by running this:

%put &=SYSHOSTNAME;

To access Redshift the 64-bit Redshift ODBC driver would need to be installed on the remote SAS server. Typically this is a job for your SAS administrator. If you have one I suggest you check with them. I suspect your LIBNAME is missing a DSN setting which identifies both the ODBC driver being used and the Redshift server to connect to.

EDIT: Try this  DSN = "svr123" 

[ChrisNZ]

@SASKiwi

Why would one use SAS/ACCESS Interface to Amazon Redshift if it relies on ODBC? Why not use SAS/ACCESS Interface to ODBC?

Amazon provides  ODBC drivers for linux and Windows.