SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Dynamic data decryption

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Dynamic data decryption

Forgive me if this isn't the correct forum to post this question.. i'm VERY new to SAS and still learning new things everyday.  My question is: I need to do column level decryption with SaS. 

 

Here is the setup.

 

SQL Server 2016:

-- SYMMETRIC KEY has been created with AES_256 bit for the database.

-- This table has been created.  The FooPassB will hold the encrypted data.

 

CREATE TABLE Foo
(
FooId INT IDENTITY(1,1) PRIMARY KEY,
FooPassN NVARCHAR(25),
FooPassB VARBINARY(MAX) NULL,
LoadDate DATETIME DEFAULT(GETDATE())
);

 

--In SQL I would add data to this table like this:

OPEN SYMMETRIC KEY CERT_KEY
DECRYPTION BY CERTIFICATE Cert;
GO

 INSERT INTO Foo (FooPassN, FooPassB)
 VALUES
 ('1234', ENCRYPTBYKEY(KEY_GUID('CERT_KEY'), N'1234')),
 ('5678', ENCRYPTBYKEY(KEY_GUID('CERT_KEY'), N'5678'))

CLOSE SYMMETRIC KEY CERT_KEY;

 

Now here is my issue... how do I decrypt that data in sas? 

 

In SQL I would:

 

OPEN SYMMETRIC KEY CERT_KEY
DECRYPTION BY CERTIFICATE Cert;
GO

SELECT FooPassN, FooPassB, CONVERT(NVARCHAR(25), DECRYPTBYKEY(FooPassB)) AS 'Decrypted Foo'
FROM DBO.Foo

CLOSE SYMMETRIC KEY CERT_KEY;

 

But I don't think I can wrap that SQL statement in a SaS statement.  Does SaS have a way to send that whole command? or do I have to create a API or a Sproc that SaS can connect to and pull data from that instead?

 

Cheers and thanks for any insight on this!


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 3,857

Re: Dynamic data decryption

I would check out if you have the latest SQL Server ODBC driver which you can do on the Microsoft website - if not download the latest one. It is probably also worth opening a track with SAS Tech Support.

 

View solution in original post


All Replies
Super User
Posts: 3,857

Re: Dynamic data decryption

Yes you can by wrapping the SQL Server-specific SQL in a pass-thru query:

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;");
  create table Want  as 
  select * from connection to odbc
  (---- Put your SQL Server query here
   )
  ;
  disconnect from odbc;
quit;
Occasional Contributor
Posts: 6

Re: Dynamic data decryption

Thanks for the response SASKiwi (I have been out for a bit and haven't had a chance to get back to this until now).  Looks like the SQL stuff changed just a bit since I last posted.  Operations has put in place a SQL Server 2016 with always on encryption.  They have given me a cert that allows me to query the data via SSMS.  However I'm still having issues with SaS.  Here is what i'm attempting to do:

 

If I create a ODBC 64 bit DNS entry to my point to my SQL box and try to call it this way:

 

libname odbclib odbc noprompt="dsn=odbclib;

DRIVER=SQLServer;Trusted Connection=yes;database=AlwaysEncryptedLab";   

 

I get this as a result with no data.

NOTE: Libref ODBCLIB was successfully assigned as follows:

      Engine:        ODBC

      Physical Name: odbclib

 

If I try this way

libname odbclib odbc noprompt="server=xxxxx;

DRIVER=SQLServer;Trusted Connection=yes;ColumnEncryption=Enabled;database=AlwaysEncryptedLab";               

 

I get this error:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

ERROR: Error in the LIBNAME statement.

 

So really all I want to do is know how to create a odbc connection with columnEncryption=Enabled and set that to a libname.  

 

Any suggestions?

Solution
2 weeks ago
Super User
Posts: 3,857

Re: Dynamic data decryption

I would check out if you have the latest SQL Server ODBC driver which you can do on the Microsoft website - if not download the latest one. It is probably also worth opening a track with SAS Tech Support.

 

Occasional Contributor
Posts: 6

Re: Dynamic data decryption

Looks like updating the driver and adding the schema was able to do the trick!  ODBC 13.1 (not 13) 

https://www.microsoft.com/en-us/download/details.aspx?id=50420

 

Then we setup the DSN called A and used the code below and it worked..

 

libname aaaa odbc datasrc=A user= alwayson password= password schema="dbo";

 

Thanks everyone!

Super User
Posts: 3,857

Re: Dynamic data decryption

Also I don't think you named your ODBC driver correctly. It is more likely to be SQL Server not SQLServer. Check with your SAS administrator to get the correct name.

New Contributor
Posts: 3

Re: Dynamic data decryption

I too have exactly the same question.  Do you have any solution now?

Super User
Posts: 3,857

Re: Dynamic data decryption

@Nartlada - have you tried? I think @shivxy has other problems with his connection string, not just encryption.

New Contributor
Posts: 3

Re: Dynamic data decryption

@SASKiwi - Yes. After multiple tries, after many failures, I've finally made it.  I also had some problems with connection string so I changed to dsn.  Thank you very much for your suggestion.  Here is my code:

 

proc sql;
    connect to odbc(dsn=<Data source name>);
    execute (open master key decryption by password = '<Master key encryption password>') by odbc;
    create table table2 as
    select * from connection to odbc
    (select convert(nvarchar, decryptbykeyautocert(cert_id('<Certificate name>'), null, <encrypted column>))
    from table1);
    disconnect from odbc;
quit;

 

@shivxy- I used DecryptByKeyAutoCert that combines the functionality of OPEN SYMMETRIC KEY and DecryptByKey so I don't need to separately open and close symmetric key.

Trusted Advisor
Posts: 1,311

Re: Dynamic data decryption

@Nartlada

 

Please mark your report of a solution as the solution.  Just because this is a bit uncommon (you are both the original poster and the solution reporter) doesn't mean this topic should remain unsolved.

 

Mark

Super User
Posts: 3,857

Re: Dynamic data decryption

@mkeintz - @shivxy was the original poster.

Trusted Advisor
Posts: 1,311

Re: Dynamic data decryption

Oops. Thanks..
Occasional Contributor
Posts: 6

Re: Dynamic data decryption

We also think we have a key store issue and looking into this. My security guy is out until next week and we're going to hit this again. With everyone replies I think we will get this!!
Occasional Contributor
Posts: 6

Re: Dynamic data decryption

we had some odd problems and had to run everything as admin. hold down select and right click on the app.. pick run as admin.not sure if that will help
Occasional Contributor
Posts: 6

Re: Dynamic data decryption

we moved to always on encryption in SQL 2016
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 517 views
  • 4 likes
  • 4 in conversation