Architecting, installing and maintaining your SAS environment

Create server connection to a data warehouse

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 135
Accepted Solution

Create server connection to a data warehouse

[ Edited ]

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? 


Accepted Solutions
Solution
‎01-23-2018 07:53 AM
Super Contributor
Posts: 277

Re: Create server connection to a data warehouse


All Replies
Solution
‎01-23-2018 07:53 AM
Super Contributor
Posts: 277

Re: Create server connection to a data warehouse

Trusted Advisor
Posts: 1,571

Re: Create server connection to a data warehouse

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.

Frequent Contributor
Posts: 135

Re: Create server connection to a data warehouse

[ Edited ]
Posted in reply to JuanS_OCS

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 128 views
  • 3 likes
  • 3 in conversation