I have a desktop application that uses Sql user instancing. This is my connection string:
"Data Source=.\SqlExpress;
AttachDbFilename=C:\path\file.mdf;
Integrated Security=True;
User Instance=True;
Connect Timeout=100;"
My application creates this DB, downloads a load of data into it from a web service and then does a lot of actions with it.
The problem comes when I attempt to re-open the connection. I get a SqlException:
"Cannot open user default database. Login failed.
Login failed for user 'myDomain\myusername'."
This error makes no sense in this context - I have no default database. I'm logging in to an instance created just for the current application, running separately from SqlExpress.
There's no other way to connect to this DB. If I start the SqlExpress service and connect to the default instance it won't be visible. It only exists for this application.
The file on disk is locked by the SqlExpress instance service running under the application. if I stop the app and restart it the connection works first time, but fails on re-opening. If I just stop the app I can delete the .mdf files and begin again, but it still crashes when I re-open the connection.
As my app started the instance running as me my current user should have access to every DB in the instance.
This doesn't happen for other users of the same code, which suggests that it's a SQL config issue.
Does anyone have any idea what causes this and how to work around it?