ODBC password protection in SAS

Question

We want to remove the hard-coded passwords from the ODBC connection strings in our SAS code, and also to prevent the appearance of any of the passwords in the SAS log files.

There seem to be a lot of recommendations discussing how to do this, but I either find problems with them or can't make them work.

A user request each time for PW is not a viable alternative. Also, storing a password in a macro variable is an acceptable approach if you have a way to suppress it from printing to log with MACROGEN and SYMBOLGEN enabled.

ATTEMPT 1 - ENCODING ( link to white paper here )

proc pwencode in='mypassword' method=sasenc; run; 

gives:

 {sasenc}ACFD24061BF77D7D5362EE7C2D00D08B 

If I replace my plaintext password with an encoded value in my code, then the POSHRORE ODBC instruction will work fine.

 proc sql noprint; connect to odbc as remote (datasrc=cmg_report user=myuser password='{sasenc}68B279564BD2695538CDCDB301E8A357563480B0'); create table sqlo as select * from connection to remote ( select top 1 * from application ) ; disconnect from remote; quit; 

And the log correctly masks the values ​​with XXXXXXX.

 961 proc sql noprint; 962 connect to odbc as remote (datasrc=cmg_report user=&user_cmg password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX); 963 create table sqlo as 964 select * 965 from connection to remote 966 ( 967 select top 1 * from application 968 ) 969 ; 971 quit; NOTE: Table WORK.SQLO created, with 1 rows and 29 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.34 seconds cpu time 0.01 seconds 

The problem with the above approach is that if someone has access to the code, he can log in using an encrypted password without knowing the plain text password. Therefore, while it hides the actual password, it does not provide security. Does this seem silly to me, or am I missing something? EDIT:. This provides some security if your ODBC password is used elsewhere, but more on that.

CONFIRMATION 2 - USE SYMGET ( link to white paper here )

The problem is that I just cannot get the described technique in SAS. I am running SAS 9.2 on XP, trying to connect to a SQL Server database.

 %let my_password = password; proc sql noprint; connect to odbc (dsn=cmg_report uid=myuser pwd=symget('my_password')); create table sqlo as select * from connection to remote ( select top 1 * from application ) ; quit; 

I get the following message that the login failed:

 1034 proc sql noprint; 1035 connect to odbc (dsn=cmg_report uid=myuser pwd=XXXXXX('my_password')); ERROR: CLI error trying to establish connection: [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'myuser'. 

It looks like he is trying to use "symget" as the actual password (since it was masked in the log). There are several answers to this text that says to wrap a character in a% sysfunc call, but the symget () function is one of the few functions that SAS does not allow in a% sysfunc call, so I don’t see how this is possible.

Any other tips / suggestions / ideas would be highly appreciated.

thanks

EDIT: It would be especially nice if there was a method that worked with options symbolgen macrogen enabled.

Answer 1

Rob, we encountered a similar problem and proposed another method that allows all our team members to run the same program without using our id / passwords in the programs. This requires that each team member has a secure text file (without access rights other than the owner) that SAS can access.

Here is an example of the contents of an ID / PW file:

 machine odbc login XX_odbc_id_XX password XXodbc_pw_XX machine oracle login XX_oracle_id_XX password XX_oracle_pw_XX 

We work on a UNIX server, so we store our separate id / pw files locked in our home directory so that no one else can access it, in this case it is called ".netrc". The macros at the end of this stream must be saved somewhere, then the program will look like this:

 %let id_pw_text_file = ~/.netrc; %ODBC_Acct; proc sql; %ODBC_Connect create table sqlo as select * from connection to odbc ( /* [ Insert ODBC query here ] */ ); %ODBC_Disconnect quit; run; 

I tried to reconsider macros to work in your environment and remove a lot of code specific to our systems, but obviously I couldn’t check it to make sure it works. Let me know if you have a problem and I will try to help fix it. Hope this helps.

 /********************************************************************* * Name: ODBC_Acct * * Desc: Set global macro vars containing a users ODBC username * * and password. Retrieves this information from a users * * specific ID/PW file. * *********************************************************************/ %macro ODBC_Acct( mprint ); %local __mprint __symbolgen __mlogic; %if ( %length( &mprint ) = 0 ) %then %let mprint = NO; %if ( %upcase( &mprint ) = NO ) %then %do; %let __mprint = %sysfunc( getoption( mprint )); %let __symbolgen = %sysfunc( getoption( symbolgen )); %let __mlogic = %sysfunc( getoption( mlogic )); options nomprint nosymbolgen nomlogic; %end; %global odbc_user odbc_pw; %Get_ID_PW( &id_pw_text_file , odbc , odbc_user , odbc_pw ) %if ( %upcase(&__mprint) ne NOMPRINT ) %then %do; options &__mprint &__symbolgen &__mlogic; %end; %mend; /********************************************************************* * Name: ODBC_Connect, ODBC_Disconnect * * Desc: Returns SAS/Access connect or disconnect statements * * for accessing ODBC. * *********************************************************************/ %macro ODBC_Connect( mprint=no ); %local __mprint __symbolgen; %if ( %upcase(&mprint) = NO ) %then %do; %let __mprint = %sysfunc( getoption( mprint )); %let __symbolgen = %sysfunc( getoption( symbolgen )); options nomprint nosymbolgen; %end; connect to odbc as remote ( datasrc=cmg_report user = "&odbc_user" password = "&odbc_pw" ); %if ( %upcase(&__mprint) ne NOMPRINT ) %then %do; options &__mprint &__symbolgen; %end; %mend; %macro ODBC_Disconnect; disconnect from odbc; %mend; /******************************************************************************* * Name: GetID_PW * * Desc: Get loginid and password from a secured file * *------------------------------------------------------------------------------* * Arguments: * * 1st Required. Source file containing IDs and passwords. * * 2nd Required. Host id. * * 3rd Required. Specify the macro variable to put the loginid. * * 4th Required. Specify the macro variable to put the password. * *------------------------------------------------------------------------------* *******************************************************************************/ %macro Get_ID_PW( source , rhost , usrvar , pw_var ); %let source_file = &source %if ( %sysfunc( fileexist( &source_file ) ) ) %then %do; %let rc = %sysfunc( filename( dummy , &source_file ) ); %let fid = %sysfunc( fopen( &dummy ) ); %do %while( %sysfunc( fread( &fid ) ) = 0 ); %let rc = %sysfunc( fget( &fid , inrec , 500 ) ); %let machine = %scan( &inrec , 2 , %str( ) ); %if ( %upcase( &machine ) = %upcase( &rhost ) ) %then %do; %let &usrvar = %scan( &inrec , 4 , %str( ) ); %let &pw_var = %scan( &inrec , 6 , %str( ) ); %goto Break; %end; %end; %Break: %*; %let rc = %sysfunc( fclose( &fid ) ); %let rc = %sysfunc( filename( dummy ) ); %end; %else %do; %put ::: ID/PW file "&source_file" not found; %end; %mend; 

Answer 2

Therefore, I also contacted the SAS to find out what they recommend for this type of problem, and that was their (timely, as always) response. Unfortunately, it seems that they cannot be reached without turning off the symbol:

To prevent passwords from being hardcoded into SAS programs or from appearing in SAS logs, the following methods are offered:

1) The safest option is to issue the LIBNAME statement using the required SAS / Access mechanism and specify DBPROMPT = YES. This will tell you for information about connecting to the database as you run the SAS code, so that no connection information is saved in your program.

