Thursday, December 23, 2010

Login database failed SQL Express

When you are using SQL Express and in the connection string if there's "User Instance=True".

ASPNET account will try to access the file. If you have accessed MDF file with Visual Studio, it will lock it with current logged in username. For E.g. SQLSERVR.exe] [Logged In User]
Hence, ASPNET account will not get a chance to access the MDF file.

How to solve it?

Solution 1:
------------------------------------------------------------------------------------------
1. Open task manager, search for [Image name=SQLSERVR.exe] [User Name=loggedinuser]
2. End Process it.
This will work temporarily.


Solution 2:
------------------------------------------------------------------------------------------
1. Add this to your web.config file
<identity impersonate= "true" userName="<Logged in user name>" password="password" />

For. E.g.
<identity impersonate="true" userName="sbs" password="fastw0rd" />

Caution: When deploying your application to server, delete the <identity .../> mark up


Why it happens?
Well according to my logic, SQL Express is single user instance server. That's just may be