Encrypting Table values in SQL Server

Aman Raiyyani
Encrypting Table values in SQL Server

There are several methods to encrypt table values in SQL Server. One of the most common methods is to use built-in encryption functions such as ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE.

Here’s an example of how to encrypt the values in a table column called “CreditCardNumber” using the ENCRYPTBYPASSPHRASE function:

— Create a passphrase

DECLARE @passphrase NVARCHAR(256) = ‘mysecretkey’;

— Create a new column for the encrypted data


ADD EncryptedCreditCardNumber VARBINARY(256);

— Update the table to encrypt the data

UPDATE Customers

SET EncryptedCreditCardNumber = ENCRYPTBYPASSPHRASE(@passphrase, CreditCardNumber);

— Drop the original column


DROP COLUMN CreditCardNumber;

— Rename the encrypted column

EXEC sp_rename ‘Customers.EncryptedCreditCardNumber’, ‘CreditCardNumber’, ‘COLUMN’;

In this example, we first create a passphrase, which is used as the encryption key. We then create a new column called “EncryptedCreditCardNumber” and use the ENCRYPTBYPASSPHRASE function to encrypt the values in the “CreditCardNumber” column. We then drop the original column and rename the encrypted column to its original name.

To decrypt the data, you can use the DECRYPTBYPASSPHRASE function:

— Declare the passphrase

DECLARE @passphrase NVARCHAR(256) = ‘mysecretkey’;

— Decrypt the data

SELECT CreditCardNumber, CAST(DECRYPTBYPASSPHRASE(@passphrase, CreditCardNumber) AS NVARCHAR(50)) AS ‘DecryptedCreditCardNumber’

FROM Customers

It’s worth noting that this method uses symmetric key encryption, which means that the same key is used for both encryption and decryption. Another method is to use Asymmetric key encryption where the encryption key is different from the decryption key.

It’s also important to store the passphrase securely and to backup the encryption keys in a safe place.

It’s highly recommended to use the built-in encryption functions of SQL Server, like Always Encrypted or Transparent Data Encryption (TDE) which provides more secure and robust encryption.

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…