Tuesday, March 6, 2012

.Net App Deployment Scenario: Privileges for Non-Admin Users

My VB.net application manipulates data in a local SQL Express database. When the app is installed, the database does not exist, but it cannot be created at run-time by anyone other than a user with administrator privileges. In addition, the application shares the data stored in SQL with a critical 3rd party component that can only reach the database via named DSN (also not existing prior to installation).

I see my primary SQL security options as being:

(1) Use SQL or Mixed Mode authentication with an admin-level username/password combination, or

(2) Create a db user/group with admin-level privileges and grant membership to all NT authenticated users

Secondary problem: Creating the DSN.

Does it make sense to create a Custom Action (.dll) that is called at the end of the installation process in order to create the database, the user security context and the named DSN?

Am I overlooking some built-in functionality provided by Visual Studio 2005 that will accompish some or all of this for me? I am aware that customizing the 'silent' installation of SQL Express to use a different authentication mode requires manifest tweaking -- I just don't know anything about setting up the appropriate security for this situation. Would you put the db and role creation stuff in a SQL script and execute it post-install?

Thanks in advance for any insight you can provide.

-T

UPDATE

I plan to modify the SQL Express installation to use mixed-mode authentication, then execute code at the tail end of my app install to (a) create the database, (b) create a dbo-level user (and pwd) for the database, and (c) create my DSN (via SQLConfigDataSource API call).

Feel free to respond if you think/know that there is a better solution.

-T

No comments:

Post a Comment