none
vb .net SQL 2005 standard user connection issue RRS feed

  • Question

  • I am begininng to work on a windows application that will utilize the SQL 2005 Express database.  I need to know how standard users will be able to access the database.  The problems I've been running into is that the Windows Authentication does not work and for security purposes we're not supposed to use SQL authentication.

     

    Here's what I've got:

     

    1.  Application installs under admin priviledges.  This includes SQLExpress.  It creates/attaches a database in the default c:\program files\Microsoft SQL Server\...\data\ folder.

    2.  The standard user opens the program selects the local server and the database.  It connects just fine using Windows Authentication.  Then the program bombs out when trying to read from the database because the user does not have permissions.

     

    A.  I can't give the user permissions because the program has already been installed.

    B.  There is no local user account created because it is a domain environment that anyone can log into the computer with and I don't know the username during the installation.

     

    Solutions?

     

    I've noticed that SQL Server runs on the Network Service user and has full access to the database.  Can I have my application access the database through that account?

     

    Other Notes:

     

    I'm trying to copy the way Outlook 2007 Business Contact Manager works with SQL 2005 Express.  It does not have a seperately created username created for access.  The only difference is it stores the database within the user's appdata folder.  This application seems to be the most secure.  It does not use SA either.  So, there's got to be a way to get my application going in a similar fasion.

     

    Thanks in advance!

    Thursday, August 9, 2007 4:35 PM

All replies

  • Here's a suggestion: Implement a windows service (running under the service account) that does the data-access.. And let the clients talk to that service (eg: via remoting)... This way, any user that can logon can use the application (and the database only has to grant access to the service account)
    Thursday, August 9, 2007 10:03 PM
  • That's a good idea!  I don't think that it's the same way as Outlook's Contact Manager uses though so it doesn't seem as clean?

     

    Another idea we had was perhaps using Application Roles.

    http://msdn2.microsoft.com/en-us/library/ms190998.aspx

     

     

     

     

    Friday, August 10, 2007 2:18 PM