Conversation started Sep 26, 2012 at 19:13.
Royi Namir
Royi Namir
Sep 26, 2012 19:13
hello. ( sql server - bak file). if I set complex permissions to my datqabase to users and I backup this database into 1.bak.
now lets say this bak file gets to john's hands. if john , opens it by restore to his own computer - will he have full access to everything ?
(ofcourse - john is an administrator in his own computer)
swasheck
swasheck
if john included himself in the sysadmin role and/or if john installed on his local machine and knows the sa password then i believe so.
Phil
Phil
Have you stolen a DB and want to know how to restore it? ;)
swasheck
swasheck
@Phil elephant in the room
Royi Namir
Royi Namir
@phil , lol no man just for knowledge
@swasheck does john has to know anything ?
he can resotre the db and add himslef to the admin group
no?
swasheck
swasheck
@RoyiNamir 1) he has to have sql server installed in a location that he has such permissions (e.g. a local SQL instance with version match)
2) he has to know the sa password or be in a role that has uninhibited access on that instance to be able to restore and then access the objects
that's my understanding ... but i may be wrong
Royi Namir
Royi Namir
Sep 26, 2012 19:20
I was told - that when i got a bak file - i can have access for all.
no password - no nothing
maybe they were wrong , or maybe i got it wrong
swasheck
swasheck
Sep 26, 2012 19:38
if you're in a role that allows such things on a sql server, then yeah
Royi Namir
Royi Namir
cant I just add myself as a new account which has full permissions ?
hello @AaronBertrand Can you please give your opinion ?
jcolebrand
jcolebrand
@AaronBertrand in this case, would it be any faster to find the index ID value for the max value you want to delete/keep (the split point in other words) and then just delete where ID less than found ID?
swasheck
swasheck
@RoyiNamir if you've got access to an account that will allow you to give those permissions to your account ... but then that seems a bit redundant
jcolebrand
jcolebrand
@RoyiNamir if you're able to acquire a non-password-protected full backup of the database (sort of thing is kept to automated systems with encrypted systems and automated passwords specifically to keep people from having copies) then you're a) inside the firewall, b) inside the network, and c) have full trusted permissions. (even if you've socially engineered or hacked)
At that point, why shouldn't you have full access to the data?
Now, if your question is how do you prevent that ... we can help with that, but it's hard.
(as all good security is)
What you can do instead is to set encrypted backups
@RoyiNamir hello are you here still?
Royi Namir
Royi Namir
@jcolebrand thanks for answering
ofcourse
it si just a knowledge question
not how to prevent...
jcolebrand
jcolebrand
Sep 26, 2012 19:46
Just helping to make sure we cover all your bases for you so you understand
Royi Namir
Royi Namir
so basically - if i have a bak file in my disk on key - I can see all the data
jcolebrand
jcolebrand
Do you understand that a "bak file" doesn't have to be "protected" it can just be "raw"?
Mark Storey-Smith
Mark Storey-Smith
@RoyiNamir yes
jcolebrand
jcolebrand
just like a ZIP doesn't have to have a password, it can be an archive, or it can have a password
theprofoundprogrammer.com/post/32328477984/…
@balpha we really need to add this to the easter-egg for the room
Royi Namir
Royi Namir
ok so now lets go into the real question.
CREATE SYMMETRIC KEY SecureSymmetricKey
WITH ALGORITHM = DESX
ENCRYPTION BY PASSWORD = N'StrongPassword';
lets say i want to encrypt every value which comes to mySp
this is actually the code which is in my SP.
Mark Storey-Smith
Mark Storey-Smith
Sep 26, 2012 19:48
@RoyiNamir You know we've got this dba.se site where people can ask questions about database stuff? :)
5
SQLkiwi
SQLkiwi
@MarkStorey-Smith LOL!
jcolebrand
jcolebrand
@MarkStorey-Smith details!
I figured he got a one-off freebie
Royi Namir
Royi Namir
@Mark yeah I know but when I asked it once - I didnt got the 100% understanding dba.stackexchange.com/questions/7209/…
Mark Storey-Smith
Mark Storey-Smith
What did the accepted answer not cover or what part of the answer are you unsure about?
Royi Namir
Royi Namir
If I got the bak file , and as youve said , I have access to all , So the certificates wont help....I still can see the whole data...right?
(still , not talking about encryption)
jcolebrand
jcolebrand
Sep 26, 2012 19:53
That is actually a complicated question. And because it will help someone in the future, let's go ahead and move to a for-real Q on Database Administrators
Before you post a Q, however, let's review what you need:
Royi Namir
Royi Namir
thanks for guiding.
jcolebrand
jcolebrand
Start with "So I was asking in chat [link to conversation] about how secure backup files are. I'm told that if an attacker has access to the .bak file unencrypted that they can have access to the data. So what if we used [code from above], would they still be able to access the data if the [continue from what you were just asking]"
Royi Namir
Royi Namir
title ? :-)
Mark Storey-Smith
Mark Storey-Smith
@RoyiNamir If you have access to a bak file that is NOT encrypted, you can access everything in it. If it contains encrypted data, you'll need the certificate that was used to encrypt, which won't be in the backup file.
swasheck
swasheck
@MarkStorey-Smith this
jcolebrand
jcolebrand
Sep 26, 2012 19:57
@RoyiNamir "Understanding MS SQL Server encryption and backups" ???
Just toss something on it, one of the cleverer of our lot will ensure that it gets a good title.
Royi Namir
Royi Namir
0
Q: Understanding MS SQL Server encryption and backups

Royi NamirSo I was asking in chat http://chat.stackexchange.com/rooms/179/the-heap about how secure backup files are. I'm told that if an attacker has access to the .bak file unencrypted that they can have access to the data. So what if we used OPEN SYMMETRIC KEY MySymetricKey DECRYPTION BY CERTIFICATE...

sql-server security
 
Conversation ended Sep 26, 2012 at 20:03.