topic Encrypted PW in ODBC -or- Supplemental Host Parm? in SAS Programming

Encrypted PW in ODBC -or- Supplemental Host Parm?

jimbarbour — Tue, 10 Mar 2020 17:42:06 GMT

I am running SAS 9.4 M6 on a 64 bit Windows Server 2016 machine. I can use either GUI (DMS or EG).

I'm trying to connect to a Hive DB using ODBC. I can connect just fine -- so long as I use a plain text (unencrypted) password.

For example, the following Libname works just fine, but the value of &lPwd is just plain text meaning that things like SYMBOLGEN will print my password for all to see in the SAS log.

LIBNAME	HiveDB	ODBC	SCHEMA		=	ChartTarget
						NOPROMPT	=	"UID=jbarbour; PWD=&lPwd; HOST=DBMS0502; 
										DSN=OPSI_HIVE_STG1; SCHEMA=ChartTarget; 
										PORT=10000; AUTHMECH=3"
										;

I've tried using Proc PWENCODE, and can produce an encrypted password just fine, but no amount of trying seems to get it to work with my Libname using a NOPROMPT (see above).

I read in SAS Usage Note 31602 that it just can't be done -- but that was for SAS 9.1. Has this changed? Is there now a way to pass in an encrypted password when using a NOPROMPT?

--ALTERNATIVELY--

Is there a way to pass in the Host and Port outside of a NOPROMPT?

Coding the following:

LIBNAME	HiveDB	ODBC	SCHEMA		=	ChartTarget
						UID			=	jbarbour
						PWD			=	&lPwd
						HOST		=	DBMS0502
						DSN			=	OPSI_HIVE_STG1
						PORT		=	10000
						;

Yields the following error messages:

29         LIBNAME	HiveDB	ODBC	SCHEMA		=	ChartTarget
30         						UID			=	jbarbour
31         						PWD			=	&lPwd
32         						HOST		=	DBMS0502
                 ____
                 22
ERROR 22-7: Invalid option name HOST.

33         						DSN			=	OPSI_HIVE_STG1
34         						PORT		=	10000
                 ____
                 22
ERROR 22-7: Invalid option name PORT.

Thus forcing me to use NOPROMPT in order to pass the Host and Port to ODBC -- which works, but I am then forced to use a plain text password. 😞

So:

1. Is there a way now to use an encrypted password with a NOPROMPT on an ODBC Libname?

--or--

2. Is there a way to pass in Host and Port outside of a NOPROMPT so that I can avoid using NOPROMPT entirely?

And, yes, I am aware of the PROMPT, REQUIRED, and COMPLETE options, but we run a lot of automatically scheduled jobs at 0300 in the morning and other fun times -- times where interactive runs might be, well, somewhat inconvenient.

Thanks in advance,

Jim

Re: Encrypted PW in ODBC -or- Supplemental Host Parm?

ChrisNZ — Wed, 11 Mar 2020 04:16:11 GMT

It looks like only HOST and PORT make it choke.

Have you tried putting these in NOPROMPT and leaving the rest out? Just an idea.

Re: Encrypted PW in ODBC -or- Supplemental Host Parm?

jimbarbour — Wed, 11 Mar 2020 17:38:35 GMT

@ChrisNZ ,

An excellent idea, and just what I was looking for when I asked for a "supplemental host parm" (some means of passing in the host and port numbers).

Unfortunately, NOPROMPT is not that means. From what I can tell, when NOPROMPT is used, 100% of the connection is based on the contents of NOPROMPT and any parameters outside NOPROMPT are ignored. 😞

For example, coding the following:

LIBNAME	HiveDB	ODBC	SCHEMA		=	ChartTarget
						UID			=	jbarbour
						PWD			=	&lPwd
						DSN			=	OPSI_HIVE_STG1
						NOPROMPT	=	"HOST=DBMS0502; PORT=10000; AUTHMECH=3"
										;

Yields the following:

29         LIBNAME	HiveDB	ODBC	SCHEMA		=	ChartTarget
30         						UID			=	jbarbour
31         						PWD			=	&lPwd
32         						DSN			=	OPSI_HIVE_STG1
33         						NOPROMPT	=	XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
34         										;
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.

Despite the fact that the DSN is in fact coded.

One would think that SAS would try all NOPROMPT parameters first and then secondarily try and parameters coded outside the NOPROMPT, but no. Parameters coded outside the NOPROMPT are ignored.

