This article was previously published under Q248683
This article has been archived. It is offered "as is" and will no longer be updated.
This article outlines some Microsoft Data Engine (MSDE) security management rules for software developers who target customers that may need to run more than one MSDE-based application at a time. It is recommended that developers intending to deploy an application with MSDE follow the guidelines listed in the More Information section of this article. This permits the sharing of the database engine with other applications and also increases scalability by ensuring that the application can be deployed against a shared SQL Server without requiring a re-design of application security and setup procedures.
You can use the following items for security management of a MSDE based application:
Since an MSDE instance may need to be shared among applications, the password for the administrative "sa" account should be under the control of the end user. If the password for the sa account is changed by an application, there is no way for subsequent applications installed on the system to log into MSDE with an administrative account to create the accounts and objects that are necessary for that application. Ideally, an MSDE-based application that follows the preceding recommendation would provide an interface that allows the end user to change the sa password.
If MSDE is already installed on a system, you should ask the user to enter the sa password during setup of your application. Do not assume that the password is blank unless your setup application installs MSDE. Use this password to log into MSDE, create or attach your application database, create a SQL Server authenticated login with sp_addlogin, and grant permissions sufficient to administer your application's database to this login. After application installation, all subsequent access to the database should be through the login created during the setup process. Do not assume that the user will not change the sa password.
Never change the sa password without informing the user of the new value for the password. This would make it impossible for the user to install any other MSDE-based applications on the computer since neither the user nor the other application's setup application has access to a MSDE login with sufficient permissions to create a new database.