 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / February 2008SQL 2005 express security issue
Hi, We just revamp our application from Access to use SQL Express 2005 as database engine, which is a standalone application running on client PC. I'm now simulating the scenario when I deploy the DB to client, how can I protect the database (or some tables) from being read by them? Since this application will distribute to client PC, which is out of our control, so we must choose SQL authentication. What I got is, when I setup a SQL express in a new machine, and attach the database I want to protect, login as 'sa', I can read everything inside, no matter what & how I set. Is it true that I can't protect database/table in SQL authentication mode? Please help. stm, A system administrator such as 'sa' or any other login granted the sysadmin role can read anything on the server. At a remote location where you do not control the rights granted on the server, there is nothing you can do about this. If your application and the remote user log in with accounts that are not sysadmins then you can protect databases, tables, stored procedures, etc. However, within a database there are other roles that, if granted, give sweeping rights. In a database the db_owner role is like a sysadmin for one database. It can see and do anything in that database. To understand what is revealed and to whom here is a write-up from SQL 2000: http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1061.mspx?mf r=true There are a few more roles now in 2005 and things are changing some, since these roles are a concatenation of several rights that can be individually granted: http://technet.microsoft.com/en-us/library/ms175892.aspx - fixed server roles http://technet.microsoft.com/en-us/library/ms189612.aspx - fixed database roles RLF > Hi, > [quoted text clipped - 14 lines] > > Please help. Back to the age of using Access, I can set password and control remote user access. Change to SQL Express eventually step backward..............I have a little regret on my choice!! > stm, > [quoted text clipped - 42 lines] >> >> Please help. The easiest way that probably matches what you were doing before with Access is: Create a SQL Server login: MyAppLogin At MyAppLogin to MyApplicationDB as a member of the db_owner role. Keep an encrypted password in your application that you can decrypt and use to log in from the application. This is not ideal security, since a determined person get get into the database with MyAppLogin, but the less determined user will simply be able to use the database through the applicatio. (But this is no different from Access, where someone with a little bit of work can break into the Access database.) If that is enough security to fit your needs, then go ahead. RLF > Back to the age of using Access, I can set password and control remote > user access. [quoted text clipped - 47 lines] >>> >>> Please help. I already create another a/c which is the db_owner, but 'sa' can still do anything she wants...... > The easiest way that probably matches what you were doing before with > Access is: [quoted text clipped - 68 lines] >>>> >>>> Please help. stm wrote on Tue, 5 Feb 2008 00:34:10 +0800: > I already create another a/c which is the db_owner, but 'sa' can still > do anything she wants...... sa can always do anything - that's the whole point of the account. The sa user should be someone that is trusted, it should not be used by just anyone.  SignatureDan but anyone can install his own SQL Express in their own machine, and view anything....... > stm wrote on Tue, 5 Feb 2008 00:34:10 +0800: > [quoted text clipped - 4 lines] > user should be someone that is trusted, it should not be used by just > anyone. stm wrote on Wed, 6 Feb 2008 00:19:17 +0800: > but anyone can install his own SQL Express in their own machine, and > view anything....... Only if they then get hold of the MDF and LDF files and attach them to their instance of SQL Server - if you allow access to the underlying files then you're at risk no matter what software you use. If they just install SQL Express on their own machine and then connect to a different server where your data lies they'd need to have the sa password on the remote server - they can't just connect using the local sa login and then pull it across. If you're really concerned about restricting access to the data, then look into using the built-in encryption features - using these would also require the person who gets the MDF and LDF files to also gain access to the certificate used to encrypt/decrypt the data, and that is likely going to be much harder. SignatureDan yes, they will get a copy of the mdf file......it is included in the distribution of my application. It is a local standalone application, no network connection required. Using Access, I can set password to MDB, although some said it is easy to crack. > stm wrote on Wed, 6 Feb 2008 00:19:17 +0800: > [quoted text clipped - 14 lines] > the certificate used to encrypt/decrypt the data, and that is likely going > to be much harder. Personally I cracked MDB files' passwords many times. There are lots of softwares which perform this job decently and easily in one second. SignatureEkrem Onsoy > yes, they will get a copy of the mdf file......it is included in the > distribution of my application. [quoted text clipped - 21 lines] >> the certificate used to encrypt/decrypt the data, and that is likely >> going to be much harder. Which statement I told to my security officer is more likely to pass the risk assessment? - Using SQL Express, I cannot control user to attached the database to their own machine, and login as 'sa' to view everything. - Using Access, I can set password to control user view the database directly, but some tools are available to crack the password. > Personally I cracked MDB files' passwords many times. There are lots of > softwares which perform this job decently and easily in one second. [quoted text clipped - 24 lines] >>> access to the certificate used to encrypt/decrypt the data, and that is >>> likely going to be much harder. As far as I can see the posts under your question you focused on the "sa" account and protecting your database. But it's not the start point. First, you should protect your OS because your SQL Server runs under the OS. If you can keep your Windows and Domain accounts under your control then nobody can reach to your database files. Then stealing a database by detaching it would not be a security risk anymore. If you can't control your Windows user accounts then you'd not be able to protect only your databases but anything in your environment. You do not have to use the "sa" account (if your application does not enforce you) and it's not recommended using this account because every hacker knows that SQL Server has a built-in sysadmin account which is called "sa". So, creating another account with sysadmin rights would be the right choice. Disable your "sa" account if you don't have to use it. SQL Server is a powerful product and much better than Access's password protection. You should just read more about it, then you'll see the light. SignatureEkrem Onsoy > Which statement I told to my security officer is more likely to pass the > risk assessment? [quoted text clipped - 32 lines] >>>> access to the certificate used to encrypt/decrypt the data, and that is >>>> likely going to be much harder. I know/understand everything you mention, it is an ideal case, but not suitable to mine, or just I use SQL Express incorrectly?? The problem is that, as I stated before, my application is standalone, run on client machine, no network connection, no domain, no AD, etc required. I can't use Windows authentication because I can't control account creation in client machine, and which account they use. I can't control user attached to my database because it is his own machine, I can't set any file permission, so the last security measure is to use SQL authentication to protect my data. Until I know that any user can install his own copy of SQL Express, and use 'sa' to attached to the mdf (at least in my case), nothing can be protected. > As far as I can see the posts under your question you focused on the "sa" > account and protecting your database. But it's not the start point. First, [quoted text clipped - 50 lines] >>>>> access to the certificate used to encrypt/decrypt the data, and that >>>>> is likely going to be much harder. I see your situation better now and it looks like your best bet is encryption. SignatureEkrem Onsoy >I know/understand everything you mention, it is an ideal case, but not >suitable to mine, or just I use SQL Express incorrectly?? [quoted text clipped - 67 lines] >>>>>> access to the certificate used to encrypt/decrypt the data, and that >>>>>> is likely going to be much harder. Do you know more about encryption in SQL Express. >I see your situation better now and it looks like your best bet is >encryption. [quoted text clipped - 72 lines] >>>>>>> gain access to the certificate used to encrypt/decrypt the data, and >>>>>>> that is likely going to be much harder. Take a look at BOL: http://msdn2.microsoft.com/en-us/library/ms189586.aspx SignatureEkrem Onsoy > Do you know more about encryption in SQL Express. > [quoted text clipped - 78 lines] >>>>>>>> also gain access to the certificate used to encrypt/decrypt the >>>>>>>> data, and that is likely going to be much harder. stm wrote on Sun, 3 Feb 2008 00:53:42 +0800: > Back to the age of using Access, I can set password and control remote > user access. It's pretty easy to find/remove passwords for Access databases, there are plenty of applications that will do so. SignatureDan |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.