topic Re: Connect to metadata server with options in Administration and Deployment

Connect to metadata server with options

kimmygzc — Mon, 29 Nov 2021 06:46:54 GMT

I'm using SAS EG 7.1 with sasfoundation 9.3; I connect to a greenplum server via a metadata server(log in a remote SVC in connection setup with user&pwd). And I could commit SQLs to GreenPlum server with libname statement, or pass-through machanisim. It works fine, and for some reason, I need to run without EG, just on sas.exe. So the trouble is, when I tried to connect to setup metadata server connection with [options], under this instruction: https://support.sas.com/kb/38/204.html The log reported error: ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ERROR: Error in the LIBNAME statement. I believe it hasn't made the right connection to GreenPlum yet, Why would sas bother with MS ODBS? What should I do to adjust this? It's just a tricky trouble to shoot, any reply would be greatly appreciated! Kim Tse

Re: Connect to metadata server with options

SASKiwi — Mon, 29 Nov 2021 07:05:53 GMT

Please provide the SAS log from the program successfully connecting to Greenplum and also the SAS log from your unsuccessful attempt. Ensure you include both the source statements and any SAS notes and errors.

Re: Connect to metadata server with options

kimmygzc — Mon, 29 Nov 2021 07:15:22 GMT

I'd love to, but the SAS is on Cloud environment...

Basically, Connect to greenplum server with [server, port, databse, authdomain] when use EG connection. I guess EG works with the authomain option, to log in the Greenplum server.

I need to get the same credential without EG before actually connect to Greenplum, I tried options [matauser, metapass, metaport, metaprotocol(bridge), metaserver], but it doesn't work...

Re: Connect to metadata server with options

gwootton — Mon, 29 Nov 2021 15:32:40 GMT

Are you running sas.exe on the compute server where Enterprise Guide runs SAS? The error seems to indicate you are trying to call an ODBC data source that is not configured, which could occur if you are running SAS somewhere else.

Re: Connect to metadata server with options

SASKiwi — Mon, 29 Nov 2021 19:23:21 GMT

Just copy and paste from your EG SAS log for showing a successful SAS log.

To ensure you get the same data libraries defined in SAS batch jobs as you get in SAS EG sessions you just need to pre-assign the libraries. This is an advanced option you can set on the data library in SAS Management Console.

Re: Connect to metadata server with options

kimmygzc — Tue, 30 Nov 2021 06:27:31 GMT

Thanks! I have SAS EG 7.1 and sas 9.3 installed, I'm not sure if the EG works standalone or through another computing node, where do I check this?

I have EG connections configured [remote machine], I think this offers access to the SVC(switched virtual circuit), where the GP user id and password is stored. And via the SVC I can finally commit my SQLs to the Greenplum Server.

I'm trying to plug in the SVC in sas.exe without EG's help, I guess you're right about ODBC, is there anything I might have ignored?

Re: Connect to metadata server with options

kimmygzc — Tue, 30 Nov 2021 06:36:52 GMT

Thanks Sir, But I can't copy anything down from the internal network...

I scripted proc sql to select from sashlep.cars and it works out fine. But the other proc sql connected to GP just won't make it, I tried both [libname] statement and [connect to greenplum], which I normally build connections to the GP server in EG, but it failed in only sas.exe.

Are there more information about the "pre-assign" and about "SAS Management Console" you mentioned?

Thanks for your time, Sir.

Re: Connect to metadata server with options

kimmygzc — Tue, 30 Nov 2021 06:53:51 GMT

The code in EG goes like this:

[libname]statement:
libname CON greenplm server= port= database= authdomain=;
pro sql outobs=30;
select *
from CON.table_nm
run;

or pass-through:
proc sql;
connect to greenplm as gpconn(server= port= database= authdomain=);
select * from connection to gpconn(
select *
from ap1.table_nm
limit 30
);
disconnect from gpconn;
run;

these would work in EG, but failed in sas.exe. the difference is EG connect to SVC with user&password every time it initiates.

Re: Connect to metadata server with options

gwootton — Tue, 30 Nov 2021 14:10:00 GMT

This SAS Note describes the failure meaning you do not have the driver installed:

Usage Note 44774: System Requirements for SAS/ACCESS® 9.3 Interface to Greenplum in Windows environments
https://support.sas.com/kb/44/774.html

Error:
libname myconn greenplm server="192.168.123.45" port=5432 database=test user=gpuser password=XXXXXXX schema=public;

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

Cause: The "SAS ACCESS to Greenplum" driver has not been installed. Be sure to follow the directions in the readme.txt file, including making the appropriate manual entries in the Windows registry.

Note: The zip files containing the drivers and readme.txt can be found here:

For 32-bit SAS: !SASROOT\access\sasmisc\w32gplm60.zip
For 64-bit SAS: !SASROOT\access\sasmisc\wx6gplm60.zip

Re: Connect to metadata server with options

Sajid01 — Sun, 05 Dec 2021 13:39:56 GMT

Hello @kimmygzc

From your posts what I understand is that you are having SAS installed on a remote servers and you are running the code in EG. You are able to to successfully connect to the greenplum database. However you are not able to connect in the batch mode.

Typically when database access is through the metadata server and the code is executed in batch mode one uses the authomain= option. ( Look here for the details. https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0aiq25zc8u8u6n1i81my0a24sd3.htm.

There are more resource available on the google. )

Make sure that the batch code is executing on the compute server with database clients installed and configured. A goodplace in your case would be the compute server where you have executed your code through EG.

In organizations with good security practices the developers do not have access to database credentials. Authdomain approach is almost invariably used.

As a rule if one has to connect to remote databases (databases not installed on the same computer) then database clients/drivers need to be installed. (There are exceptions but in the SAS practice no).
The implementation of SAS and best practices differ from organization to organization. SAS Administrators/ Seniors in the organaztion are often very helpful and the first persons to seek help from.