April 29, 2008 at 2:19 pm
Has anyone been able to successfully resolve preventing 'hackers' from attaching a database and then being able to view the data in all tables.
I have tested with an sql express 2005 instance - where I have run following when connected using sa (plus I have removed Security Logins for BUILTIN\Administrators and BUILTIN\Users - just from this instance):
use MyDB
create master key encryption by password = 'apwd'
open master key decryption by password = 'apwd'
alter master key drop encryption by service master key
then I have detached MyDB and have been able to re-attach but was able to run any queries fine (either to the same instance (connecting using sa) or a separate SQL Express on the same computer (and connecting with Windows Authentication). I'm not seeing any errors or messages indicating access denied.
I expected that it would have at least not worked successfully to the separate instance.
Have I missed something?
BTW:
The intention is that my application is downloaded and an instance of SQL Express is also installed and during the installation process access to Windows Authentication has been removed by removing Security Logins for BUILTIN\Administrators and BUILTIN\Users - this is what my installation code will do.
However, how do I stop that person on their computer installing their own SQL Express instance and attaching my database to their new SQL Express instance (which will still have BUILTIN\Administrators and BUILTIN\Users - and thus Windows Authentication working).
Also:
I am concerned with competitors viewing my database design. But from reading up stopping this is impossible. So I thought I'd just settle for competitors accessing any data in the tables.
I have two client markets for my application. The one I am addressing here is a low cost downloadable version for the end user and where I would expect there would be no local dba or in fact even a need for a local dba. My concern however, is that my competitors also download my product, but have the skill set to interrogate unethically - my aim is to make this as difficult as possible.
Thanks muchly
April 29, 2008 at 2:41 pm
The database master key is used to encrypt other keys in the DB. It's not there to stop the DB being attached/restored onto another server.
Currently, the way to ensure that no unauthorised person can read the DB is to secure the server and ensure that no unauthorised people have access to the server.
Transparent database encryption may help (SQL Server 2008) but that works by preventing the DB from being attached/restored to any instance that doesn't have the appropriate certificate installed. From what I read, you'd have to distribute the certificate to allow the express edition to attach the DB, meaning anyone who downloads the DB can get the certificate from the installation package, unless there's some form of encryption you can put on the files in the installation package.
Oh, and btw, even with builtin/admins removed, there is a way for a local administrator on the server to get sysadmin access to SQL server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2008 at 10:29 pm
Thank you
>Oh, and btw, even with builtin/admins removed, there is a way for a local administrator on the server to get sysadmin access to SQL server.
April 29, 2008 at 10:30 pm
Sorry hit the wrong key!
>Oh, and btw, even with builtin/admins removed, there is a way for a local administrator on the server to get sysadmin access to SQL server.
Is this even if they do not know the sa pwd?
Ta
May 1, 2008 at 9:02 pm
Yes. If SQL Server is started in single user mode, any account with local administrator rights can connect/login to SQL Server and they will have sysadmin rights.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 2, 2008 at 9:27 am
gcmcmahon (4/29/2008)
Is this even if they do not know the sa pwd?
Yes, and it works even if the server's in windows authentication only mode.
It's so that an admin cannot accidentally lock himself out of his own server (by dropping the last sysadmin login)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply