Tag Info

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 ...


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 ...


6

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


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

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

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

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

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 ...


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

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

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

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

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

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 ...


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

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 ...


3

PostgreSQL's built-in two-way encryption functions are documented here. The functions that come in the encrypt/decrypt pairs are the two-way encryption options.


3

I'm not sure about pgPool, but pgBouncer (another connection pool) can use SSL by using Stunnel. Check your functional requirements to see if pgBouncer can solve your problem. Maybe pgPool can also use Stunnel, you could give it a try.


3

Encrypting files through SSIS is possible, but there is no real easy native solution available with SSIS. There are a wealth of third party productions out there than can assist with this. Time is money, so it all depends on how much you want to spend on trying to come up with a customized solution or just go buy something that can help you do it quicker. ...


3

Usually, you don't want to do this. Have you given second thoughts to this? Else, pg_crypto is your friend. But as the above-mentioned slides suggest, it isn't necessarily a panacea.


3

Things that come to my mind: use the contrib module pgcrypto to encrypt the column contents manually (will require a change to your application) Store the data on an encrypted volume/partition (this can be done on table level using tablespaces that are located on the encrypted volume)


3

I think what you are referring to is cell-level encryption, but using this feature requires changing the application. TDE (transparent database encryption) is another option, this encrypts the files on the disk, but they are decrypted in-memory, so anyone with direct access to the machine and sufficient privileges to look into the block buffer cache directly ...


3

Passport numbers should definitely be encrypted in the database. You should investigate the legality of collecting and storing this information before proceeding.


3

TDE is a new feature in SQL Server 2008. You can do column level encryption of the data within the database or in the application tier but that's about it. What's the goal of the encryption? Protecting data in the database? Protecting data in the data file? Protecting data in the backup files? Etc?


3

Short answer: as of April 20, 2012 column encryption is not supported in SQL Azure, see: http://msdn.microsoft.com/en-us/library/windowsazure/ee336253.aspx and search for ENCRYPT. You will see that all of the encryption functions are listed as unsupported. If you need to encrypt your data at rest and have to support SQL Azure then you are going to have to ...


3

I normally do not endorse any one specific product, but in this case I will make an exception. I have personally evaluated a product called Gazzang. It can encrypt data in such a way that when transported to another server, the data is unreadable unless the encryption key is copied and verified on that external server. I have tried this and copied ...



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