Problem with SYMGET in the CONNECT Statement

[hari1109]

Hi All,

I have an issue with Symget fucntion  i am using the following code to mask my password from displaying in log. I have attached both code and error log to this post. please suggest a way out of this problem. Thank you very much.

[Tom]

Remove the quotes from around the name of the symbol.  When using %SYSFUNC() (and macro functions in general) you do not need to put quotes around strings. To the macro processor everything is a string.

[hari1109]

Hi Tom,

Thank you for the reply , i tried without quotes as you mentioned but it is not working and i am getting the same error as i have mentioned in the error_log file. quoted were removed for all strings and am geeting the same error.

[Tom]

You do not need to use SYMGET.  Just reference the macro variable. 

Replace:

WHERE uid eq %sysfunc(symget('abc_username')) AND pwd eq %sysfunc(symget('abc_password'))

with

WHERE uid eq "&abc_username" AND pwd eq "&abc_password"

Note the quotes so that the compiler will see that the terms on the right hand side of the EQ operator are strings and not variable names or constants.

Symget is only useful when running in a data step and you want to get the current value for a macro variable that you might have updated since the data step was compiled by using a CALL SYMPUT statement.

[hari1109]

Hi Tom,

Thank you for the suggestions yeah if i mean if i  replace the where statement as you mentioned the macro variables will get resolved but when you use some options like symbolgen mprint and mlogic in your code these password values will get exspodes in the sas log and that is i why i am trying to mask those variables using SYMGET function. i came across this in the paper mentioned below

http://support.sas.com/resources/papers/proceedings09/013-2009.pdf

Thank you.

[Tom]

The example in the program is calling the symget function directly in the select statement of the SQL. Does not need to wrap it in a SYSFUNC call.  Did you try just removing the %SYSFUNC() wrapper around the SYMGET function calls so that it looks like in the paper you found?

[hari1109]

Yes Tom i tried without the %SYSFUNC fucntion but it didn't work. The reason why i used is i looked into this link

http://www.sascommunity.org/wiki/Talk:Secret_Sequel:_Keeping_Your_Password_Away_from_the_LOG   and i thought it would work.

Thank you.

[SASKiwi]

A better way to hide passwords is to encrypt them like so:

proc pwencode in='My password';

run ;

This produces an encrypted string like this: {sas001}TXkgcGFzc3dvcmQ=

Which you then put into your macro variable or supply directly in a database connect statement:

connect to oracle (user = myuser password = '{sas001}TXkgcGFzc3dvcmQ=' path='Mypath' );

The advantage is that is doesn't matter if the password appears masked or not, it can't be easily unencrypted.

[FriedEgg]

This suggesttion will also work well, however if you are going to use it inside a select statement to dbs like oracle you will need another one where the in="'My password'"  Also I am pretty sure that certain conditions of the sastrace option will still show the unmasked password.  This is just an assumption though.

[hari1109]

Hi SASkiwi,

This could be good for one but in my case there are mutiple people who have to connect to the database using pass through facility. That is why am looking for other methods to hide the password from not appearing in the sas log. Thank you.

[Tom]

We tried using this, but it does not really solve the problem.

Anyone with access to SAS can use that "encrypted" password to access the database.

[SASKiwi]

Agreed Tom, but it does block any use of that password in other non-SAS database tools like TOAD, Oracle client etc. By using encrypted passwords in combination with other security measures, as discussed, you can make it hard enough for all but the most expert of users to crack.

[vrmkool]

This paper describes about masking your password.

support.sas.com/resources/papers/proceedings09/013-2009.pdf

[FriedEgg]

What are you connecting to when running the query?  For example, if you are connecting to oracle the query would not work becuase the character strings you are trying to pass the where clause are not encased in single quotes.  Try doing this:

%let user=user;

%let pass=pass;

data _null_;

  call symput('user',"'"||"&user"||"'");

call symput('pass',"'"||"&pass"||"'");

run;

proc sql;

  connect to oracle user=&user pass=&pass path=mypath;

create table want as select * from connection to oracle

  ( select *

      from mytable

     where uid=&user and pwd=&pass );

disconnect from oracle;

quit;

[hari1109]

Hi FriedEgg,

Thank you I can use what you have told but i am trying to hide the password away from the SAS log. For example if is use the above code with options such as Symbolgen, mprint and mlogic then what ever the password i am using in the connect statement will get exsposed. So to avoid that i am using Symget fuction which i have seen in a document

http://support.sas.com/resources/papers/proceedings09/013-2009.pdf

THank you.