Connect To SQL Server Express

Question

I have just started using SQL Server Express which syntactically the same as any other version of SQL Server, however, I am using my web.config file to create a connection string and connect with my code BUT I keep getting an error of

An exception of type 'System.Data.SqlClient.SqlException' occured in System.Data.dll but was not handled in user code
Additional information: Can not open database "TestSQL" requested by the log-in. The login failed.

This is my syntax, and I am using windows authentication. If I log into the server itself with this info I can query away, but accessing through syntax it does not work. Once my con.Open() line is hit the error is thrown.

web.config:

<connectionStrings>
    <add name="SQLServer"
         connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TestSQL;User ID=windowslogin; password=windowspassword; Integrated Security=True;"
         providerName="System.Data.SqlClient"/>
</connectionStrings>

C# code:

public DataSet GetItems()
{
    connectionstring = ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
    using (SqlConnection con = new SqlConnection(connectionstring))
    {
      con.Open();
      using (SqlCommand command = new SqlCommand("Select [field1] from [TestSql].[dbo].[Table1]", con));
      con.Close();
    }
}

EDIT:

I updated my web.config file to have this line of code and now it is presenting a new error shown below. Also, I am using UltiDev Web App Explorer to host not IIS

web.config:

<system.web>
    <identity impersonate="true" userName="windowsusername"   password="windowspassword" /?
</system.web>

And new error is:

Additional information: Login failed for user ''.

Answer 1

You can't pass a username and password when using IntegratedSecurity, and you can't use "windows" user accounts without it. You need to use SQL logins (which is possible but not easy in SQL Express) or run the asp.net site (or impersonate) a user that has the appropriate privileges to the database.