topic Re: Using metadata server credential in SQL pass through code in SAS Enterprise Guide

Using metadata server credential in SQL pass through code

mpg — Wed, 22 Aug 2018 23:47:56 GMT

Hello,

Is it possible for Enterprise Guide to use a non-stored metadata server login credential as a database credential when running a SQL pass-through statement?

Context:

Our Enterprise Guide users connect to a metadata server with individual credentials, and the same individual credentials are used to connect to an Oracle database. We don't allow users to store their credentials in their profile or in their metadata user profile, but we encourage them to set the option in EG to persist their credential for the session. I'd like to avoid users entering and storing their credentials in program code.

I've tried the following code unsuccessfully:

PROC SQL;

	CONNECT TO oracle AS alias 
	(
		authdomain="DefaultAuth"
		path=dbPath
		preserve_comments
	); 
 	SELECT * FROM connection to alias
	(
		SELECT count(*) FROM schema.table
	);
	DISCONNECT FROM alias;

QUIT;

After the CONNECT TO statement, I get the following errors:

NOTE:  Credential could not be obtained from SAS metadata server.
WARNING: No login information was available for authdomain DefaultAuth.
ERROR: ORACLE connection error: ORA-12162: TNS:net service name is incorrectly specified.

Thanks in advance for any advice.

Re: Using metadata server credential in SQL pass through code

SASKiwi — Thu, 23 Aug 2018 01:24:52 GMT

Yes it is possible. I'm assuming you are using the IWA option (Integrated Windows Authentication) in your EG connection profile. Is that correct? If so then you want that to delegate your IWA credentials to your Oracle database connections.

What OS does your SAS servers run on? If you don't credential delegation already set up then that needs to be configured in your SAS application server Kerboros settings. This is a task for a SAS or IT administrator.

I'm not so familiar with how this works with Oracle but I know how it works with SQL Server. Hopefully someone with more Oracle experience can add to this post.

Re: Using metadata server credential in SQL pass through code

mpg — Thu, 23 Aug 2018 12:34:57 GMT

Our SAS servers are on Linux and use PAM and LDAP for authentication.