ODBC Connection SAS Enterprise Guide - Data is Encrypted

[jv4]

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?

[SASKiwi]

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;

[jv4]

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?

[SASKiwi]

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.

[jv4]

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.

[SASKiwi]

Please post your complete SAS log.

[jv4]

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

[SASKiwi]

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;

[Sajid01]

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-datab... 
The purpose of encryption of sensitive data is to prevent it being viewed without proper authorization and keys/certificate.

[Kurt_Bremser]

Your pass-through misses a closing bracket:

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

[jv4]

Hi saskiwi,

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

[SASKiwi]

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.

[jv4]

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;

[Kurt_Bremser]

You still miss a closing bracket to end the EXECUTE:

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

[jv4]

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