Solved

Encrypt in c# into sql server, read, xml-serialize, download and decrypt in local windows PC

Posted on 2014-02-23
14
1,579 Views
Last Modified: 2014-04-03
What are some options for doing the local decryption?
0
Comment
Question by:codequest
  • 3
  • 3
  • 3
  • +3
14 Comments
 
LVL 16

Assisted Solution

by:Kalpesh Chhatrala
Kalpesh Chhatrala earned 50 total points
Comment Utility
Hi,

try this MSSQL Data Encryption / Decryption Tutorial.

http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

I used this code in my application to encrypt data. it's very helpful for me.
0
 
LVL 2

Author Comment

by:codequest
Comment Utility
Thanks, however, not sure how to apply this to the general problem of encrypting in a .net server environment and decrypting in a local client environment e.g. Windows
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
Do you just need to encrypt the data or are you looking to encrypt it before the data leaves the server?

Also what is your target environment on the local windows 7 pc?

Can you describe your workflow in more detail?
0
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
Comment Utility
What is it that you want to encrypt?  Do you want to use certificates for symmetric encryption/decryption ?  How do you envision things to be working?
0
 
LVL 61

Accepted Solution

by:
btan earned 225 total points
Comment Utility
IF you are looking at the SQL content such as password or column, probably good to use the built-in SQL Server crypto functions like EncryptByKey, DecryptbyKey or use Transparent Database Encryption (requires Enterprise Edition).

This is also example using SQL CLR C# User Defined Type in .NET - encrypt data in a columns by using .NET types that can be imported to SQL Server

However, I understand that the 2 APIs are not compatible with any client side encryption or decryption because it uses an internal, undocumented, storage format. But also note we should not need for the application to encrypt or decrypt the password itself and use its native password protect capability instead. There is a nice encryption hierarchy to sum up the story.

Another example

.NET: Create an X509Certificate2 object and use the public key to encrypt a string password; Call the stored procedure with the encrypted password and use the SQL Server certificate to decrypt it
0
 
LVL 2

Author Comment

by:codequest
Comment Utility
Thanks for the questions and inputs.

I am building an ASP net application.  It will produce work products in SQL Server. A user function is to be able to convert certain data sets to XML and download them.  One of the options will be to encrypt these data sets prior to download.  After they are downloaded, one workflow is to re-upload them, provide the password to decrypt them, and we load them into the database for further use.  I have already implemented that.

What I'm hoping to find out is if there is any way that the document could be downloaded encrypted and then locally decrypted, so that it could for example be converted to an Excel document.

I appreciate that the person doing the download could do that on a secure line, so that they might not need to encrypt it.  I'm imagining some use cases though where they might want to download it encrypted and then locally decrypt it later for some other purpose.

I'm thinking that one possible scenario is to create a little local .net app that they could use to decrypt with, because at least would have the same .net technology, which might work the same way both in a server application and in a local .net application.  in that case however I would be stuck with the .net dependency which could be some limitation.

Another option may be that they have to download one of the work products decrypted and then encrypt it themselves locally if that's what they need (like to distribute it via email).

In fantasyland it could be decrypted in Excel.

It is possible that there is no solution for this that is not highly convoluted and if that is the case it would be good to at least get that resolved.   So answers that might indicate that are welcome.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 150 total points
Comment Utility
So apparently it's possible to access the windows API
http://social.msdn.microsoft.com/Forums/en-US/84d49593-2d8e-4e7c-af3a-61882af9557d/access-to-cryptencrypt-cryptoapiwinapi-functions-in-vba?forum=isvvba

I didn't read through the whole thing but I'm just posting it as a possible solution.

Option Explicit

Private Declare Function CryptAcquireContext Lib "advapi32.dll" Alias "CryptAcquireContextA" (phProv As Long, pszContainer As String, pszProvider As String, ByVal dwProvType As Long, ByVal dwFlags As Long) As Long

Private Declare Function CryptCreateHash Lib "advapi32.dll" (ByVal hProv As Long, ByVal Algid As Long, ByVal hKey As Long, ByVal dwFlags As Long, phHash As Long) As Long

Private Declare Function CryptDeriveKey Lib "advapi32.dll" (ByVal hProv As Long, ByVal Algid As Long, ByVal hBaseData As Long, ByVal dwFlags As Long, phKey As Long) As Long

Private Declare Function CryptDestroyHash Lib "advapi32.dll" (ByVal hHash As Long) As Long

Private Declare Function CryptDestroyKey Lib "advapi32.dll" (ByVal hKey As Long) As Long

Private Declare Function CryptEncrypt Lib "advapi32.dll" (ByVal hKey As Long, ByVal hHash As Long, ByVal Final As Long, ByVal dwFlags As Long, ByVal pbData As String, pdwDataLen As Long, ByVal dwBufLen As Long) As Long

Private Declare Function CryptDecrypt Lib "advapi32.dll" (ByVal hKey As Long, ByVal hHash As Long, ByVal Final As Long, ByVal dwFlags As Long, ByVal pbData As String, pdwDataLen As Long) As Long

Private Declare Function CryptExportKey Lib "advapi32.dll" (ByVal hKey As Long, ByVal hExpKey As Long, ByVal dwBlobType As Long, ByVal dwFlags As Long, ByVal pbData As String, pdwDataLen As Long) As Long

Private Declare Function CryptGenKey Lib "advapi32.dll" (ByVal hProv As Long, ByVal Algid As Long, ByVal dwFlags As Long, phKey As Long) As Long

Private Declare Function CryptGetProvParam Lib "advapi32.dll" (ByVal hProv As Long, ByVal dwParam As Long, pbData As Any, pdwDataLen As Long, ByVal dwFlags As Long) As Long

Private Declare Function CryptGetUserKey Lib "advapi32.dll" (ByVal hProv As Long, ByVal dwKeySpec As Long, phUserKey As Long) As Long

Private Declare Function CryptHashData Lib "advapi32.dll" (ByVal hHash As Long, ByVal pbData As String, ByVal dwDataLen As Long, ByVal dwFlags As Long) As Long

Private Declare Function CryptReleaseContext Lib "advapi32.dll" (ByVal hProv As Long, ByVal dwFlags As Long) As Long

Private Declare Function CryptSignHash Lib "advapi32.dll" Alias "CryptSignHashA" (ByVal hHash As Long, ByVal dwKeySpec As Long, ByVal sDescription As String, ByVal dwFlags As Long, ByVal pbSignature As String, pdwSigLen As Long) As Long

Private Declare Function CryptVerifySignature Lib "advapi32.dll" Alias "CryptVerifySignatureA" (ByVal hHash As Long, ByVal pbSignature As String, ByVal dwSigLen As Long, ByVal hPubKey As Long, ByVal sDescription As String, ByVal dwFlags As Long) As Long

'API error function

Private Declare Function GetLastError Lib "kernel32" () As Long

'API memory functions

Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long

Private Declare Function GlobalFree Lib "kernel32" (ByVal hMem As Long) As Long

Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long

Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long

Private Declare Sub CpyMemValAdrFromRefAdr Lib "kernel32" Alias "RtlMoveMemory" (ByVal hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)

Private Declare Sub CpyMemRefAdrFromValAdr Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, ByVal hpvSource As Any, ByVal cbCopy As Long)

'constants for API memory functions

Private Const GMEM_MOVEABLE = &H2

Private Const GMEM_ZEROINIT = &H40

Private Const GHND = (GMEM_MOVEABLE Or GMEM_ZEROINIT)

'constants for Cryptography API functions

Private Const MS_DEF_PROV = "Microsoft Base Cryptographic Provider v1.0"

Private Const PROV_RSA_FULL = 1

Private Const CRYPT_NEWKEYSET = &H8

Private Const PP_CONTAINER = 6

Private Const AT_KEYEXCHANGE = 1

Private Const AT_SIGNATURE = 2

Private Const SIMPLEBLOB = 1

Private Const ALG_CLASS_DATA_ENCRYPT = 24576

Private Const ALG_CLASS_HASH = 32768

Private Const ALG_TYPE_ANY = 0

Private Const ALG_TYPE_BLOCK = 1536

Private Const ALG_TYPE_STREAM = 2048

Private Const ALG_SID_RC2 = 2

Private Const ALG_SID_RC4 = 1

Private Const ALG_SID_MD5 = 3

Private Const CALG_MD5 = ((ALG_CLASS_HASH Or ALG_TYPE_ANY) Or ALG_SID_MD5)

Private Const CALG_RC2 = ((ALG_CLASS_DATA_ENCRYPT Or ALG_TYPE_BLOCK) Or ALG_SID_RC2)

Private Const CALG_RC4 = ((ALG_CLASS_DATA_ENCRYPT Or ALG_TYPE_STREAM) Or ALG_SID_RC4)

'constants from WinErr.h

Private Const NTE_NO_KEY As Long = -2146893811 '0x8009000DL

