SAS/ACCESS ODBC: Connection String truncation with PROC SQL Pass Pass Through "Connect Using"?

jchiang — Tue, 01 Nov 2022 18:04:55 GMT

I'm noticing odd behavior with ODBC in Linux64 operating system where CONNECT USING and CONNECT TO differ on DSN-less (NOPROMPT) connections. Cannot find relevant documentation or if this is a bug. Does anyone know if there is a solution to allow for CONNECT USING to function like CONNECT TO? We hope to use CONNECT USING so that the LIBREF can be defined only once in code, rather than repeatedly calling a NOPROMPT.

I'm on SAS9.4M6 In the below statements I have masked connection details but preserved the length of the strings by putting in an "×" where a character was.

The following statement works and the ODBC Trace exits from SQLDriverConnect with SQL_SUCCESS, connection string length of 366.

OPTIONS
    SASTRACE='d,,,'
    SASTRACELOC=saslog
    NOSTSUFFIX;

libname sf odbc
noprompt="DRIVER={/××××××××××××××××××××××××××××/libSnowflake.so};
AUTHENTICATOR=SNOWFLAKE_JWT;
SERVER=××××××××××××××××××××××××××××××snowflakecomputing.com;
UID=×××××××××××××××××××××××××;
PRIV_KEY_FILE=×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××;
PRIV_KEY_FILE_PWD=;
ROLE=×××××××××××××××××××××××××××××××××;
WAREHOUSE=×××××××××××××××××××××××;
DATABASE=×××××××××××××××××;"
readbuff=999;

proc sql;
    connect using sf;
    disconnect from sf;
quit;
%PUT &=SQLXRC.;
%PUT &=SQLXMSG.;
libname sf clear;

OPTIONS
    SASTRACE=OFF;

However. If I edit the connection string and add parameters, or even simply change the location of the private key to a single character longer, the libref assignment succeeds but the CONNECT USING fails. Also, if I keep the same 366 length NOPROMPT as above but edit READBUFF=1000 (one additional digit), I get the same error. Interestingly, adding SCHEMA= option to the libref does not produce this error.

libname sf odbc
noprompt="DRIVER={/××××××××××××××××××××××××××××/libSnowflake.so};
AUTHENTICATOR=SNOWFLAKE_JWT;
SERVER=××××××××××××××××××××××××××××××snowflakecomputing.com;
UID=×××××××××××××××××××××××××;
PRIV_KEY_FILE=××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××;
PRIV_KEY_FILE_PWD=;
ROLE=×××××××××××××××××××××××××××××××××;
WAREHOUSE=×××××××××××××××××××××××;
DATABASE=×××××××××××××××××;"
readbuff=999;

This edit (1 character longer NOPROMPT) produces an error where SQLDriverConnect read a "0 length" NOPROMPT and PROC SQL returns:

%PUT &=SQLXRC.;
 SQLXRC=IM002
%PUT &=SQLXMSG.;
 SQLXMSG=[unixODBC][Driver Manager]Data source name not found, and no default driver specified

However, the LIBNAME statement it is USING executes without issue, with NOPROMPT having a 367 length reported in trace.

Oddly, the length is completely irrelevant for all scenarios tested when using CONNECT TO. All scenarios run successfully in CONNECT TO without issue, including with READBUFF up to 32767.

Re: SAS/ACCESS ODBC: Connection String truncation with PROC SQL Pass Pass Through "Connect Usin

SASKiwi — Tue, 01 Nov 2022 19:19:55 GMT

I suggest you open a Tech Support track on this. I use CONNECT USING a lot and never had any problems but I'm not accessing Snowflake.

Not sure if this old SAS note is still relevant or not: https://support.sas.com/kb/50/855.html

Re: SAS/ACCESS ODBC: Connection String truncation with PROC SQL Pass Pass Through "Connect Usin

jchiang — Tue, 01 Nov 2022 19:23:29 GMT

Thanks, I think I'll have to go down the technical support track...

Thanks for linking that issue, I don't think it applies anymore in 9.4M6 seeing that simply changing the READBUFF can trigger issue using CONNECT USING.

topic Re: SAS/ACCESS ODBC: Connection String truncation with PROC SQL Pass Pass Through "Connect Usin in SAS Procedures

SAS/ACCESS ODBC: Connection String truncation with PROC SQL Pass Pass Through "Connect Using"?

Re: SAS/ACCESS ODBC: Connection String truncation with PROC SQL Pass Pass Through "Connect Usin

Re: SAS/ACCESS ODBC: Connection String truncation with PROC SQL Pass Pass Through "Connect Usin