Hot answers tagged

14

Your problem boils down to access control. The first defense I'd propose is to simply deny access to the untrusted users. If they can't get into the database, they can't query the database and get at the sensitive data. If they must be allowed to access the database server, you can look at either explicitly granting them read permission to the tables they ...


9

The attacker will get the data unencrypted. The T in TDE stands for "transparent". The user will never see encrypted data. The database transparently decrypts it when it is read from disk and transparently encrypts it when writing to disk. If your application is insecure, TDE doesn't help you plug those application security holes. You need to fix those ...


8

Q1a: Is the master key password created per DB instance? A1a: Assuming the question really means "Is the master key created per DB?"" then answer is Yes. Each DB has an different master key. there is also a thing called the service master key, which is per SQL Server instance. Q1b: When I backup that DB (.bak) will I be able to restore the DB to ...


7

\df *crypt in psql reveals the argument types of the pgcrypto encrypt and decrypt functions (as do the PgCrypto docs): List of functions Schema | Name | Result data type | Argument data types | Type --------+-----------------+------------------+--------------------------+-------- ... public | decrypt ...


7

The only way to use encryption to protect the data against your own administrators/IT is when the user enters the decryption password him/herself, every time it queries the data. If your application presents the user with a password dialog and then issues an OPEN SYMMETRIC KEY ... DECRYPTION BY CERTIFICATE ... WTIHT PASSWORD ... (or some equivalent) to open ...


7

The problem with cell level encryption is that the column itself isn't really encrypted, it's the data contained in that column. The columns themselves are just varbinary columns (because that's what's required) and could contain completely legible data. It's the use of the ENCRYPTBY* and DECRYPTBY* functions that truly make the data encrypted. You can ...


7

SQL Server supports SSL 3.0 and TLS 1.0 only, you must have at least one of them enabled or SQL Server will not be able to start. SQL Server does not support TLS 1.1, 1.2 etc, it specifically uses TLS 1.0. Your options here are to enable SSL 3.0 and/or TLS 1.0, no other way around it I'm afraid. Cheers Mat


6

As in the other answer: you need a recent CU for TLS1.2. See https://support.microsoft.com/en-us/kb/3052404: Cumulative Update 8 for SQL Server 2014 Cumulative Update 1 for SQL Server 2014 SP1 Cumulative Update 6 for SQL Server 2012 SP2 Cumulative Update 7 for SQL Server 2014 After enabling only TLS 1.2 you will possibly encounter two errors: SQL ...


6

As a rule of thumb: If your data is well structured, well known (in advance) and of a limited size per entry (no mega BLOBs), relational databases are really good at storing it. Even if you don't use the advanced indexing features. Managing space, especially empty space in data files, is a very hard problem to solve. Relational databases have been dealing ...


6

No, this is not true, and there is an easy proof. On one server, create a database, and store some data using ENCRYPTBYPASSPHRASE(): CREATE DATABASE blat; GO USE blat; GO CREATE TABLE dbo.mort(floob INT, splunge VARBINARY(64)); GO INSERT dbo.mort VALUES (1, ENCRYPTBYPASSPHRASE(N'kerplunk', N'secret')), (2, ENCRYPTBYPASSPHRASE(N'kerplunk', N'hidden')); ...


5

If you create keys without a certificate like: CREATE SYMMETRIC KEY smTestKey WITH ALGORITHM=AES_256 , IDENTITY_VALUE = 'Key to protect bla' , Key_SOURCE = N'Secret pass phrase' ENCRYPTION BY PASSWORD = 'secret password'; then the following is enough: GRANT ALTER ANY SYMMETRIC KEY TO dbuser If you create symmetric keys that's encrypted by a ...


5

Assuming you are talking about data that is encrypted with SQL Server keys, there is way to find these columns. The Key_name() Function will return the name of the key used for the encryption for that particular value and will return NULL if there isn't anything encrypted with a "known" key (3rd party, or simple not encrypted). With that knowlegde we can ...


5