However, since this requires some manual interaction when starting up jobs, this may not be feasible in your situation.

2) Information about connecting to the database can be stored in the SAS registry and the password required to connect will be encrypted. In order to configure this, launch SAS interactively and from the Explorer window, select "Libraries". From the menu, select "File-New." Type the libref that you want to use to connect to the database in the "Name" field, then select the database engine that is used in the drop-down menu. Once the engine is selected, you will see a window that allows you to enter a username, password, path and parameters. Fill in the connection information, then click on the small button in the upper right corner that indicates “Enable at startup”. This method stores your connection information in the SAS registry and will automatically connect when you start SAS. When starting SAS in batch mode, you MUST specify -startlib when invoked so that the library is allocated. Your password will appear in your SAS log as an encrypted value.

3) When running PROC SQL Pass-Through, you can pass the SAS password through the -sysparm option when calling SAS and use the SAS-Parm SAS macro, where the password is usually encoded. An example as follows:

sas -nodms -sysparm mypassword

1? PROC SQL 2? CONNECT TO ORACLE (user = scott password = "& sysparm");

Please note that if the macro parameters MPRINT and / or SYMBOLGEN are valid, the allowed macro variable will appear in the SAS log and therefore your password will be displayed in plain text in your SAS log. NOMPRINT and NOSYMBOLGEN are the default settings.

4) In addition, when running PROC SQL Pass-Through, you can save your CONNECT in a file protected by the permissions of the operating system, such as that you just got read permission, then use the% INCLUDE statement to enable the CONNECT statement. The following is an example:

sas -nodms

1? OPTIONS NOSOURCE2; 2? PROC SQL 3? % INCLUDE 'myconnect.dat';

In the above example, OPTIONS NOSOURCE2 prevents the inclusion of code from being displayed in the SAS log. Note SOURCE2 lists the contents of the included file in the SAS log. NOSOURCE2 default setting.

5) Using SAS 9.1 and later, you can use the Proc PWENCODE procedure, which will create a coded password that can be used instead of plain text passwords in SAS programs in batch mode.

http://support.sas.com/onlinedoc/913/getDoc/proc.hlp/a002595988.htm

and select the PWENCODE procedure.

The syntax of Proc is given below. The encoded password is logged.

proc pwencode in = "plaintextPassword"; to run;

Answer 3

There is no safe method for determining an ODBC connection!

Everyone can read auth domains, usernames, and encode passwords using the metadata API. A linux administrator or root user can access any file system, including home directories and password.sas files.

SAS also provides ChangePassPhrase to lower the stored passwords to the earlier SAS PWENCODE method. If you know a password with encoding, you can decode it, for example, via the Internet https://decrypt-password.appspot.com/sas-pwdecode/

Temporary credentials used for different services. The username and password for FTP or MAIL can also be used for ssh. Files and metadata are not secure storage.

Answer 4

when SYMGET does not work in the CONNECT statement, try using the %SUPERQ quoting function. It also enables a macro without popping it into the LOG.

Answer 5

Sorry, I can’t write a comment, but %superq -Trick really works. Use it like this:

 proc sql; select * from set_encrypedon (pw="%SUPERQ(_password)"); quit; 

where you have a macro variable called _password .

Unfortunately, this cannot be wrapped in a macro, for example

 %macro pwd(); "%SUPERQ(_password)" %mend; 

because then MPRINT will write down your password again.