Private Const NTE_BAD_SIGNATURE As Long = -2146893818

'clsCryptoFilterBox constants

Private Const CFB_BUSY = 0

Private Const CFB_READY = 1

Private Const CFB_VALID = 2

Private Const ENCRYPT_ALGORITHM = CALG_RC4

Private Const ENCRYPT_BLOCK_SIZE = 1

Private Const CRYPT_EXPORTABLE = 1

'private property buffers

Private sInBuffer As String

Private sOutBuffer As String

Private sPassword As String

Private sSignature As String

Private lStatus As Long

Public Property Get InBuffer() As String

InBuffer = sInBuffer

End Property

Public Property Let InBuffer(vNewValue As String)

sInBuffer = vNewValue

End Property

Public Property Get OutBuffer() As String

OutBuffer = sOutBuffer

End Property

Public Property Get Signature() As String

Signature = sSignature

End Property

Public Property Let Signature(vNewValue As String)

sSignature = vNewValue

End Property

Public Sub Sign()

'Create a signature for Inbuffer and place in Signature

Dim sContainer As String, sDescription As String, sProvider As String, lHCryptprov As Long

Dim lHHash As Long, lResult As Long, lSignatureLen As Long

On Error GoTo ErrSign

'switch Status property

lStatus = CFB_BUSY

'init Signature property

sSignature = ""

'Get handle to the default provider.

sContainer = vbNullChar

sProvider = MS_DEF_PROV & vbNullChar

