If add [user instances=true] to connection string, an exception is thrown

Question

Exception Details:

System.Data.SqlClient.SqlException: can not open the logon request database "RealtyDB" login failed. account 'FAFHN24BNK43\JKAD754' login failed.

My connection string looks like this:

add name = "ApplicationServices"
         connectionString="data source=.\SQLEXPRESS;User Instance=True;Initial Catalog=RealtyDB;Integrated Security=SSPI;"
         providerName="System.Data.SqlClient"       

if I removed the [User Instance=True;], my application will be fine.

Does anybody know why?

Answer 1

I've only ever seen the user instance feature used in combination with AttachDbFileName. In other words, you can't connect to a database that is already attached to a running instance of SQL Server, and tell SQL Server to spin up a new instance for that database, since only one instance can "own" a database at a time. When you use AttachDbFileName, it tells SQL Server to make a copy of that MDF file for the use of the application.

So, unless this is the way you intend to use this feature, I'll suggest again that you just take the User Instance = true parameter out of your connection string.

(It may also be interesting to note that this questionably useful feature has been deprecated.)

Answer 2

I agree with Aaron Bertrand. The connection string should be like this,

connectionString= "data source=.\SQLEXPRESS; Integrated Security=SSPI; AttachDBFilename=|DataDirectory|\appdb.mdf; User Instance=true"