SQL Server Data...is it encrypted?

Question

Post reply

SQL Server Data...is it encrypted?

water490

SSC Eights!

Points: 987
More actions
January 11, 2025 at 4:29 am
Go to Answer
#4513614

Hi
Suppose someone gained unauthorized access to the physical SS data stored on my machine. Is the data encrypted? Will the user need a password to access the data? Here is how I login to SS:
Thank you
Grant Fritchey

SSC Guru

Points: 398926
More actions
January 13, 2025 at 2:33 pm
Answer
#4514762
You can check the encryption status of your databases:
```
SELECT 
db.name AS database_name, 
db.is_encrypted, 
dm.encryption_state, 
dm.percent_complete, 
dm.key_algorithm, 
dm.key_length 
FROM 
sys.databases db 
LEFT OUTER JOIN 
sys.dm_database_encryption_keys dm 
ON 
db.database_id = dm.database_id;
```
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182935 More actions · Answer 1

Without further information, there is no way one can answer your question!

😎

1. Are you using any hard drive encryption?

2. Are you using TDE, transparent data encryption on the server/database level?

3. Have you done any attack vector assessments?

4-100. Why,What,How..... are you protecting and managing the access to your system and the data at the moment?

Jeff Moden SSC Guru Points: 1004457 More actions · Answer 2

My suggestion is that you need to stop taking chances and hire a consultant to set you up correctly because it sounds like you're not just storing personal data (which can certainly make a mess for you) but maybe some company information, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

water490 SSC Eights! Points: 987 More actions · Answer 3

Eirikur Eiriksson wrote:

Without further information, there is no way one can answer your question! 😎 1. Are you using any hard drive encryption? 2. Are you using TDE, transparent data encryption on the server/database level? 3. Have you done any attack vector assessments? 4-100. Why,What,How..... are you protecting and managing the access to your system and the data at the moment?

good questions

My concern is that if my computer is ever stolen then an authorized person may access my SS db because he will have physical access to my drive. I have bitlocker enabled on the drive but not sure how secure this is (ie, is there a backdoor...probably?). Other than this, I don't have any intentional encryption set. I am wondering what encryption, by default, does SS have? Is there any encryption by default? I provided the screenshot to show how I currently access SS. Any feedback you can provide is much appreciated.

Eirikur Eiriksson SSC Guru Points: 182935 More actions · Answer 4

water490 wrote:

My concern is that if my computer is ever stolen then an authorized person may access my SS db because he will have physical access to my drive. I have bitlocker enabled on the drive but not sure how secure this is (ie, is there a backdoor...probably?). Other than this, I don't have any intentional encryption set. I am wondering what encryption, by default, does SS have? Is there any encryption by default? I provided the screenshot to show how I currently access SS. Any feedback you can provide is much appreciated.

If the hard drive has Bitlocker then it is pretty safe to assume that the data is secure, given that the user authentication is robust enough (password, fingerprint, security token etc.)

😎

If anyone who can break the Bitlocker encryption gets hold of your system, they can probably bypass the Windows authentication anyway.

For database encryption, start by reading this documentation, Transparent data encryption (TDE).

water490 SSC Eights! Points: 987 More actions · Answer 5

Grant Fritchey wrote:

You can check the encryption status of your databases:

SELECT 
db.name AS database_name, 
db.is_encrypted, 
dm.encryption_state, 
dm.percent_complete, 
dm.key_algorithm, 
dm.key_length 
FROM 
sys.databases db 
LEFT OUTER JOIN 
sys.dm_database_encryption_keys dm 
ON 
db.database_id = dm.database_id;

Thank you! This is exactly what I was looking for.