SQL Server Express Edition attaching and using problems

"Cannot open user default database. Login failed.
Login failed for user ......"

This problem occurs very often while the developer uses Visual Studio and testing in a browser at the same time. The reason is the already opened connection to the database in the VS environment. For example every time when the developer edit DataSet connected to current database this connection stays opened. The solve this problem the connection to the db has to be closed by the right mouse button -> Close Connection in the Server Explorer tab.

Another problem with attaching database and using its connection is:
"An attempt to attach an auto-named database for file ..... failed. A database with the same name exists, or specified file cannot be Opened, or it is located on UNC share."

1 . The first thing that has to be done is to try to attach the database manually with Microsoft SQL Server Management Studio.

2. The data folder permissions have to be set to "Modify" for ASPNET account and NETWORK SERVICE for windows server 2003

3. Make ASPNET user a member of SQLServer2005MSSQLUser$server$SQLExpress group

4. Add new Login to the SQL Express server for ASPNET (NETWORK SEVICE) user

5. In the connection string User Instance=True has to be checked. It works only if SQL Server uses Windows Authentication mode.
Changing the authetication mode can be done by SQL Server Management Studio with right mouse click on the computer name and Properties -> Security.
When Integrated Security=SSPI in the connection string is used make sure you are using in system.web sections of the web.config and also make sure that a windows account for the "Anonymous User" is selected in the access section in IIS.

6. Also the sql settings in
c:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\ can be deleted and the sql service restarted.

No comments:

Post a Comment