DATA Step, Macro, Functions and more

Problem with SYMGET in the CONNECT Statement

Reply
Contributor
Posts: 36

Problem with SYMGET in the CONNECT Statement

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.

Attachment
Attachment
Super User
Super User
Posts: 6,495

Re: Problem with SYMGET in the CONNECT Statement

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.

Contributor
Posts: 36

Re: Problem with SYMGET in the CONNECT Statement

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.

Super User
Super User
Posts: 6,495

Re: Problem with SYMGET in the CONNECT Statement

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.

Contributor
Posts: 36

Re: Problem with SYMGET in the CONNECT Statement

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.

Super User
Super User
Posts: 6,495

Re: Problem with SYMGET in the CONNECT Statement

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?

Contributor
Posts: 36

Re: Problem with SYMGET in the CONNECT Statement

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/TalkSmiley Frustratedecret_Sequel:_Keeping_Your_Password_Away_from_the_LOG   and i thought it would work.

Thank you.

Super User
Posts: 3,101

Problem with SYMGET in the CONNECT Statement

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.

Trusted Advisor
Posts: 1,300

Problem with SYMGET in the CONNECT Statement

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.

Contributor
Posts: 36

Problem with SYMGET in the CONNECT Statement

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.

Super User
Super User
Posts: 6,495

Re: Problem with SYMGET in the CONNECT Statement

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.

Super User
Posts: 3,101

Problem with SYMGET in the CONNECT Statement

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.

New Contributor
Posts: 3

Re: Problem with SYMGET in the CONNECT Statement

This paper describes about masking your password.

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

Trusted Advisor
Posts: 1,300

Problem with SYMGET in the CONNECT Statement

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;

Contributor
Posts: 36

Re: Problem with SYMGET in the CONNECT Statement

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.

Ask a Question
Discussion stats
  • 19 replies
  • 1956 views
  • 6 likes
  • 6 in conversation