Jim

Re: Encrypted PW in ODBC -or- Supplemental Host Parm?

SASKiwi — Wed, 11 Mar 2020 19:10:33 GMT

@jimbarbour - If you use NOPROMPT then you will also need to move the DSN into the connection string something like this:

NOPROMPT	=	"DSN=OPSI_HIVE_STG1;HOST=DBMS0502;PORT=10000;AUTHMECH=3"

In my experience NOPROMPT connection strings are really fiddly to get right. Spaces can cause problems for example.

Re: Encrypted PW in ODBC -or- Supplemental Host Parm?

ChrisNZ — Wed, 11 Mar 2020 22:02:36 GMT

> any parameters outside NOPROMPT are ignored

Mmm that's very annoying.

How about this clever idea?

https://communities.sas.com/t5/SAS-Programming/Redshift-ODBC-Connection-with-PROC-PWENCODE/td-p/600358

The problem dates back to V9.1 apparently!! 😞

https://support.sas.com/kb/31/602.html

Time to add SASWare ballot entry?

Re: Encrypted PW in ODBC -or- Supplemental Host Parm?

jimbarbour — Thu, 12 Mar 2020 00:33:18 GMT

@SASKiwi ,

Yes, you are quite correct. The DSN definitely has to be included in the NOPROMPT string, but so does password, and that password has to be in plain text (unencrypted). @ChrisNZ has an idea worth considering though. More on that in my response to his post.

Jim

Re: Encrypted PW in ODBC -or- Supplemental Host Parm?

jimbarbour — Thu, 12 Mar 2020 00:44:09 GMT

@ChrisNZ ,

Here, here! It's high time for a SAS ballot measure on this one!

That said, it's a rather clever idea -- to encrypt the entire NOPROMPT string. What's more, it works!

The following code:

%Enc_Pwd(Conn_Str, %STR(UID=jbarbour; PWD=&lPwd; HOST=DBMS0502; DSN=OPSI_HIVE_STG1; SCHEMA=ChartTarget; PORT=10000; AUTHMECH=3));
LIBNAME	HiveDB	ODBC	SCHEMA		=	ChartTarget
						NOPROMPT	=	"&Conn_Str"
										;

Yields the following in the log:

NOTE: Libref HIVEDB was successfully assigned as follows: 
      Engine:        ODBC 
      Physical Name: OPSI_HIVE_STG1

Unfortunately, if you look at the argument I'm passing to %Enc_Pwd, I still have to have a plain text password available, in this case via the lPwd macro variable. 😞

In our environment, we're not supposed to have plain text passwords in any form stored on disk.

Code could be set up in the autoexec.sas to create encrypted connection strings. A separate connection string would have to be created for each schema, and two per schema if there were, for example, a production and a stage version. Boy, what a hassle. 😞

Still, it could be done. I'll have to think about this one.

Ultimately, I think a SAS ballot measure is the right approach. Putting passwords in plain text is just no good.

I had to laugh at SAS's User Note: The solution is to code your password in plain text (for all to see). What a wonderful solution! Hallelujah! We're saved! I'd like to give a piece of my mind to the person who thought that was an acceptable workaround.

Jim

Re: Encrypted PW in ODBC -or- Supplemental Host Parm?

Patrick — Thu, 12 Mar 2020 02:59:11 GMT

If you need to connect to this server regularly then you could of course also define the server in SAS metadata.

If you connect with a generic user/password then you could also define an authentication domain and use this one in the libname instead of user/pw.

Re: Encrypted PW in ODBC -or- Supplemental Host Parm?

ChrisNZ — Thu, 12 Mar 2020 02:56:45 GMT

I forgot you had already referenced the UN.

> What a wonderful solution! Hallelujah! We're saved!

Lol indeed!

The only alternative I can think of is that the libname is defined in a macro, and that whoever needs to define it runs the macro.

The macro would temporary disable spying options of course.

> I think a SAS ballot measure is the right approach

It is. Whether is eventuates is another matter.

Re: Encrypted PW in ODBC -or- Supplemental Host Parm?

SASKiwi — Thu, 12 Mar 2020 06:15:07 GMT

@jimbarbour - I see you are on Windows. Using the ODBC Administrator can you set up a DSN and include a stored username / password? Wouldn't that mean you don't need to add one? Also avoiding the problem entirely with Windows Authentication works great for some databases like SQL Server but I'm guessing not yours...