How to connect web service running through IIS express to SQL Server-CodePudding

I am trying to run my web app through Visual studio code on my Windows 10 laptop ( http://localhost:3000/ ) and connect to a service which is developed through asp.net in Visual studio ( http://localhost:44389/ ) in the same machine.

When the app runs, I get this error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

I tried to add the service to SQL Server security by using this command based on this post:

So, lets connect to a database called TEST4 (might as well call the connection TEST4).

With above, then you can use the wizards to setup a connection.

You can of course "always" connect to SQL server on your local machine using windows authentication, since you are the "super user" of your own machine.

So, lets do that:

So, from above, we can click on the [...] button in settings, and build the connection.

I VERY strong suggest you install SQL server express for this setup.

Ok, so click on this:

And now we can just follow the wizard to setup a connection.

In the drop down, your local instance of SQL server should appear.

Like this:

If you local server DOES NOT appear, then

Make sure you are running the so called "browser" service for SQL server (has nothing to do with web browsers!).

That would be this service:

Also, while you at this, make sure tc/ip is turned on. (you don't normally have to do this, but I recommend you do).

So, on SQL Server Network configuration, you see this:

So, as long as you KNOW sql server is running.

And again, without question, we assume you have SQL management stuido installed, right?

(Visual studio, SQL server express, and the SQL management studio are all free).

Ok, so back to the simple connection wizard that we have to just click away with the mouse to setup?

Then this:

and after selecting the database, then do hit test connection.

eg this:

So, you do not have to do all those steps you note.

IIS will connect fine to sql server, you just have to make sure SQL server is running, and there is ZERO ZERO ZERO ZERO use to try all kinds of permission settings to IIS when above will suffice.

And I would suggest that you install SQL server in mixed mode (both windows authenicatiaon and also sql server auntheication.).

For testing and dev, you can simple use the above windows authentication, but you might want to add a logon user using SSMS, and thus connect using a sql logon you created.

But, even on production servers? I have NEVER had to muck around with creating permissions in IIS to allow it to connect to sql server. All you require is a valid connecton string, and you should be good to go.

Ok, so now that I created the above connection?

Then in code I can use it this way.

Let's drop in a grid view into the aspx page like this:

        <asp:GridView ID="GridView1" runat="server">

        </asp:GridView>

And code behind is this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadGrid
    End If

End Sub


Sub LoadGrid()

    Dim strSQL As String =
        "SELECT FirstName, LastName, City, Description, HotelName
        FROM tblHotelsA ORDER BY HotelName"

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            Dim rstData As New DataTable
            rstData.Load(cmdSQL.ExecuteReader)
            GridView1.DataSource = rstData
            GridView1.DataBind()
        End Using
    End Using

End Sub

And now we get this:

So, make sure SQL server is up and running.

Make sure you can use/connect from SSMS.

Make sure you running the sql browser serivce.

Turn on tc/ip.

And then use the wizards in VS to connect and test the connection BEFORE you start coding away.

The above building of the connection also means we have a system wide connection to use (and no messy connection strings all over in code).

Even better bonus points? The project->settings when you build a connection with the wizard? It ALSO places that connection inside of web config for you, further saving hassle and world poverty.