Dynamic data decryption

[shivxy]

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!

[SASKiwi]

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;

[shivxy]

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?

[SASKiwi · Accepted solution]

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.

[shivxy]

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!

[SASKiwi]

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.

[Nartlada]

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

[SASKiwi]

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

[Nartlada]

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

[mkeintz]

@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

[SASKiwi]

@mkeintz - @shivxy was the original poster.

[mkeintz]

Oops. Thanks..

[shivxy]

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

[shivxy]

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

[shivxy]

we moved to always on encryption in SQL 2016