Cannot connect to SQL SERVER from ASP.NET Web Method

Question

I use a C# ASP.NET Webform app and Cannot connect to SQL SERVER from ASP.NET Web Method.(Frame work 4.6)

The connection does not open. Same setup opens from a non webserivce method.

My connection string:

string constr = @"Data Source=LAPTOP-IUDF0K61\BTFSQLSERVER;Initial Catalog=Test;Integrated Security=True";

Connection using windows authentication, but I think WebMethod user is IIS based and that is why it fails.

Question: How to add the proper IIS user to sql server so conn does not fail.

Answer 1

Are you running this on your dev computer, or are you posting to some live or development server running IIS?

I mean, to setup a connection, I assume you use the connection builder, add a connect setting to your project, and are using that? (You're not going to type in the connection string EVERY time you write code then, right?

So, setup a connection for the project, and test that connection. Be it desktop, or whatever? You want to let Visual Studio build and setup the connection for you. Then in code, you can use that connection setting over and and over.

So, from VS go project->"your project name" properties.

So this:

And I have of course in this "test" project many connections I've been playing with, but let's create a new one.

So, now in project, choose settings, and we have this:

So, let's create a new connection:

Do note that in place of typing in YOUR computer name (the server name in this case is your development computer).

But, NOTE also how we do a test connection.

OK, so now we have a valid connection, tested the connection.

But MORE importantly, we NOT written any code as of yet!

OK, so, now let's try/use that connection in code. Say a grid view to display some hotels.

So, drag + drop in a grid view to the page.

So, this markup:

        <asp:GridView ID="GridView1" runat="server"
            width="40%"
            CssClass="table table-hover">
        </asp:GridView>

and now code behind:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
        LoadGrid();
}

void LoadGrid()
{
    string strSQL =
        @"SELECT FirstName, LastName, City, HotelName, Description
            FROM tblHotels ORDER BY HotelName";
    using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.Hotel))
    {
        using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
        {
            conn.Open();
            DataTable rstData = new DataTable();
            rstData.Load(cmdSQL.ExecuteReader());
            GridView1.DataSource = rstData; 
            GridView1.DataBind();   
        }
    }
}

And we now have this:

Note that when you setup a connection as per above?

Visual Studio DOES place/create this connection in the web config for you.

Of course, before a publish to a production server, you would change that connection, but for setting up such a connection, just let Visual Studio create this connection for you.

Also note that I did NOT type in the name of my computer in above, but used "." to represent the local host computer name. (This works in most places in Windows - even when logging on to a server and you don't want the "domain" name, or you don't know the host name of the computer.

And that setup ALSO works nice if you have several developers and are using say GitHub. (We all have different computer names, but use of "." in place of your computer name means that connection's will work for all of the developers and their local running copy of SQL server.

I would of course fire up SQL Studio Manager, and double check if you can connect, and ALSO of course check the "instance" of SQL Server.

The default is "SQLEXPRESS".

Also, note that you REALLY now need to ensure that you not only running SQL Server (we are assuming local for this discussion), you also need to ensure that the SQL browser service is running also. This one:

And since you do development, then I assume you ensured your local copy of SQL server has tcp/ip connections enabled. Thus this:

(I would enable all 3).

However, if bits and parts of your local SQL Server setup on not working? Then the "test connection" button would have shown you don't have a valid connection anyway, right?

Now, in your question title, you mention that you're attempting to build a web method call? Well, use of a connection in a web method call (end point) is not at all different then above code - your code behind is still free to use the settings and the connection you made as per above.

So, if I wanted a web method (and "really" amazing is any web method supports SOAP + XML, JSON, or even REST call via URL using parameters of the URL).

So, let's now use that connection string in a web method then.

So, client side code - web method call (jQuery).

We have this:

Enter HotelName: <asp:TextBox ID="txtHotel" runat="server" ClientIDMode="Static">
                    </asp:TextBox>
<br />
City = <asp:TextBox ID="txtCity" runat="server" ClientIDMode="Static">
        </asp:TextBox>
<asp:Button ID="cmdGetCity" runat="server" Text="Get City"
    CssClass="btn"
    OnClientClick="mygetcity();return false;"
    />


<script>
    function mygetcity() {

        var txtHotel = $('#txtHotel')
        $.ajax({
            type: "POST",
            url: "HotelShow.aspx/HotelCity",
            contentType: "application/json; charset=utf-8",
            data: JSON.stringify({ HotelName : txtHotel.val()}),
            dataType: "json",
            success: function (Data) {
                $('#txtCity').val(Data.d)
            },
            error: function (xhr, status, error) {
                var errorMessage = xhr.status + ': ' + xhr.statusText
                alert('Error - ' + errorMessage)
            }
        });
    }


</script>

Code behind: (the soap, web method (with headers), or rest?

This works:

[WebMethod()]
public static string HotelCity(string HotelName)
{
    DataTable dtHotels = new DataTable();   
    using (SqlConnection con = 
        new SqlConnection(Properties.Settings.Default.Hotel)) 
    {
        string strSQL =
            "SELECT City FROM tblHotels WHERE HotelName = @HotelName";
        using (SqlCommand cmdSQL = new SqlCommand(strSQL, con))
        {
            cmdSQL.Parameters.Add("@HotelName",SqlDbType.NVarChar).Value = HotelName;
            con.Open();
            dtHotels.Load(cmdSQL.ExecuteReader());
        }
    }
    if (dtHotels.Rows.Count > 0)
        return dtHotels.Rows[0]["City"].ToString();
    else
        return "";            
}

So, we now get this:

So, be it a web method (end point), or simple code behind, once that connection is setup in the project, you tested the connection, THEN and only then should you start to write code.

So, let VS build and setup the connection(s) for you - it's really just mouse clicks, and this will ensure you have a working connection string before writing ANY code.