asked on

Problem connecting to database

I am using Visual Studio Web version and MS SQL. I have developed an asp.net page that takes arguments from a query string and edits the database. The database and the web server are both on the same machine, so the web pages are not going cross domain or even to a different machine within the domain. Everything works perfectly when I launch the page using Visual Studio's preview using localhost(dynamic port number) and it reads and writes to the database. As soon as I use the actual ip address with no dynamically generated VS port, as in, the real world published version, nothing works as though it can't connect to the database. Since it works on localhost in VS, I don't think the code is wrong, but rather I am missing a setting that is required when accessing the page through it's actual domain ip address.

Here is my connection settings

SqlConnection myConnection = new SqlConnection(
"server=localhost;" +
"Trusted_Connection=yes;" +
"database=blip; " +
"connection timeout=30");

I'm guessing someone must have run into this small ambiguity.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Nakul Vachhrajani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Arnold Layne

ASKER

Hi guys, I can't seem to get any errors out of it. It just does nothing as if it completely ignores the write attempt to the database, so I suspect it's security as you guys mentioned. I don't know anything about impersonation. I added the administrator as the user with the correct password to the connection string, and it still does nothing. I also change web.config debug to false as setting it to true on live sites sometimes causes problems if I remember correctly, but it did nothing. Here is my code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class data : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        String phone = Request["Phone"];
        String address = Request["Address"];
        String dollarAmt = Request["DollarAmt"];
        if (address == null) { address = ""; }
        float dollars = 0;
        string deleteRequest = Request["delete"];

        SqlConnection myConnection = new SqlConnection("user id=Administrator;" +
                                           "password=mypassword;" +
                                           "server=localhost;" +
                                           "Trusted_Connection=yes;" +
                                           "database=mydata; " +
                                           "connection timeout=30");
        try
        {
            if (dollarAmt != null)
            {
                dollars = float.Parse(dollarAmt);
            }
            myConnection.Open();
            if (deleteRequest != null)
            {
                
                SqlCommand myDelete = new SqlCommand("DELETE FROM Orders WHERE ID='" + deleteRequest + "'", myConnection);
                myDelete.ExecuteNonQuery();
            }
            if (phone != null)
            {
                try
                {
                    SqlCommand myInsert = new SqlCommand("INSERT INTO Orders (PhoneNumber, Address, DollarAmt) Values ('" + phone + "', '" + address + "', '" + dollars + "')", myConnection);
                    myInsert.ExecuteNonQuery();
                }
                catch (Exception err) { Response.Write("<p>" + err + "</p>"); }
            }
            if (deleteRequest == null)
            {
                SqlDataReader myReader = null;
                SqlCommand myCommand = new SqlCommand("Select ID, PhoneNumber, Address, DollarAmt FROM Orders", myConnection);
                myReader = myCommand.ExecuteReader();
                while (myReader.Read())
                {
                    Response.Write("<div id=\"section" + myReader["ID"] + "\">");
                    Response.Write("<ul id=\"item" + myReader["ID"] + "\" onclick=\"showOptions('buttons" + myReader["ID"] + "');\">");
                    Response.Write("<li>" + myReader["PhoneNumber"].ToString() + "</li>");
                    Response.Write("<li>" + myReader["Address"].ToString() + "</li>");
                    Response.Write("<li>" + myReader["DollarAmt"].ToString() + "</li>");
                    Response.Write("</ul>");
                    Response.Write("<section id=\"buttons" + myReader["ID"] + "\" style=\"display:none\">");
                    Response.Write("<div id='delivered" + myReader["ID"] + "' class='buttons green' onclick='delivered(\"section" + myReader["ID"] + "\", \"" + myReader["ID"] + "\");'>DELIVERED</div>");
                    Response.Write("<div id='delivered" + myReader["ID"] + "' class='buttons green' onclick='delivered(\"section" + myReader["ID"] + "\", \"" + myReader["ID"] + "\");'>DELIVERED</div>");
                    Response.Write("</section>");
                    Response.Write("</div>");
                }
            }
            myConnection.Close();
        }
        catch (Exception error)
        {
            Response.Write("<p>" + error + "</p>");
            myConnection.Close();
        }
    }
}

Open in new window

If anybody can give me some debug suggestions, that would be greatly appreciated as I don't know a lot about advanced debugging and database security. I'm curious as to why my aspx page is an anonymous user as far as the database is concerned when I am accessing the page within webroot, but it's not anonymous when using VS virtual server (localhost:dynamic port). Even when I try localhost when I'm on the server with no dynamic port (port 80), it still doesn't work.

Arnold Layne

ASKER

I found this in the event viewer

"Log Name: Application
Source: MSSQLSERVER
Date: 3/4/2015 6:20:24 AM
Event ID: 18456
Task Category: Logon
Level: Information
Keywords: Classic,Audit Failure
User: N/A
Computer: MyComputer
Description:
Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 117.21.176.17]

So it is trying an SQL authentication rather than Windows. The aspx page lives on the same machine as the database, and it is an AWS machine with no other machines on it's network, so could it use Windows Auth if I change the connection method? And if so, how?

Arnold Layne

ASKER

I have also tried to bring up an aspx page and cannot do that either. And the aspx page is what actually tries to connect to the database. I get the following error:

"HTTP Error 404.3 - Not Found
The page you are requesting cannot be served because of the extension configuration. If the page is a script, add a handler. If the file should be downloaded, add a MIME map."

Again, I never receive this error or any other errors when I run the site in debug mode thru VS. I get these errors when I run it under wwwroot and access it via localhost/mypage.aspx when on the server, and the same error when accessing it via it's actual ip address when not on the server.

Anthony Perkins

Server is configured for Windows authentication only.
In order to use SQL Server Authentication you need to enable the SQL Server to use mixed (SQL Server and Windows Authentication) mode.

You can do this by modifying the server properties using SSMS or modifying the registry as in something like this:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

Again, I never receive this error or any other errors when I run the site in debug mode thru VS.
Trust me we understand. No need to repeat it.

Arnold Layne

ASKER

Thanks guys. You are both correct, however the only thing ever querying the database will be local asp.net pages on the same server as the db. Therefore, I really want to keep it all windows auth, and I believe that I can because the aspx pages merely receive info from the html pages via GET and then manipulates the db, so all db requests are from the same domain and even same machine. It turns out, the real problem was that I did not implement ASP.NET in IIS correctly. After I did that, I added the ASP.NET user to the db server with the proper permissions. Problem solved

Anthony Perkins

Therefore, I really want to keep it all windows auth, and I believe that I can
And I believe we both said you could and probably should. The key when using Windows Authentication is to use impersonation.