Post reply

Encrypt Varchar(max) column in SQL Server

  • June 8, 2023 at 4:00 pm

    #4207152

    I have a varchar(max) column which has sensitive data and want to encrypt it. I used ENCRYPTBYPASSPHRASE but its failing because its not able to encrypt if the number of characters is more than 8000. can you please help me what other options I have to encrypt the column in SQL Server table. I am using SQL Server 2022.

  • June 8, 2023 at 5:08 pm

    #4207208

    What are you trying to accomplish with the encryption? Is it to protect confidentiality of the data at rest, from other administrators, from application users, or something else? Does it really need to be encrypted or could it just be obscured?

    Whatever the case, I would not recommend using SQL server to do encryption on blobs, if you have to do that, would instead have the application do it then write the encrypted blob to the DB.

  • July 20, 2023 at 7:00 am

    #4239841

    In SQL server you have limitation with varchar(max) column and want to encrypt it and as well ENCRYPTBYPASSPHRASE has a limitation that it can only encrypt data up to 8000 bytes.

    You can also use another approach:

    1. Use a Symmetric Key: SQL Server gives CREATE SYMMETRIC KEY statement to create a symmetric key that you can use for encryption and decryption.

    CREATE SYMMETRIC KEY MySymmetricKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'YourStrongSymmetricKeyPassword';

    2. Using a certificate as well can help you to encrypt and decrypt data with SQL server instances.

    I hope it works for you!!

     

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply