SQL Server Credit Card Encryption and Decryption

Problem
Credit card data is very sensitive and has a variety of regulations around the globe.  For our application, the business requirement is not to store this data in clear text in the customers table in the database, so we are looking for options to store only the hashed and encrypted value. The reason for storing the encrypted value of the credit card in the customers table in the database is to give the customer the option to re-use the card without having to retype the card’s value again as input. The system needs only the customer ID, the encrypted card number value and the secret phrase in order to recalculate the real credit number.  How can we address this need?

Solution
First and foremost, be sure you are well aware of the legal requirements to store sensitive data prior to considering any solution.  For our application, the method for solving this problem involves an encryption T-SQL stored procedure and a T-SQL decryption function. These objects will use the built-in SQL Server ENCRYPTBYPHRASE and DECRYPTBYPASSPHRASE functions.  The stored procedure will get a secret phrase that is not stored in the database along with the card number as input.

There are two items that are important to note for the Customers table:

This method of using a T-SQL stored procedure for the encryption process and a T-SQL Scalar UDF for the decryption process is good for generalizing all calls to the built-in SQL Server ENCRYPTBYPHRASE and DECRYPTBYPASSPHRASE functions in the application code. Another advantage for this option is that these objects can be encrypted using the WITH ENCRYPTION option and to further improve the sec

SQL Server Encryption Stored procedure

 

This method of using a T-SQL stored procedure for the encryption process and a T-SQL Scalar UDF for the decryption process is good for generalizing all calls to the built-in SQL Server ENCRYPTBYPHRASE and DECRYPTBYPASSPHRASE functions in the application code. Another advantage for this option is that these objects can be encrypted using the WITH ENCRYPTION option and to further improve the sec