Visual Studio Web Config - Ignores SQL username password in connection string?

Question

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?

Answer 1

The answer is embarrassing! Even though all the code for the classes is in the project I was testing for, the connection string had to be copied from the web.config into the app.config. Wow I feel like a dumbdumb what a rookie mistake.... lol I was churning on this for hours this morning!

Answer 2

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.