topic Re: Dynamic data decryption in SAS Data Management

Dynamic data decryption

shivxy — Thu, 22 Feb 2018 02:21:32 GMT

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!

Re: Dynamic data decryption

SASKiwi — Thu, 22 Feb 2018 02:55:11 GMT

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;

Re: Dynamic data decryption

shivxy — Fri, 11 May 2018 14:56:12 GMT

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?

Re: Dynamic data decryption

SASKiwi — Sun, 13 May 2018 02:34:32 GMT

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.

Re: Dynamic data decryption

Nartlada — Fri, 25 May 2018 10:28:23 GMT

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

Re: Dynamic data decryption

SASKiwi — Fri, 25 May 2018 23:14:42 GMT

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.

Re: Dynamic data decryption

SASKiwi — Fri, 25 May 2018 23:16:30 GMT

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

Re: Dynamic data decryption

Nartlada — Sat, 26 May 2018 10:48:58 GMT

@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.

Re: Dynamic data decryption

mkeintz — Sat, 26 May 2018 23:59:39 GMT

@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

Re: Dynamic data decryption

SASKiwi — Sun, 27 May 2018 20:44:30 GMT

@mkeintz - @shivxy was the original poster.

Re: Dynamic data decryption

mkeintz — Sun, 27 May 2018 21:35:22 GMT

Oops. Thanks..

Re: Dynamic data decryption

shivxy — Wed, 30 May 2018 16:31:38 GMT

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!!

Re: Dynamic data decryption

shivxy — Fri, 08 Jun 2018 01:44:39 GMT

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!

Re: Dynamic data decryption

shivxy — Fri, 08 Jun 2018 01:45:42 GMT

we moved to always on encryption in SQL 2016

Re: Dynamic data decryption

shivxy — Fri, 08 Jun 2018 01:46:56 GMT

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

Re: Dynamic data decryption

SASKiwi — Fri, 08 Jun 2018 01:50:26 GMT

@shivxy - I'd be interested to know what your connection string looks like now you have it working.