MS Access 2016 decrypt field that is encrypted in linked ODBC SQL Server table

Question

I need to encrypt SSN (nvarchar(25)) field in a SQL Server database table. Once encrypted, the Access program needs to be able to decrypt the field for user viewing/report printing editing, etc... The value needs to be saved after data entry or editing.

I appreciate any help with this.

Answer 1

If I am understanding your issue correctly it sounds like the data was obfuscated in the SQL server which you are using ODBC (in MS Access) to look at, and you want to be able to see the SSN's properly. If that is the case, you will likely need to get the DBA for the SQL server to give you appropriate permissions. The DBA should be able to use: GRANT UNMASK TO dbuser.

Answer 2

If you control the process from your Access application - both read and write the encrypted SSN value - you can use my functions Encrypt and Decrypt:

' Encrypt a string using AES and a key.
' Return the encrypted text as a Base64 encoded string.
'
' Example:
'   Text = "Careful with that axe, Eugene!"
'   Key = "Have a Cigar"
'   EncryptedText = Encrypt(Text, Key)
'   EncryptedText -> 6uLffExuQmAi/oI3AzCLZTRZfv1XL6kl01z4hJ5y1MWXHgFACj3XhvboF/rNU89znrX1d5btmCbRK9dAjjjlKxTDJMImQr3YGiscMDvn/YtjKmc8nFuR65IU9vEn4a0Rca72k55cZXjKzOGMpbZ/6A==
'
' Note: Length of the encrypted string can be predetermined by the function EncryptedTextLength:
'   ' Use Text from example above.
'   Length = EncryptedTextLength(Len(Text))
'   Length -> 152
'
' Original code by Erik A, 2019.
' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Encrypt( _
    ByVal Text As String, _
    ByVal Key As String) _
    As String

    Dim EncryptedData()     As Byte
    Dim EncryptedText       As String

    If Text = "" Or Key = "" Then
        ' Nothing to do.
    Else
        If EncryptData((Text), (Key), EncryptedData) = True Then
            ' Success.
            ' Convert the byte array to a Base64 encoded string.
            EncryptedText = ByteBase64(EncryptedData)
        Else
            ' Missing Text or Key.
        End If
    End If

    Encrypt = EncryptedText

End Function

' Decrypt a Base64 encoded string encrypted using AES and a key.
' Return the decrypted and decoded text as a plain string.
'
' Example:
'   EncryptedText = "6uLffExuQmAi/oI3AzCLZTRZfv1XL6kl01z4hJ5y1MWXHgFACj3XhvboF/rNU89znrX1d5btmCbRK9dAjjjlKxTDJMImQr3YGiscMDvn/YtjKmc8nFuR65IU9vEn4a0Rca72k55cZXjKzOGMpbZ/6A=="
'   Key = "Have a Cigar"
'   Text = Decrypt(EncryptedText, Key)
'   Text -> Careful with that axe, Eugene!
'
' Original code by Erik A, 2019.
' 2021-10-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Decrypt( _
    ByVal EncryptedText As String, _
    ByVal Key As String) _
    As String

    Dim EncryptedData()     As Byte
    Dim TextData()          As Byte
    
    If EncryptedText = "" Or Key = "" Then
        ' Nothing to do.
    Else
        ' Convert the Base64 encoded string to a byte array.
        EncryptedData = Base64Bytes(EncryptedText)
        If DecryptData(EncryptedData, (Key), TextData) = True Then
            ' Success.
        Else
            ' Invalid EncryptedData or wrong key.
        End If
    End If
    
    Decrypt = TextData

End Function

The encrypted value will be stored Base64 encoded. Similar functions are at hand, should you prefer to store the values binary.

Full documentation:

Encryption in VBA using the Microsoft NG Cryptography (CNG) API

Using binary storage to serve the Microsoft NG Cryptography (CNG) API

Full code: VBA.Cryptography.