Background: I have created a basic entity framework repository pattern using code first. I then created a test project and the first unit test created was to test some basic repository calls against the database. Strangely, I noticed that entity framework was creating a database in my users\myusername directory!
After a bit of research I realized best practice would be for me to create a database for a bit of testing using SSMS with a defined username and password. I accomplished this, and also tested a created connection in Server Explorer within VS 2012, which succeeded. My connection string as is looks like:
<connectionStrings>
<add name="TestConnection" connectionString="Data Source=.\SQLEXPRESS;User ID=DevSqlUser;Password=devsql;Initial Catalog=TestDatabase;MultipleActiveResultSets=True;Integrated Security=False;" providerName="System.Data.SqlClient" />
</connectionStrings>
However, now when I run my test which should instantiate a repository using the connection string by name, I get a strange exception which is using my windows credentials! Why it is doing this? I have no clue. I do not have any identity impersonation in my web config.
System.Data.SqlClient.SqlException: Cannot open database "TestConnection" requested by the login. The login failed. Login failed for user '{domain}{myusername}'
Anyone know why it isn't using the user ID I specified in the connection string?
Looks like your SQL express is set for Windows authentication only. Take a look at authentication mode settings on your SQL database. If you are using windows authentication, there is no need to provide username and password. If you need to use a username and password, you can use mixed mode.
Connect to database from SSMS, right click on server and select properties. Under security, you can see authentication mode settings. Set that as per your needs.