June 20, 2012 at 4:55 am
Hi guys,
We have a potential customer with an Adobe Flex v4 designed website running on .NET that stores documents in an SQL 2k8R2 database. They need to be able to Encrypt files when they are resting in the database and whenever they are updated – all being updated via this Flex front-end.
Is there a way SQL can call to encrypt and/decrypt the files when Flex calls them from the database? Or would PGP have to be the middleman in this scenario i.e Flex explicitly sends to PGP rather than SQL. If at all possible we would like to include the encryption/decryption without changing the configuration of the Flex
Thanks!
June 20, 2012 at 6:14 am
it sounds like you are looking for a CLR which can encrypt/decrypt a file to disk using PGP;
my Google-Fu pointed me to this thread from 2007 about SecureBlackbox , which supposedly does that;
if you can call PGP via .NET, it would be fairly easy to do this, but more details are really required;
can you give more details?
for example, here's my assumptions...and they are huge assumptions!
the file exists on C:\Data\EncryptedDoc.bin,
a database table has a reference that says "MyBizDoc.docx is encrypted as C:\Data\EncryptedDoc.bin?
is that right?
and you need to load the unencrypted value via tSQL to return as a dataset.. something like this?
SELECT
ID,
FileName,
dbo.CLR_PGPDecrypt(FileStarageLocation)
FROM SOMETABLE
WHERE ID = 42
Lowell
June 20, 2012 at 6:23 am
Lowell (6/20/2012)
it sounds like you are looking for a CLR which can encrypt/decrypt a file to disk using PGP;my Google-Fu pointed me to this thread from 2007 about SecureBlackbox , which supposedly does that;
if you can call PGP via .NET, it would be fairly easy to do this, but more details are really required;
can you give more details?
for example, here's my assumptions...and they are huge assumptions!
the file exists on C:\Data\EncryptedDoc.bin,
a database table has a reference that says "MyBizDoc.docx is encrypted as C:\Data\EncryptedDoc.bin?
is that right?
and you need to load the unencrypted value via tSQL to return as a dataset.. something like this?
SELECT
ID,
FileName,
dbo.CLR_PGPDecrypt(FileStarageLocation)
FROM SOMETABLE
WHERE ID = 42
Thanks for the reply!
At the moment the database has no knowledge of any encryption as far as I am aware, they need to implement encryption to become FIPS compliant.
So as I understand it there'll be a reference in the database to say DocumentA resides in c:\data\DocumentA.docx, so when the initial encryption occurs This'll have to change to DocumentA.pgp
And yes when the file is called to be reviewed or edited or some such it needs to be decrypted. It's essentially so when the data is at rest, it is completely encrypted. The database will assume if someone is calling it its legitimate, as there's authentication within the Flex itself.
I am by no way shape or form a .NET or SQL admin - my realm is encryption and this project is stepping over boundaries I am not familiar with.
SecureBlackBox and BizCrypto are packaged OpenPGP products, I am a PGP admin and know how to do the PGP bit of this scenario, I need some pointers on how one would get the .Net and SQL to talk with it.
I was going to be using PGP Commandline which would just be saved as some sort of batch file or script which gets called, would that work?
Thanks!
June 20, 2012 at 6:50 am
lol I'm the opposite, clueless when it comes to PGP, but well versed in Sql Server, .NET and CLR.
can you give me a pair of examples on the command line syntax for encrypting and decrypting a single file?
with that i might be able to come up with a good example.
Lowell
June 20, 2012 at 7:20 am
Ok really simple stuff:
pgp --encrypt report.doc --passphrase Pa55w0rd report.doc:encrypt (0:output file report.doc.pgp)
pgp --decrypt report.doc.pgp --passphrase Pa55w0rd
That will be enough to get the script working from a PGP point of view, but to be FIPS compliant it has to be signed with Keys, but thats a PGP issue not an SQL one.
Cheers!
June 20, 2012 at 7:43 am
I just spoke to another PGP Consultant to had another way of doing it:
Website passes record to PGP -> Encrypts -> passes back to website -> Website inserts into database
Just something else thrown into the hat.
June 20, 2012 at 7:53 am
another quick question:
pgp --decrypt report.doc.pgp --passphrase Pa55w0rd
does that create the file report.doc or return a bytestream/filestream?
maybe there is an optional param for output file name like the encrypt command?
Lowell
June 20, 2012 at 7:57 am
itll create a file, there are other switches for what to do with the file incase of duplicates:
pgp --decrypt report.doc.pgp --symmetric-passphrase Pa55w0rd --overwrite remove
June 20, 2012 at 8:28 am
well it seems pretty easy as i'm slapping together a CLR;
gota basic syntax issue for when it comes to full file names:
for the switches, do any of these look correct? i'm guessing here, as i don't have PGP command line , and the pdf of instructions is not showing specific examples for me so far.
'pgp --encrypt "C:\Folder With Spaces\report.doc" --passphrase Pa55w0rd report.doc:encrypt (0:output file "C:\Folder With Spaces\report.doc.pgp")
'pgp --encrypt "C:\Folder With Spaces\report.doc" --passphrase Pa55w0rd "C:\Folder With Spaces\report.doc":encrypt (0:output file "C:\Folder With Spaces\report.doc.pgp")
'pgp --decrypt "C:\Folder With SPaces\report.doc.pgp" --passphrase Pa55w0rd --overwrite remove
also , when you encrypt a document, does it auto-delete the original, unencrypted document? so only report.doc.pgp exists, or is that up to the process later?
Lowell
June 20, 2012 at 8:30 am
Either of the first 2 works, if you dont specify a location it assumes the root directory to place the encrypted file, and the 3rd one is fine
July 4, 2012 at 8:38 am
Hey,
Has anyone got any further suggestions for this solution?
July 4, 2012 at 9:30 am
There is asymmetric and symmetric encryption in SQL Server. You can create keys there (even temp ones) and do the encryption.
What PGP does, and most encryption algorithms is take a stream of binary data and encrypt it into something unrecognizable. The file creation piece of PGP is just a write of the stream to the file system. You could easily do the encryption with a .NET routine called from your web app and then insert the resulting binary data into SQL Server. Or you could do an insert in SQL Server, call the encryption from SQL CLR and store the result back in the table (that may not be FIPS compliant).
If you choose to use SQL Server encryption, then you either need to have good meta data in the rest of your table that describes the data for searches, or you need to hash a portion of the data before encryption and use that for searches. Indexing encrypted data doesn't work and something like
select ccnumber, name
from mutable
where decryptbykey( ccencrypted) = @userparameter
doesn't work well. Too many resources, no indexes used, and lots of waits.
PGP is asymmetric encryption, and while you can create keys in SQL Server and use those, it's resource intensive. Typically people will create a symmetric key, use that to encrypt the data, and then protect the symmetric key with an asymmetric key.
I've seen a few people do things like only create the symmetric key from the app (temp key, CREATE SYMMETRIC KEY #MyTempKey) or even send in the password from the app, which is used to decrypt the asymmetric key.
July 4, 2012 at 10:55 am
I had built a prototype CLR that i thought would work, but i didn't have PGP installed on my work machine for testing.
here's the CLR code i thought would work, but it was untested;
#Region "PGP Encryption logic?"
'##############################################################################################
'PGP Encrypt Decrypt
'##############################################################################################
'pgp --encrypt report.doc --passphrase Pa55w0rd report.doc:encrypt (0:output file report.doc.pgp)
'pgp --decrypt report.doc.pgp --passphrase Pa55w0rd --overwrite remove
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function CLR_PGPDecryptGetFileImage(ByVal FileNamePath As SqlString, ByVal PassPharase As SqlString) As SqlBytes
Try
'PGP will decrypt the file without the .pgp extention.
Dim Arguments As String = """--decrypt ""{0}"" --passphrase {1} --overwrite remove "
pShell("C:\PGP\pgp.exe", Arguments)
FileNamePath = FileNamePath.ToString.Replace(".pgp", "")
Return New SqlBytes(GetFileImage(CType(FileNamePath, String)))
Catch sqlex As SqlException
Throw New Exception("", sqlex)
Catch ex As Exception
Throw New Exception("", ex)
End Try
End Function
'pgp --encrypt report.doc --passphrase Pa55w0rd report.doc:encrypt (0:output file report.doc.pgp)
'pgp --decrypt report.doc.pgp --passphrase Pa55w0rd --overwrite remove
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Function CLR_PGPEncryptSaveFileImage(ByVal FileNamePath As SqlString, _
ByVal PassPharase As SqlString, _
ByVal FileBytes As SqlBytes) As Integer
Try
Dim _bFileBytes() As Byte
_bFileBytes = FileBytes.Buffer
'save the local file
SaveFileImage(CType(FileNamePath, String), _bFileBytes)
'encrypt it.
Dim Arguments As String = "--encrypt ""{0}"" --passphrase {1} ""{0}"":encrypt ""(0:output file ""{0}.pgp"")"
pShell("C:\PGP\pgp.exe", Arguments)
Return 0
Catch sqlex As SqlException
ShowError(sqlex.ToString)
Return 1
Catch ex As Exception
ShowError(ex.ToString)
Return 1
End Try
End Function
Public Shared Function pShell(ByVal ExecutableName As String, ByVal Arguments As String) As Boolean
Dim myProcess As New Process
myProcess.StartInfo.FileName = ExecutableName
myProcess.StartInfo.Arguments = Arguments
myProcess.StartInfo.UseShellExecute = False
'prevent a black cmd window from flashing onscreen
myProcess.StartInfo.CreateNoWindow = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()
Dim result As String = myProcess.StandardOutput.ReadToEnd
myProcess.WaitForExit()
myProcess.Dispose()
End Function
Public Shared Function pShell(ByVal ExecutableName As String, ByVal Arguments As String, ByVal WithResults As Boolean) As String
Dim myProcess As New Process
myProcess.StartInfo.FileName = ExecutableName
myProcess.StartInfo.Arguments = Arguments
myProcess.StartInfo.UseShellExecute = False
'prevent a black cmd window from flashing onscreen
myProcess.StartInfo.CreateNoWindow = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()
Dim result As String = myProcess.StandardOutput.ReadToEnd
myProcess.WaitForExit()
myProcess.Dispose()
Return result
End Function
#End Region
Lowell
July 4, 2012 at 11:22 am
If you are using SQL Server 2008 R2 Enterprise Edition you can encrypt the whole database with transparent data encryption (TDE), and no changes at all are required to your application.
Understanding Transparent Data Encryption (TDE)
http://msdn.microsoft.com/en-us/library/bb934049(v=sql.105).aspx
Database Encryption in SQL Server 2008 Enterprise Edition
http://technet.microsoft.com/library/cc278098(SQL.100).aspx
Transparent Database Encryption in SQL Server: A Planning Guide
http://www.sqlservercentral.com/articles/Transparent+Database+Encryption/71879/
July 5, 2012 at 1:59 am
Some great suggestions here guys, its a great help. I have a workshop with the client in a few days. It seems there is indeed more than one way to skin this particular cat. I'm sure the SQL people will want the .NET people to do it, the .NET people will want the SQL people to do it, and the client will want me to do it, so we'll see who wins 🙂
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply