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.
LVL 9
BobHavertyComhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
That is because you are connecting to the website anonymously.   So either use SQL Server Authentication or use impersonation.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Overall I agree - since trusted connections are being used, authentication is based on the windows security context. With anonymous login, that would not be possible.

For further information, what is the exception that you receive when the connection fails? Error number and state would also be of great help.
BobHavertyComhAuthor Commented:
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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

BobHavertyComhAuthor Commented:
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?
BobHavertyComhAuthor Commented:
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 PerkinsCommented:
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.
BobHavertyComhAuthor Commented:
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 PerkinsCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.