topic Re: Create server connection to a data warehouse in Administration and Deployment

Create server connection to a data warehouse

Riana — Tue, 23 Jan 2018 11:03:09 GMT

Hi,

There are two types of libnames for connection to Vertica:

libname mydblib vertica server="mysrv1.abc.com" port=5433 user=myusr1 password=mypwd1 database=mydb1 schema= abc;

And there are a connection in ODBC file in the system and I can run:

libname mydblib1 vertica dsn=mydsn1 user=myusr1 password=mypwd1 database=mydb1 schema=abc ;

Both libnames work fine when I run them in SAS EG and I can see datasets under mydblib and mydblib1.

But now I'm trying to create a permanent library in SAS MC under Data Library Manager and here is the steps that I followed:

1. Created a server under Server Manager ->'Vertica server' from the list -> selected 'Server Information' option from the next window.

2. Created a library with connection to the server that I created in the first step and named it MYDBLIB.

When I open SAS EG and connect to the SASAPP, MYDBLIB is under libraries but I can't see datasets.

Is there anything that I'm missing in step 1 or 2?

Re: Create server connection to a data warehouse

nhvdwalt — Tue, 23 Jan 2018 11:06:22 GMT

Try...

http://support.sas.com/kb/20/760.html

Re: Create server connection to a data warehouse

JuanS_OCS — Tue, 23 Jan 2018 11:08:36 GMT

Hello @Riana,

this one is an excellent and very common question. The problem is not the connection to Vertica, but how SAS metadata is allowing you to authenticate against Vertica.

The trick is Authentication Domains. In short:

- you would need to set an Authentication Domain (VerticaAuth, i.e)

- then assign this VerticaAuth to the connection under the Vertica Server in the Server Manager

- to create a SAS group (i.e. SAS Vertica database usergroup), add your usergroups to this usergroup, and on the Account tab, you would need to set the user AND password for the connection, and then use this VerticaAuth on the account)

- Finally, you can update your library on the metadata: you would need to select the connection again, to refresh it, and then the user authentication. You will see the account created in the SAS Vertica database usergroup.

Starting here, and after a good refresh of the Object Spawner / Application Server, you should be able to test/validate this connection.

Re: Create server connection to a data warehouse

Riana — Tue, 23 Jan 2018 12:54:22 GMT

Thanks @JuanS_OCS, @nhvdwalt for your reply.

The issue is resolved by following the steps in the link you sent and now I can see tables 🙂 Thank you.