SQL encryption and web

Question

User1510859543 posted

Our web customer plans to encrypt their SQL Server 2016 database and was wondering if I needed to make any changes on the web pages for reading and writing to it?

Answer 1

User475983607 posted

Our web customer plans to encrypt their SQL Server 2016 database and was wondering if I needed to make any changes on the web pages for reading and writing to it?

The forum cannot answer this question as we cannot see your code and cannot contact your customer.  I recommend reading the SQL 2016 documents that covers encryption and work with your customer.

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-encryption?view=sql-server-2017

Answer 2

User-719153870 posted

Hi David,

if I needed to make any changes on the web pages for reading and writing to it?

Commonly, if you encrypted one field, then you will need to do the decryption when you select it from your db.

You can find a lot of related articles online, they( encryption and decryption ) usually appear in pairs. For example, Encrypt a Column of Data.

As for the web pages, you might need to make changes in your select command.

To deal with different encryption methods, you need to use different decryption methods.

Best Regard,

Yang Shen

Answer 3

User1510859543 posted

Can I assume that our connection string used to connect to SQL Server will also need updating?  If so, can you point me to help on this? We are planning to use TDE.

Answer 4

User753101303 posted

Hi,

AFAIK you have nothing special to do on the application side (which is why it is called "transparent"). See : https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017#about-tde

The purpose is to have SQL Server encrypting database files on the disk so that if someone can stole them he can't attach and read them on his own server to read your data (is this the kind of protection your customer want ?)

Answer 5

User1510859543 posted

Yes, that is the type of protection desired.

From what I read, if we supply a password in text via parameter from our website that SQL will handle encrypt/decrypt to match to one in database.

Answer 6

User753101303 posted

Humm... I'm not sure how the web site is involved in that. My understanding is that this is purely a server side feature ie all happens as usual expect that SQL Server encrypts data when updating the database files on the disk so that they can't be decrypted if stolen.

Other options I can think of are :
- having values being encrypted (or better hashed) on the client side (commonly used to encrypt hash passwords)
- having some columns being encrypted on the server side
- having some columns being automatically encrypted so that only the configured client side knows to decrypt them
- encrypting the traffic between the web server and the db server
- and likely other stuff I forgot (for example you can "mask" data to application not being allowed ie showing for example the first two digit of a phone number and then x characters) etc...

When putting a safety measure in place it is IMO very important to understand first what you want to prevent exactly...

Edit: "From what I read" : this is about TDE ? Or point us to where you have seen that so that we can better understand your exact intent ?

Edit: from https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017 :

"TDE performs real-time I/O encryption and decryption of the data and log files. [...] This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications."

Edit 3: I wonder if you are not in a case where your customer goal is just to "encrypt data" based on "it is safer" without having really looked at the exact threat against which they want a protection.

Answer 7

User1510859543 posted

The requirements are " the data must be encrypted at rest period for HIPPA"

Which requires the entire db be encrypted.

Answer 8

User753101303 posted

Ok so is TDE but 

"if we supply a password in text via parameter from our website that SQL will handle encrypt/decrypt to match to one in database" seems unrelated.

To me this is a server side encryption and the client side doesn't have to care or know about that...

Answer 9

User475983607 posted

dlchase

The requirements are " the data must be encrypted at rest period for HIPPA"

Which requires the entire db be encrypted.

Your connection string will not change as the encryption is performed at the database level.  The encryption is Transparent to your application.

Below is the fist paragraph of the linked documentation which cleanly and openly explains the purpose of TDE. 

Transparent Data Encryption (TDE) encrypts SQL Server, Azure SQL Database, and Azure SQL Data Warehouse data files, known as encrypting data at rest. You can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned in advance.