How to encrypt data in SQL Server databases

How to encrypt data in SQL Server databases

To prevent others from accessing the database from external sources and steal other important information such as the user name, password, and credit card number in the database, when we create a database-driven solution, the first design decision we need to consider is how to encrypt and store data so as to ensure its security and avoid being snooped by others.

Which of the following SQL server support can be used to encrypt objects and data? It is wise to discuss from the very beginning what SQL Server lacks, or what you should not do for the encryption part of SQL Server.

First, SQL Server has two built-in cryptographic functions: PWDENCRYPT () and pwdcompare (). At the same time, there are two functions that SQL server does not officially record to manage the password hash: PWDENCRYPT () stores the password after hash; pwdcompare () compare the provided string with the hash string. Unfortunately, this hash function is not very secure and can be attacked through dictionaries.AlgorithmCracked (similar to command line applicationsProgram!).

These functions are constantly modified with the development of the SQL Server version, which is another reason for not using them. The hash of passwords in earlier versions of SQL Server cannot be decrypted in later versions. Therefore, if you depend on functions in a previous version, all your encrypted data is useless unless you can decrypt it first-This violates the original purpose of encryption.

Second, you may try to create a self-made encryption solution for your database, but there are three reasons for this:

Unless you are an encryption expert, a random encryption system will only provide very low-level protection with low value. What's new is that one-way password hashing or "rotx" encryption can be easily defeated without any effort.

If encryption is cracked due to your lack of capabilities, your data will be ruined. You need to back up everything without encryption, right? (Is there a security vulnerability even if you encrypt it ?)

When there is a professional-level, industrial-intensive encryption solution available on the market, it is not worth the time to do it on your own. Use your time to build a good, strong database, instead of re-inventing the wheel.

So what is a good way to encrypt data?

For beginners, Microsoft provides its own encryption solution, CryptoAPI. For lightweight encryption, military-Level Security is beyond consideration. It has the advantage of relatively easy implementation: the administrator can install an ActiveX control named CAPICOM, it provides CryptoAPI functionality in T-SQL stored procedures. CAPICOM supports various types of bidirectional encryption and one-way hash algorithms. Therefore, administrators can select the most suitable part for application problems.

If you are not interested in using Microsoft's solutions, there are some good third-party solutions available. A software company named activecrypt has created xp_crypt, a plug-in for SQL Server, encryption can be completed in views, programs, and triggers by extending stored procedures and user-defined functions (in SQL Server 2000. You can download a free version of the application that Supports Wireless MD5, Des, and sha1 hashing. Other encryption models are implemented in BIT depth. (The full version is infinite .) In your ownCodeIn, you can use xp_crypt, which is the same as ActiveX control (in a limited free version ). For ASP programmers, a component named aspencrypt provides a simple way to integrate advanced encryption into your code.

How about encrypting database files or providing security protection on the transport layer? For the former, you can continue to use the encrypted file system in windows. However, you must save the backup of the encryption key. In case of any problems, this data may be lost. For the latter, there are IPSec and SQL Server's own SSL encryption, which is the main focus of both SQL Server and windows. We should avoid storing sensitive data in plain text, because extracting unencrypted data from the database is also the most vulnerable to attacks.