topic Re: SAS/ACCESS to Sybase error when using 'Connect Using' in SAS Data Management

SAS/ACCESS to Sybase error when using 'Connect Using'

mpordon — Wed, 21 Aug 2019 11:16:37 GMT

In a set of code we have we define a libref to a Sybase server in the beginning and use the 'connect using' syntax to run explicit pass-through code on the server. Originally the libref was defined using ODBC as below along with an example of how we were running pass-through code:

libname Server odbc required="driver=Adaptive Server Enterprise;server=[SERVER];port=[PORT];database=[DATABASE];logonid=[ID];password=[PASSWORD];charset=iso_1;" schema=dbo;
proc sql;
	Connect using Server;
	Select
		*
	From
		Connection to Sever
		(
			Select
				8
			From
				SomeTable
		);	
quit;

Recently we were asked to start using proc pwencode to encode our passwords so that they did not need to be stored as plaintext. To my knowledge the SAS encoded passwords do not work in the 'required' parameter so we switched the libref declaration to:

libname Server sybase USER=[ID] PASSWORD="[PASSWORD]" SERVER=[SERVER] DATABASE=[DATABASE] schema=dbo;
proc sql;
	Connect using Server;
quit;

The libname statement works as expected and I can navigate to tables on the server via the explorer. However, the attempt to connect using the libname via proc sql now generates an error:

The odd thing is that using 'connect to' and redefining the connection works:

proc sql noprint;
  connect to SYBASE(USER=[ID]
                    PASSWORD=[PASSWORD]
                    SERVER=[SERVER]
                    DATABASE=[DATABASE]);
quit;

Has anyone experienced this sort of issue with SAS/Access for Sybase before? This is all running on SAS 9.4 M2.

Re: SAS/ACCESS to Sybase error when using 'Connect Using'

JBailey — Wed, 21 Aug 2019 14:26:54 GMT

Hi @mpordon

This is a guess; I haven't tried this, but change the Libref Server to a different name. It looks like something could be getting discombobulated because of a reserved keyword.

libname myServ sybase USER=[ID] PASSWORD="[PASSWORD]" 
SERVER=[SERVER] 
DATABASE=[DATABASE] 
schema=dbo;

proc sql;
	Connect using myServ;
quit;

Best wishes,
Jeff

Re: SAS/ACCESS to Sybase error when using 'Connect Using'

mpordon — Thu, 22 Aug 2019 12:47:41 GMT

Unfortunately that is not the problem. I had changed the name of the libref before posting the code just to make it as generic as possible. Normally it is the name of the actual server that is not a reserved word.

Re: SAS/ACCESS to Sybase error when using 'Connect Using'

JBailey — Thu, 22 Aug 2019 15:58:48 GMT

Hi @mpordon

I think your Sybase interfaces file is pointing to an incorrect server (or there is some other setup problem). There may be missing environment variables. You can try connecting with isql and it will probably give you the same error message.

Here is a snippet from an interfaces file:

MYSERVER
        master tcp ether myserver 5000
        query tcp ether myserver 5000

Connecting with isql is a good test because it takes SAS out of the picture.

Link to the SAP doc describing isql

Make sure the environment variables are correct.

SAS 9.4 Configuration Guide for Linux

Hope this helps,
Jeff