I need to encrypt few column level data in multiple tables in SQL server 2014. I'm a little confused after reading an article on Encrypt a column of data from the microsoft forum. Do I need to create a new column in the table for encrypted data? I mean is it possible to encrypt the existing column instead of creating a new column for encrypted data? Say Column A has a credit card information which I need to encrypt. Per the article there is a need to create Column B which will store the encrypted credit card information. Is it possible to do an encryption on column A instead of creating extra Column B. Thanks
Column level data encryption in SQL Server 2014
1.2k Views Asked by LearningMacro At
1
There are 1 best solutions below
Related Questions in SQL
- Can MVC.NET prevent SQL-injection at razor or controller level?
- SQL server not returning all rows
- When dealing with databases, does adding a different table when we can use a simple hash a good thing?
- Creating a parametrized field name for a SELECT clause
- Combine two rows based on common ID
- Column displays each count
- Slick query for one to optional one (zero or one) relationship
- Aggregate and count in PostgreSQL
- MAX and GROUP BY - SQL
- SQL statement for a tricky 2 table query
- How to create nested selects with sql?
- Pull and push data from and into sql databases using Excel VBA without pasting the data in Excel sheets
- Best Practice for adding columns to a Table in Oracle database
- SQL FIFO STACK using two tables
- SQL Query - Order by String (which contains number and chars)
Related Questions in ENCRYPTION
- How to customize the output of the Postgres Pseudo Encrypt function?
- encrypted email with entrust certificate is not opening with MS Outlook
- Encrypting with Crypto Node.js and decrypt with window.crypto in Service-Worker
- How to decrypt identity section in web config?
- An exception of type 'System.Security.Cryptography.CryptographicException': keyset does not exist
- IBM DB2 native encryption applied on live database
- crypto.BadPaddingException: data hash wrong (EKYC-Response)
- searchable row level encryption using java?
- AES 256 and Base64 Encrypted string works on iOS 8 but truncated on iOS 7
- Decrypted string returns "Length of the data to decrypt is invalid"
- Storing Encryption Key in Application
- Decryption password Encrypted using Encryptbypassphrase of SQL Server in Java
- Using HTTPS or encrypt response myself
- Encrypting (large) files in PHP with openSSL
- Writing a code to decrypt message from a text file
Related Questions in SQL-SERVER-2014
- Creating a parametrized field name for a SELECT clause
- Data streams in case of Merge
- Cannot login with new SQL User - SQL 2014
- SQL Server 2012 or 2014 Failover Cluster - Change Instance Port Number
- If numeric then Insert numeric else Insert non-numeric
- How to Handle DATEDIFF(MINUTE, '00:00', '24:20') Like scenario?
- DateTime sent in wrong format to Sql Server 2014
- joining two tables by value of one column and calculating
- SqlCommandBuilder() creates insert/update for underlying tables instead for a view
- GROUP BY with HAVING clause does not return a row?
- OpenQuery and using dynamic SQL
- Tricky matching event to office hours in TSQL
- how to migrate or copy SSRS datasource from one server to another without restoring the report server
- IN Memory Oltp hash index vs non clustered
- SQL bypass REPLACE by CASE statement
Related Questions in DATABASE-SECURITY
- Column level data encryption in SQL Server 2014
- Password protect sql database backups in maintenance plan
- Forgot my password for secure database
- SQLite Database Security and Tampering
- How to disable remote connections to MongoDB?
- Script the granting of server roles to a SQL Server Login
- Connecting to an encrypted database after changing encryption key in OrientDB
- How to do SQL Server database back up and recovery in MVC 4
- MySQL escaped_strings VS. Parameterised Queries
- Creating a master key in SQL Server
- hide database password in zend framework 2
- Can I protect T-SQL business logic from SQL Server database administrators and owners
- Multitenancy with Database connection using credentials to achieve pure isolation and increases security
- password_verify_function not used?
- security problems with passing javascript variable to a php variable
Trending Questions
- UIImageView Frame Doesn't Reflect Constraints
- Is it possible to use adb commands to click on a view by finding its ID?
- How to create a new web character symbol recognizable by html/javascript?
- Why isn't my CSS3 animation smooth in Google Chrome (but very smooth on other browsers)?
- Heap Gives Page Fault
- Connect ffmpeg to Visual Studio 2008
- Both Object- and ValueAnimator jumps when Duration is set above API LvL 24
- How to avoid default initialization of objects in std::vector?
- second argument of the command line arguments in a format other than char** argv or char* argv[]
- How to improve efficiency of algorithm which generates next lexicographic permutation?
- Navigating to the another actvity app getting crash in android
- How to read the particular message format in android and store in sqlite database?
- Resetting inventory status after order is cancelled
- Efficiently compute powers of X in SSE/AVX
- Insert into an external database using ajax and php : POST 500 (Internal Server Error)
Popular Questions
- How do I undo the most recent local commits in Git?
- How can I remove a specific item from an array in JavaScript?
- How do I delete a Git branch locally and remotely?
- Find all files containing a specific text (string) on Linux?
- How do I revert a Git repository to a previous commit?
- How do I create an HTML button that acts like a link?
- How do I check out a remote Git branch?
- How do I force "git pull" to overwrite local files?
- How do I list all files of a directory?
- How to check whether a string contains a substring in JavaScript?
- How do I redirect to another webpage?
- How can I iterate over rows in a Pandas DataFrame?
- How do I convert a String to an int in Java?
- Does Python have a string 'contains' substring method?
- How do I check if a string contains a specific word?
In almost every case, you have to create a new column for encrypted data. Encrypted data is stored in SQL Server as varbinary type. Plaintext data is almost always in varchar or some other character type. In your case, your ColumnA is probably varchar(16) or something like that while ColumnB is probably varbinary(128).
You can and should drop the original column after the encryption process is complete else you are still exposed to most security risks. You can also rename the new varbinary type column that holds the ciphertext of the sensitive data to the original column name if you like. Just remember that it now holds data in ciphertext instead of the original plaintext.
If your source type is also varbinary and has sufficient length to store the newly encrypted data, you could do an in-place encryption but the risk there is if you change your mind or discovered a bug in your code during or shortly after execution, you don't have a quick & easy way to back out of the changes. You also lose the ability to do side-by-side testing/verification when you encrypt in place. Finally, you probably won't have too many varbinary columns that require encryption, hopefully.