topic Re: SAS and Encrypted Data in Administration and Deployment

SAS and Encrypted Data

jtb2o2 — Wed, 19 Aug 2015 15:58:28 GMT

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 ** ;

Re: SAS and Encrypted Data

ballardw — Wed, 19 Aug 2015 17:47:14 GMT

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

Re: SAS and Encrypted Data

jtb2o2 — Wed, 19 Aug 2015 17:54:54 GMT

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.

Re: SAS and Encrypted Data

jakarman — Wed, 19 Aug 2015 18:43:30 GMT

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.

Re: SAS and Encrypted Data

Kurt_Bremser — Thu, 20 Aug 2015 07:08:38 GMT

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

Re: SAS and Encrypted Data

Tom — Thu, 20 Aug 2015 07:37:05 GMT

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?

Re: SAS and Encrypted Data

jtb2o2 — Thu, 20 Aug 2015 12:43:53 GMT

Hi Tom.

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

Re: SAS and Encrypted Data

jtb2o2 — Thu, 20 Aug 2015 12:45:24 GMT

We are trying this. Thank you.

Re: SAS and Encrypted Data

jtb2o2 — Thu, 20 Aug 2015 12:46:27 GMT

Sorry, that was for Jaap Karman.

Re: SAS and Encrypted Data

jtb2o2 — Thu, 20 Aug 2015 12:47:38 GMT

Kurt Bremser, we are using Windows server 2012 R2

Re: SAS and Encrypted Data

Kurt_Bremser — Thu, 20 Aug 2015 13:08:30 GMT

#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)

Re: SAS and Encrypted Data

Tom — Thu, 20 Aug 2015 13:16:39 GMT

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.

Re: SAS and Encrypted Data

jakarman — Thu, 20 Aug 2015 15:25:44 GMT

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.