BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
shivxy
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

15 REPLIES 15
SASKiwi
PROC Star

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
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5

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
PROC Star

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
Fluorite | Level 6

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

SASKiwi
PROC Star

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

Nartlada
Fluorite | Level 6

@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
PROC Star

@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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
SASKiwi
PROC Star

@mkeintz - @shivxy was the original poster.

mkeintz
PROC Star
Oops. Thanks..
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
shivxy
Calcite | Level 5
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
Calcite | Level 5
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
Calcite | Level 5
we moved to always on encryption in SQL 2016

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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