I am trying to figure out a way to use a LocalDB in place of SQL for integration testing of EF6 queries (this would allow us to run integration tests on our build server). It seems like I should be able to replicate my database in SQL Express, detach, and import into my test project. I would expect from there I can modify my connection string in my test project to hit the local database file. I am running into a security issue though. I need to get SQL security working to pass in a user id and password in my conn string (since the build server will not be using my domain account), but regardless of what I try I get a bad username or password error as soon as I try to query. My connection string looks like this:
<add name="ClinicalContext" connectionString="metadata=res://*/ClinicalDataStore.csdl|res://*/ClinicalDataStore.ssdl|res://*/ClinicalDataStore.msl;provider=System.Data.SqlClient;provider connection string="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\ClinicalDataStore.mdf;Integrated Security=False;User Id=SomeUser;Password=SomeUsersPassword;""
providerName="System.Data.EntityClient" />
Does anyone have any experience with this and know if I need to do something particular to get sql security to work this way?
Update I attached the database file via Server Explorer and checked the updated connection string. The only change was to add an Initial Catalog and change the security back to integrated. If I try to connect that way, I get an error that it cannot access the catalog as the defined name. If I change back to sql security I still get the bad username or password error.
