Connection string between IIS and SQL server

Question

I'm confused by the connection string in the Web.config file of project with Entity Framework Web API. There are a lot of variants I tried, but none of them was helping me out. Currently the connection string is

  <connectionStrings>
       <add name="DefaultConnection" connectionString="Server=.\SQLEXPRESS;Database=MyProject;User Id=John;Password=duck;" providerName="System.Data.SqlClient" />
  </connectionStrings>

I can access the database which is called MyProject in SQL Server Management Studio 2012 (even from remote), using John as username and duck as password.

The project is run by an IIS server on the same machine as the SQL server, the SQL server's instance name is SQLEXPRESS. My webpage is displayed on localhost, but the controller just replies 500 Interal server error whenever some data from the sql database is requested. It looks like there is no connection to the database.

What do I have to add or change in order to get a connection betweeen IIS and SQL server, or how can I locate better the problem's cause?

Also, try setting a breakpoint in your page, and see where the error is actually occurring. It might not be a DB connection problem. — 3Dave
– 3Dave, Commented Dec 9, 2013 at 16:35

ckal · Accepted Answer · 2013-12-09 16:32:59Z

3

Use the Event Viewer to get more information on what the 500 error actually is.

answered Dec 9, 2013 at 16:32

ckal

3,5802 gold badges25 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Explicat · Accepted Answer · 2013-12-13 07:56:33Z

This tutorial how to setup SQL Server 2008 for an ASP.net website on IIS 7.0 brought me close to the solution.

Basically, what you need to do is

Install SQL server.
Allow TCP/IP connections to the SQL server.
Attach your database.
Create a login. I'm using SQL Authentication.
Assign the user permissions to your database.
Configure your database connection string.

Changing the name of my connection string helped me establish the connection finally.

<connectionStrings>
       <add name="MyProjectContext" connectionString="Server=.\SQLEXPRESS;Database=MyProject;User Id=John;Password=duck;" providerName="System.Data.SqlClient" />
</connectionStrings>

The context model required this name.

public class MyProjectContext : DbContext
{

    public MyProjectContext() : base("name=MyProjectContext")
    {
    }

    public DbSet<Model1> Model1 { get; set; }
    public DbSet<Model2> Model2 { get; set; }
}

Steven V · Accepted Answer · 2013-12-09 16:10:35Z

1

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=MyProject;User ID=John;Password=duck" providerName="System.Data.SqlClient" />
</connectionStrings>

Please try the above connection syntax.

edited Dec 9, 2013 at 16:10

Steven V

16.7k3 gold badges66 silver badges77 bronze badges

answered Dec 9, 2013 at 16:08

January Mmako

3301 silver badge7 bronze badges

Comments

Aarti Srivastava · Accepted Answer · 2013-12-13 08:07:21Z

1

Basically just amend your connection string using Integrated Security=False like :

and it will work ...

answered Dec 13, 2013 at 8:07

Aarti Srivastava

112 bronze badges

2 Comments

Aarti Srivastava Over a year ago

Basically just amend your connection string using Integrated Security=False like : <add name="MyProjectContext" connectionString="Server=.\SQLEXPRESS;Database=MyProject;User Id=John;Password=duck;" providerName="System.Data.SqlClient" Integrated Security="False" />

Aarti Srivastava Over a year ago

basically Integrated Security =True means --- the current Windows account credentials are used for authentication. so Make it False when your connection is using SQL Server authentication instead of Windows

user2275545 · Accepted Answer · 2021-04-01 14:30:56Z

0

My answer is not related to connection string, however, if you have an application that is hosted on IIS and it tries to connect to SQL Server and you get "500 Interal server" error (despite a correct connection string), you still need to configure both IIS and SQL Server.

There is an invaluable article called "Configuring IIS, ASP.NET, and SQL Server". I followed the steps described there and my app communicates with my SQL database fine now:

https://www.codeproject.com/Articles/674930/Configuring-IIS-ASP-NET-and-SQL-Server

answered Apr 1, 2021 at 14:30

user2275545

671 silver badge2 bronze badges

Collectives™ on Stack Overflow

Connection string between IIS and SQL server

5 Answers 5

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related