Since TDE relies on a certificate stored in master (which is used to encrypt the database encryption key), then this would work only work if you could restore the master database to another server in such a way that the certificate could be decrypted. This is the TDE encryption hierarchy: Service master key (protected by Windows; tied to the service ...


5

Why would you want to do this? What risk does it mitigate? This sounds like a great way to lock yourself out of your database in a way that requires downtime to recover. Limit your exposure by securing the servers with firewalls and SSH and keep MySQL authentication simple. If you are worried about brute force attacks from within your trusted network, ...


5

Do not disable TDE. Besides being a lengthy size-of-data operation, it was established initially for a reason and you may be breaking whatever compliance/operations reason exists for TDE. Rather set up log shipping properly in presence of TDE. Follow the steps described in Move a TDE Protected Database to Another SQL Server. You are going to export the TDE ...


4

Whether encrypted RDs is disk/file level encrypted or db level encrypted? I have done some searching, but could bot able to get a clear answer. The encryption is done at the file/disk level. From this page, emphasis mine: Amazon RDS encrypted instances provide an additional layer of data protection by securing your data from unauthorized access to ...


4

You can join on the certificate thumbprint: use master; go select database_name = d.name, dek.encryptor_type, cert_name = c.name from sys.dm_database_encryption_keys dek left join sys.certificates c on dek.encryptor_thumbprint = c.thumbprint inner join sys.databases d on dek.database_id = d.database_id; My sample output: database_name ...


4

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available. I assume that the db1 master key is encrypted with the SMK. This makes everything encrypted by the database master key 'available' to applications, w/o having to explicitly open the database master key. What you need to do ...


4

When you restore a database, TRUSTWORTHY is automatically set to OFF. For Service Broker, if you don't use encryption and do use cross-database message transmission, TRUSTWORTHY needs to be set to ON. Try ALTER DATABASE MyDB SET TRUSTWORTHY ON ...for all restored databases involved with Broker.


4

General guidelines: Server physical security: behind a locked door, with a very short list of people able to access it (as a point of interest, I myself can't access our server room without being escorted). Backup security: Make sure your backups are encrypted (if you use physical media for backups, make sure they're stored with a similar level of security ...


4

This article on msdn indicates it's a new feature in SQL Server 2008. To me that means it's not available in 2005. There are a lot of resources available with step-by-step guides for other data encryption types in 2005, though. I think it's probably beyond the scope of a question on a Q&A site, however.


4

You will need to encrypt the data within the application and store the encrypted values within the database. Things like TDE and storage level encryption will not meet your requirements.


4

Great question! There's a handful of ways of doing this, depending on how deeply you want to be involved in the configuration and long term maintenance. You certainly could use Cassandra on top of an encrypted filesystem, such as TrueCrypt or eCryptfs, and it should function normally. While possible, it's a little complicated to set up encrypted filesystem ...


4

Yes, it is normal. The certificates and keys are part of you database. CREATE CERTIFICATE my_certificate ENCRYPTION BY PASSWORD = 'blahblah' ...; CREATE SYMMETRIC KEY my_symmetric_key ... ENCRYPTION BY CERTIFICATE my_certificate; Your posted code creates a symmetric key encrypted with a certificate encrypted with a password. The master key is not involve ...


4

When you encrypt the first database in the instance, tempdb will also be encrypted. As per this reference on TDE: Transparent Data Encryption and the tempdb System Database The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This will be the case when you set ENCRYPTION ON ...


4

Yes. Transparent Data Encryption doesn't change any of the internal processing of queries. Database pages are transparently encrypted/decrypted during I/O (i.e. when they are read from or written to disk). In-memory query processing is therefore unaffected, so indexes continue to function exactly as they did without TDE. TDE is designed to negate the need ...


3

Look at the encryption hierarchy found in this TechNet article. This document from MS shows the entire hierarchy. You can see that the Master Key is created from the Windows DPAPI service and is used for: -DB Master Key -Certificates -Symmetric Keys -Asymmetric Keys -TDE -Transact SQL Encryption Functions -Passwords (I'm not 100% on this, but it ...


3

No. Scalar operators cannot magically transform themselves into relational operators. You use the ENCRYPTBYPASSPHRASE to encrypt a column (there is no way to 'encrypt a table') and you use DECRYPTBYPASSPHRASE to decrypt a column. Using passphrases to encrypt the data directly is a very very poor choice. A proper key hierarchy is needed where data is ...


3

The specific permissions required are: GRANT CONTROL ON CERTIFICATE :: [cert] TO [user] GRANT VIEW DEFINITION ON SYMMETRIC KEY :: [key] TO [user] However, rather than grant permissions directly on the Certificate and Key, I like to have two stored procedures - one for encryption and one for decryption. We generally have different users that perform the ...



Only top voted, non community-wiki answers of a minimum length are eligible