Re: How can I secure a MSDE database for users who have SQL Server?

From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 05/12/04

  • Next message: Robbs: "Change default to named instance??" 
    Date: Wed, 12 May 2004 18:42:55 +0200

    hi Ed,
    "Ed-it" <ed-it@euronet.nl> ha scritto nel messaggio
    news:6d7b9e63.0405120509.74507ca8@posting.google.com...
    > How can I protect a MSDE-database so that SQL Server Enterprise
    > Manager always asks for a password if you want to access the database
    > in SQL Server E.M.?
    >
    > I've created a .NET-application that uses MSDE. I know some of the
    > users where the application will be running will also have SQL Server
    > running. I don't want them to look around in my db. How do I protect
    > my database so that it's only accessible for my application and only
    > in SQL Server Ent.Man. if you know the login name and password. It
    > works with SQL Server authentication but not with Windows
    > authentication.
    > I install MSDE with mixed mode (SECURITYMODE=SQL) and a password
    > (SAPWD=mypassword). The connection from my application uses: "User
    > ID=sa; Password=mypassword".
    >
    > Thanks for your help,
    > Ed

    it is not possible to protect your database that way..
    SQL Server security implements a 2 phases security protocol..
    the first on is concerned with SQL Server login authentication, where an
    appropriate Login object must be registered, both SQL Server login and/or
    WindowsNT login...
    at the database level, the security is implemented at user's level, where
    each user is mapped to an existing login..
    every login which is mapped to a database user in your database will have
    ritghts to access it, and, according to it's privileges, can read/modify
    data and/or database structure..
    more.. every login member of the sysadmin server role will be granted access
    to every registered database, even without expressed database access
    privileges..
    you can protect your database setting limited privileges to your users, or
    excluding some of them, but not sysadmins members..
    if you require strong data protection, you have to resort on encryption via
    .Net and/or third parties tools for the data part, and creating
    procedures/functions/views with WITH ENCRYPTION option, but table structures
    will still be accessible.. 

    -- 
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm        http://italy.mvps.org
DbaMgr2k ver 0.7.0  -  DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply
  • Next message: Robbs: "Change default to named instance??"

    Relevant Pages

    • Re: System Administrator Implied Permissions
      ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
      (microsoft.public.sqlserver.security)
    • Re: No db access after publishing web site
      ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: cannot login to the db after...
      ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
      (microsoft.public.sqlserver.security)
    • Re: Cant view merge agent properties (trying again)
      ... In the List of Actions for the Snapshot Agent History I see this repeated: ... every single database listed. ... So, just now, I went to computername\Administrator Login ID (because it's ... On the computer running SQL Server, ...
      (microsoft.public.sqlserver.replication)
    • Re: MDF file level security from copying
      ... is a way to protect the MDF files, ... located on FAT like Windows 98 and Windows ME, ... >BTW, if you are concerned about the database schema, as ... >> file while the sql server is stopped? ...
      (microsoft.public.sqlserver.security)