If Not CBool(CryptAcquireContext(lHCryptprov, ByVal sContainer, ByVal sProvider, PROV_RSA_FULL, 0)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptAcquireContext!")

GoTo ReleaseHandles:

End If

'Create a hash object.

If Not CBool(CryptCreateHash(lHCryptprov, CALG_MD5, 0, 0, lHHash)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptCreateHash!")

GoTo ReleaseHandles:

End If

If Not CBool(CryptHashData(lHHash, sInBuffer, Len(sInBuffer), 0)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptHashData!")

GoTo ReleaseHandles:

End If

'Sign hash object.

'Determine size of signature.

sDescription = vbNullChar

lResult = CryptSignHash(lHHash, AT_SIGNATURE, sDescription, 0, sSignature, lSignatureLen)

sSignature = String(lSignatureLen, vbNullChar)

'Sign hash object (with signature key).

If Not CBool(CryptSignHash(lHHash, AT_SIGNATURE, sDescription, 0, sSignature, lSignatureLen)) Then

MsgBox ("Error " & CStr(GetLastError()) & " during CryptSignHash")

GoTo ReleaseHandles:

End If

ReleaseHandles:

'Destroy hash object.

If lHHash Then lResult = CryptDestroyHash(lHHash)

'Release provider handle.

If lHCryptprov Then lResult = CryptReleaseContext(lHCryptprov, 0)

'switch Status property

lStatus = CFB_READY

Exit Sub

ErrSign:

MsgBox ("ErrSign " & Error$)

GoTo ReleaseHandles

End Sub

Public Sub Validate()

'Validate InBuffer with Signature and assign Status with result

Dim bValid As Boolean, sContainer As String, sDescription As String, sProvider As String

Dim lDataLen As Long, lDataPoint As Long, lHCryptprov As Long, lHHash As Long

Dim lResult As Long, lSignatureLen As Long, lHCryptKey As Long

ReDim aByteData(0) As Byte

On Error GoTo ErrValidate

'switch Status property

lStatus = CFB_BUSY

'init internal valid flag

bValid = True

'Get handle to the default provider.

sContainer = vbNullChar

sProvider = MS_DEF_PROV & vbNullChar

If Not CBool(CryptAcquireContext(lHCryptprov, ByVal sContainer, ByVal sProvider, PROV_RSA_FULL, 0)) Then

bValid = False

MsgBox ("Error " & CStr(GetLastError) & " during CryptAcquireContext!")

GoTo ReleaseHandles:

End If

'Create a hash object.

If Not CBool(CryptCreateHash(lHCryptprov, CALG_MD5, 0, 0, lHHash)) Then

bValid = False

MsgBox ("Error " & CStr(GetLastError) & " during CryptCreateHash!")

GoTo ReleaseHandles:

End If

'Add data to hash object.

If Not CBool(CryptHashData(lHHash, sInBuffer, Len(sInBuffer), 0)) Then

bValid = False

MsgBox ("Error " & CStr(GetLastError) & " during CryptHashData!")

GoTo ReleaseHandles:

End If

'Determine size of signature.

'sDescription = vbNullChar

'lResult = CryptSignHash(lHHash, AT_SIGNATURE, sDescription, 0, 0, lSignatureLen)

'Get handle to signature key.

If Not CBool(CryptGetUserKey(lHCryptprov, AT_SIGNATURE, lHCryptKey)) Then

bValid = False

MsgBox ("Error " & CStr(GetLastError) & " during CryptGetUserKey!")

GoTo ReleaseHandles:

End If

lSignatureLen = Len(sSignature)

'Verify signature.

If Not CBool(CryptVerifySignature(lHHash, sSignature, lSignatureLen, lHCryptKey, sDescription, 0)) Then

If GetLastError = NTE_BAD_SIGNATURE Then

bValid = False

GoTo ReleaseHandles:

Else

bValid = False

MsgBox ("Error " & CStr(GetLastError) & " during CryptVerifySignature!")

GoTo ReleaseHandles:

End If

End If

ReleaseHandles:

'Release signature key.

If lHCryptKey Then lResult = CryptDestroyKey(lHCryptKey)

'Destroy hash object.

If lHHash Then lResult = CryptDestroyHash(lHHash)

'Release provider handle.

If lHCryptprov Then lResult = CryptReleaseContext(lHCryptprov, 0)

Select Case bValid

Case True

lStatus = CFB_VALID

Case Else

lStatus = CFB_READY

End Select

Exit Sub

ErrValidate:

MsgBox ("ErrValidate " & Error$)

Resume

End Sub

Public Sub Encrypt()

'Encrypt InBuffer into OutBuffer

Dim lHExchgKey As Long, lHCryptprov As Long, lHHash As Long, lHkey As Long

Dim lResult As Long, sContainer As String, sProvider As String, sCryptBuffer As String

Dim lCryptLength As Long, lCryptBufLen As Long

On Error GoTo ErrEncrypt

'switch Status property

lStatus = CFB_BUSY

'Get handle to the default provider

sContainer = vbNullChar

sProvider = vbNullChar

sProvider = MS_DEF_PROV & vbNullChar

If Not CBool(CryptAcquireContext(lHCryptprov, ByVal sContainer, ByVal sProvider, PROV_RSA_FULL, 0)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptAcquireContext!")

GoTo Done

End If

'Create a hash object.

If Not CBool(CryptCreateHash(lHCryptprov, CALG_MD5, 0, 0, lHHash)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptCreateHash!")

GoTo Done

End If

'Hash in the password data.

If Not CBool(CryptHashData(lHHash, sPassword, Len(sPassword), 0)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptHashData!")

GoTo Done

End If

'Derive a session key from the hash object.

If Not CBool(CryptDeriveKey(lHCryptprov, ENCRYPT_ALGORITHM, lHHash, 0, lHkey)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptDeriveKey!")

GoTo Done

End If

'Destroy the hash object.

CryptDestroyHash (lHHash)

lHHash = 0

'Prepare a string buffer for the CryptEncrypt function

lCryptLength = Len(sInBuffer)

lCryptBufLen = lCryptLength * 2

sCryptBuffer = String(lCryptBufLen, vbNullChar)

LSet sCryptBuffer = sInBuffer

'Encrypt data

If Not CBool(CryptEncrypt(lHkey, 0, 1, 0, sCryptBuffer, lCryptLength, lCryptBufLen)) Then

MsgBox ("bytes required:" & CStr(lCryptLength))

MsgBox ("Error " & CStr(GetLastError) & " during CryptEncrypt!")

'GoTo Done

End If

sOutBuffer = Mid$(sCryptBuffer, 1, lCryptLength)

Done:

'Destroy session key.

If (lHkey) Then lResult = CryptDestroyKey(lHkey)

'Release key exchange key handle.

If lHExchgKey Then CryptDestroyKey (lHExchgKey)

'Destroy hash object.

If lHHash Then CryptDestroyHash (lHHash)

'Release provider handle.

If lHCryptprov Then lResult = CryptReleaseContext(lHCryptprov, 0)

'switch Status property

lStatus = CFB_READY

Exit Sub

ErrEncrypt:

MsgBox ("ErrEncrypt " & Error$)

Resume

End Sub

Public Sub Decrypt()

'Decrypt InBuffer into OutBuffer

Dim lHExchgKey As Long, lHCryptprov As Long, lHHash As Long, lHkey As Long

Dim lResult As Long, sContainer As String, sProvider As String

Dim sCryptBuffer As String, lCryptBufLen As Long, lCryptPoint As Long

Dim lPasswordPoint As Long, lPasswordCount As Long

On Error GoTo ErrDecrypt

'switch Status property

lStatus = CFB_BUSY

'Init sOutBuffer

sOutBuffer = ""

'Get handle to the default provider.

sContainer = vbNullChar

sProvider = vbNullChar

sProvider = MS_DEF_PROV & vbNullChar

If Not CBool(CryptAcquireContext(lHCryptprov, ByVal sContainer, ByVal sProvider, PROV_RSA_FULL, 0)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptAcquireContext!")

GoTo Done

End If

'Create a hash object.

If Not CBool(CryptCreateHash(lHCryptprov, CALG_MD5, 0, 0, lHHash)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptCreateHash!")

GoTo Done

End If

'Hash in the password data.

If Not CBool(CryptHashData(lHHash, sPassword, Len(sPassword), 0)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptHashData!")

GoTo Done

End If

'Derive a session key from the hash object.

If Not CBool(CryptDeriveKey(lHCryptprov, ENCRYPT_ALGORITHM, lHHash, 0, lHkey)) Then

MsgBox ("Error " & CStr(GetLastError) & " during CryptDeriveKey!")

GoTo Done

End If

'Destroy the hash object.

CryptDestroyHash (lHHash)

lHHash = 0

'Prepare sCryptBuffer for CryptDecrypt

lCryptBufLen = Len(sInBuffer) * 2

sCryptBuffer = String(lCryptBufLen, vbNullChar)

LSet sCryptBuffer = sInBuffer

'Decrypt data

If Not CBool(CryptDecrypt(lHkey, 0, 1, 0, sCryptBuffer, lCryptBufLen)) Then

MsgBox ("bytes required:" & CStr(lCryptBufLen))

MsgBox ("Error " & CStr(GetLastError) & " during CryptDecrypt!")

GoTo Done

End If

'Apply decrypted string from sCryptBuffer to private buffer for OutBuffer property

sOutBuffer = Mid$(sCryptBuffer, 1, Len(sInBuffer))

Done:

'Destroy session key.

If (lHkey) Then lResult = CryptDestroyKey(lHkey)

'Release key exchange key handle.

If lHExchgKey Then CryptDestroyKey (lHExchgKey)

'Destroy hash object.

If lHHash Then CryptDestroyHash (lHHash)

'Release provider handle.

If lHCryptprov Then lResult = CryptReleaseContext(lHCryptprov, 0)

'switch Status property

lStatus = CFB_READY

Exit Sub

ErrDecrypt:

MsgBox ("ErrDecrypt " & Error$)

GoTo Done

End Sub

Public Property Get Status() As Long

Status = lStatus

End Property

Private Function InitUser() As Long

Dim lHCryptprov As Long, lHCryptKey As Long, avProviderData(1000) As Byte

Dim lProviderDataAddress As Long, lProviderDataLen As Long, lDataSize As Long

Dim lResult As Long, sContainer As String, sProvider As String

Dim sUserName As String, lPoint As Long, lMemHandle As Long

Dim lReturn As Long, sBuffer As String

On Error GoTo ErrInitUser

'prepare string buffers

sContainer = vbNullChar

sProvider = MS_DEF_PROV & vbNullChar

'Attempt to acquire a handle to the default key container.

If Not CBool(CryptAcquireContext(lHCryptprov, ByVal sContainer, ByVal sProvider, PROV_RSA_FULL, 0)) Then

'Create default key container.

If Not CBool(CryptAcquireContext(lHCryptprov, ByVal sContainer, ByVal sProvider, PROV_RSA_FULL, CRYPT_NEWKEYSET)) Then

MsgBox ("Error creating key container! " & CStr(GetLastError))

Exit Function

End If

'Get name of default key container.

lProviderDataLen = Len(avProviderData(0)) * (UBound(avProviderData) + 1)

If Not CBool(CryptGetProvParam(lHCryptprov, PP_CONTAINER, avProviderData(0), lProviderDataLen, 0)) Then

MsgBox ("Error getting user name! " & CStr(GetLastError))

avProviderData(0) = 0

End If

'Get sUserName from avProviderData()

lPoint = LBound(avProviderData)

While lPoint <= UBound(avProviderData)

If avProviderData(lPoint) <> 0 Then

sUserName = sUserName & Chr$(avProviderData(lPoint))

Else

lPoint = UBound(avProviderData)

End If

lPoint = lPoint + 1

Wend

MsgBox ("Create key container " & sUserName)

End If

'Attempt to get handle to signature key

If Not CBool(CryptGetUserKey(lHCryptprov, AT_SIGNATURE, lHCryptKey)) Then

If GetLastError = NTE_NO_KEY Then

MsgBox ("Create key exchange key pair")

If Not CBool(CryptGenKey(lHCryptprov, AT_SIGNATURE, 0, lHCryptKey)) Then

MsgBox ("Error during CryptGenKey! " & CStr(GetLastError))

Exit Function

Else

lResult = CryptDestroyKey(lHCryptprov)

End If

Else

MsgBox ("Error during CryptGetUserKey! " & CStr(GetLastError))

Exit Function

End If

End If

'Attempt to get handle to exchange key

If Not CBool(CryptGetUserKey(lHCryptprov, AT_KEYEXCHANGE, lHCryptKey)) Then

If GetLastError = NTE_NO_KEY Then

MsgBox ("Create key exchange key pair")

If Not CBool(CryptGenKey(lHCryptprov, AT_KEYEXCHANGE, 0, lHCryptKey)) Then

MsgBox ("Error during CryptGenKey! " & CStr(GetLastError))

Exit Function

Else

lResult = CryptDestroyKey(lHCryptprov)

End If

Else

MsgBox ("Error during CryptGetUserKey! " & CStr(GetLastError))

Exit Function

End If

End If

'release handle to provider

lResult = CryptReleaseContext(lHCryptprov, 0)

InitUser = True

Exit Function

ErrInitUser:

MsgBox ("ErrInitUser " & Error$)

Resume

End Function

Private Sub Class_Initialize()

If InitUser = True Then

MsgBox ("InitUser OK")

Else

MsgBox ("InitUser failed")

End If

End Sub

Public Property Get Password() As String

Password = sPassword

End Property

Public Property Let Password(vNewValue As String)

sPassword = vNewValue

End Property

Open in new window

0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 75 total points
Comment Utility
to my mind , the issue is not the encryption but how you decide the key to use.

one option would be for the local app to ask or generate a key, which is then sent to the server and used to generate the encrypted data, which is then sent back.

when the user wanted to access the data , they would provide the same key.

see EE Q28383566 where we used an AES method in Excel vba to generate and decrypt  a text string.

if you used this method, incorporating at server level would be easy enough and code is there for excel client.
0
 
LVL 61

Assisted Solution

by:btan
btan earned 225 total points
Comment Utility
I was thinking if you can identified a dedicated folder for the working of workflow of the file and make the folder to be EFS which is encrypted based on specific login account, the encryption is transparent per se. Or simply has truecrypt to create a container or secure volume which it can be loaded upon login and the apps used that mapped secure volume to work through what is needed on the data from the SQL - any data outside the secure container, folder or volume will be its native data format created. Excel stored in those container is encrypted and encrypted on the fly.

just thinking aloud to reduce coding where possible.
0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 150 total points
Comment Utility
The question I guess is do you want to encrypt the file or the contents within the file?  (EG: Still have it opened in excel but the data doesn't make sense or encrypt the file itself so that it's not openable?)

What if you pushed the dependancies to the server? User can encrypt a file, email it.

Users that receive the file would need to upload the file and then receive an unencrypted downloadable file over https.  

I agree that encryption is encryption but it's what do you have access to and what makes sense for the users.  It wouldn't make sense to pass both lock (the encrypted file) and key ( the password) in the same email.  

Can we get some more use cases around the locally encrytped section?
0
 
LVL 61

Assisted Solution

by:btan
btan earned 225 total points
Comment Utility
for content field encrypt and decrypt, maybe this snipplets may be of interest, just that I dont really recommend the hardcoded key for the encrypt and decrypt, maybe a keyfile will be good or password based (that may be interactive though).

The Username or Password will be first encrypted using AES Symmetric key (Same key) algorithm and then will be stored in the database. And while fetching it will be again decrypted using AES Algorithm using the same key that was used for encryption.
0
 
LVL 2

Author Comment

by:codequest
Comment Utility
Thanks for the inputs.  Many good ideas.  It's clear that there are a variety of solutions, so there is do-ability here.  I need to think about the use cases that are made possible by these options.   For practical purposes, this is answered, so I'm rewarding points now.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Explore the encryption capabilities built into Google Apps and how these features can help you meet privacy policy and regulatory compliance, but are not a full solution. Understand and compare the most popular email encryption services for Google A…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now