topic Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted in SAS Enterprise Guide

ODBC Connection SAS Enterprise Guide - Data is Encrypted

jv4 — Mon, 22 Feb 2021 20:57:23 GMT

I have an ODBC connection to a replicated database. I am able to see all the tables and most of the data in the tables, however, some of the data is encrypted. How would I enter the sql statement so that I can see all of the encrypted data in SAS? In mysql I can just run the sql statement and then see the encrypted data in the tables. Is there a way to use that sql statement in SAS so that I can see that encrypted data?

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

SASKiwi — Mon, 22 Feb 2021 21:12:01 GMT

You could try an SQL Passthru query containing your native SQL statement(s).

libname mysql odbc noprompt = "<MySQL connection string>";

proc sql;
 connect using mysql;
  create table Want as 
  select * from connection to mysql
  (<-Put your MySQL query here->)
  ;
quit;

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

jv4 — Mon, 22 Feb 2021 21:48:41 GMT

Thanks SASKiwi.I am a beginner when it comes to this ODBC connection scenarios.

I have a libname statement with the connection to the database. Please see example below.

libname db odbc datasrc=datasource schema = schema user=user password = password; --> this is my current statement which gets me to the tables. I just can't see the data in certain columns where it is encrypted.

Would I put the one line of sql code that decrypts the data into the noprompt section?

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

SASKiwi — Mon, 22 Feb 2021 22:19:49 GMT

No, you just need to replace the LIBNAME I posted with yours:

libname db odbc datasrc=datasource schema = schema user=user password = password;

proc sql;
 connect using db;
  create table Want as 
  select * from connection to db
  (<-Put your MySQL query here->)
  ;
quit;

Then put your actual MySQL query inside round brackets where I've indicated. Test it with something easy like: select * from MySQLTable.

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

jv4 — Wed, 24 Feb 2021 23:17:13 GMT

I ran this and didn't get an error, or see anything in the log.
When I run another sql step the data is still encrypted.

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

SASKiwi — Thu, 25 Feb 2021 02:13:05 GMT

Please post your complete SAS log.

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

jv4 — Wed, 03 Mar 2021 16:07:45 GMT

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='CodeName';
4 %LET _CLIENTPROJECTPATH='';
5 %LET _CLIENTPROJECTNAME='';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13 STYLE=HtmlBlue
14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
15 NOGTITLE
16 NOGFOOTNOTE
17 GPATH=&sasworklocation
18 ENCODING=UTF8
19 options(rolap="on")
20 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 proc sql;
24 connect using vh;
25 create table plan2 as
26 select *
27 from connection to vh
28 (call set_key('abcdefghjsfhdksjfbask0123dfjjsldkj'
29 )
30 ;quit;
31
32 GOPTIONS NOACCESSIBLE;
33 %LET _CLIENTTASKLABEL=;
34 %LET _CLIENTPROJECTPATH=;
35 %LET _CLIENTPROJECTNAME=;
36 %LET _SASPROGRAMFILE=;
37
38 ;*';*";*/;quit;run;
39 ODS _ALL_ CLOSE;
40
41
42 QUIT; RUN;
43

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

SASKiwi — Wed, 03 Mar 2021 19:29:40 GMT

Try this:

proc sql;
connect using vh;
execute (call set_key('abcdefghjsfhdksjfbask0123dfjjsldkj') by vh;
create table plan2 as
select *
from connection to vh
(select * from MyEncryptedTable)
;
;quit;

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

Sajid01 — Thu, 04 Mar 2021 18:51:21 GMT

Hello
Please have a look at this https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/

and this https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15
The purpose of encryption of sensitive data is to prevent it being viewed without proper authorization and keys/certificate.

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

jv4 — Tue, 09 Mar 2021 16:39:05 GMT

Hi saskiwi,

I tried this new method and am getting the same type of log as above, and no output data set.

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

Kurt_Bremser — Tue, 09 Mar 2021 17:58:35 GMT

Your pass-through misses a closing bracket:

27 from connection to vh
28 (call set_key('abcdefghjsfhdksjfbask0123dfjjsldkj'
29 )
30 ;quit;

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

SASKiwi — Tue, 09 Mar 2021 19:20:26 GMT

Please post your SAS log including code and any notes. Also you would be best opening a SAS Tech Support Track on this to tap into their database experts.

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

jv4 — Wed, 24 Mar 2021 19:52:50 GMT

below is the log. I apologize I didn't see your response. I will definitely open a task support track.

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Pull';
4 %LET _CLIENTPROJECTPATH='';
5 %LET _CLIENTPROJECTNAME='';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13 STYLE=HtmlBlue
14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
15 NOGTITLE
16 NOGFOOTNOTE
17 GPATH=&sasworklocation
18 ENCODING=UTF8
19 options(rolap="on")
20 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 proc sql;
24 connect using vh;
25 execute (call set_key('abcdedhksjhfksdjhflkfh3f215s3d1fsdfhskdjfhlsdj') by vh;
26 create table plan2 as
27 select *
28 from connection to vh
29 (select * from table)
30 ;
31 ;quit;
32
33 GOPTIONS NOACCESSIBLE;
34 %LET _CLIENTTASKLABEL=;
35 %LET _CLIENTPROJECTPATH=;
36 %LET _CLIENTPROJECTNAME=;
37 %LET _SASPROGRAMFILE=;
38
39 ;*';*";*/;quit;run;
40 ODS _ALL_ CLOSE;
41
42
43 QUIT; RUN;

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

Kurt_Bremser — Thu, 25 Mar 2021 05:52:36 GMT

You still miss a closing bracket to end the EXECUTE:

execute (call set_key('abcdedhksjhfksdjhflkfh3f215s3d1fsdfhskdjfhlsdj') by vh;

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

jv4 — Wed, 31 Mar 2021 15:21:12 GMT

Oh my goodness, I thought I got it. Sorry about that! But it did work this time! Thank you so much for your help!

Re: ODBC Connection SAS Enterprise Guide - Data is Encrypted

SASKiwi — Wed, 31 Mar 2021 20:11:47 GMT

@jv4 - Please mark your post as answered in that case.