SAS and Encrypted Data

[jtb2o2]

We are currently trying to run SAS with encrypted data (lancrypt).  For VERY small files, it works fine.  No issues.  For what I consider to still be small (22 Meg) files, it is rebooting the server when I try to overwrite a file using either proc sort or a data step.  Does anyone have any thoughts about this?  Is it a setting I need to change?

Here is what I ran, I've commented the results....

hrm is an encrypted folder

enc is an encrypted folder

une is an unencrypted folder

*** with "large" master mailed file *** ;

sort data = hrm.master_mailed out=enc.master_mailed ;  by hm_seqnum ;  run ;  *** file does not exist - run fine *** ;

sort data = hrm.master_mailed out=enc.master_mailed ;  by hm_seqnum ;  run ;  *** file exists - bombed *** ;

sort data = hrm.master_mailed out=une.master_mailed ;  by hm_seqnum ;  run ;  *** file does not exist - run fine *** ;

sort data = hrm.master_mailed out=une.master_mailed ;  by hm_seqnum ;  run ;  *** file exists - ran fine *** ;

data enc.master_mailed ;

  set hrm.master_mailed ;

  run ;  ** file does not exist - bombed - left lock file ** ;

data enc.master_mailed ;

  set hrm.master_mailed ;

  run ;  ** first thing to run after reboot - ran fine  ** ;

data enc.master_mailed ;

  set hrm.master_mailed ;

  run ;  ** run again after deleting file - ran fine  ** ;

data enc.master_mailed ;

  set hrm.master_mailed ;

  run ;  ** run again leaving file there - bomb  ** ;

*** with small master30d mailed file *** ;

sort data = hrm.master30d_mailed out=enc.master30d_mailed ;  by hm_seqnum ;  run ;  *** file does not exist - run fine *** ;

sort data = hrm.master30d_mailed out=enc.master30d_mailed ;  by hm_seqnum ;  run ;  *** file exists - ran fine *** ;

sort data = hrm.master30d_mailed out=une.master30d_mailed ;  by hm_seqnum ;  run ;  *** file does not exist - run fine *** ;

sort data = hrm.master30d_mailed out=une.master30d_mailed ;  by hm_seqnum ;  run ;  *** file exists - ran fine *** ;

data enc.master30d_mailed ;

  set hrm.master30d_mailed ;

  run ;  ** file does not exist - ran fine ** ;

data enc.master30d_mailed ;

  set hrm.master30d_mailed ;

  run ;  ** run again leaving file there - ran fine  ** ;

[ballardw]

I think the first thing I would check would be your permissions in relation to the folder ENC. You may not have write privileges.

[jtb2o2]

Yup.  Thought of that too.  Permissions are EXACTLY the same, on the file level and folder level.  The only difference is the small file is 2 MB and the "large" file is 22 MB.

[jakarman]

Rebooting the server is looking something went terrible wrong.

I found: https://www.sophos.com/en-us/medialibrary/pdfs/manuals/sglc_38_aheng.pdf  There is note on word renaming files (4.4) could be a problem. SAS is also doing things like that.

Some others:

- Another one when processing SAS files you will notice it will be written out in bigger chunks like 20Mb due to caching and other optimization like a RDBMS is doing.  
- As Sophos has hacked the IO interface maybe SAS has also hacked some things. http://downloads.sophos.com/readmes/readsglc_39_ceng.html
just try and validate some processing with bigger datasets (including sorting). Review whit is known and how it should work. 

[jtb2o2]

We are trying this.  Thank you.

[jtb2o2]

Sorry, that was for Jaap Karman.

[Kurt_Bremser]

Your user process causes the server to reboot? Which 'OS' ist this?

[jtb2o2]

Kurt Bremser, we are using Windows server 2012 R2

[Kurt_Bremser]

#1 I consider this as one more confirmation that Windows is not suitable for server operations, period.

#2 Try to alleviate the problems by avoiding overwriting of files with the same name, as that seems to somehow be the culprit. Remove existing files first.

I must say, however, that SAS uses a very foolproof way to deal with existing datasets. The new dataset is written side-by-side to the existing one by appending .lck to the filename, and only when the operation has finished successfully, the old file is deleted and the new one renamed. Maybe the quick succession of delete/rename after transferring a larger chunk of data causes an overflow in the interaction between the Windows I/O system and the Lan Crypt module, which must insert itself in the file I/O subsystem.

I strongly suspect Lan Crypt to be responsible (and of course, the lousy design of Windows)

[Tom]

I do not see any "encryption" in the SAS code.  Are you using some third party tool to create an encrypted folder or drive?

What happens if you just use an operating system command to copy the SAS dataset from the path that the libref HRM is using to the path that the libref ENC is using?

[jtb2o2]

Hi Tom.

It IS a third party tool.  Lan Crypt.  I can do a manual copy with no issues whatsoever.

[Tom]

Sounds like that LANCRYPT is not responding properly to the way that SAS replaces an existing file.  That it depends on the file size probably means it is a timing issue.

When SAS writes a new file it first writes the new version using a modified name (master_mailed.sas7bdat.lck when that is finished it renames the file (master_mailed.sas7bdat).

When there is already a file with that name it removes it just before the rename.  This way if the step that is writing the data set fails then the existing file is not replaced.

You should complain the maker of the LANCRYPT software. 

[jakarman]

As it is Windows you could run bitlocker as it is coming with the professional license. This wil encrypt harddisks a mitigation for the physical attacks. You need a third party tool with Unix for this.

It wil not protect any data for abuse by admins eg with their backup/dr operations.

- SAS datasets can be encrypted using SAS  AES is possible wiht 9.4. That will mitiaget sensitive data in sas type datasets.

- The temporaries (work/util) should be emptied cleaned. Having those in backup or DR is not very smart.

- Any data access at OS level should be monitored when it is highly sensitive. Should be no issue as a standard mititagation.  

Wiht those acttions the need for a third party tool encrypting data on personal access is less necessary.