• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

Exception got

Hi,
I've published the project and then run it like this
https://WIN-BVDIC8GIAHI/Login

But I got the catch exception below executed
            ..
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT COunt(*) FROM user_master where user_abbr=@par_id and user_password=@par_pass", conn);
                cmd.Parameters.Add("par_id", SqlDbType.VarChar).Value = tb_userid.Text;
                cmd.Parameters.Add("par_pass", SqlDbType.VarChar).Value = tb_password.Text;
                ret_val = Convert.ToInt32(cmd.ExecuteScalar());

                if (ret_val == 0)
                {
                    Userid.InnerText = "Invalid User ID/Password - '" + tb_userid.Text.Trim() + "/" + tb_password.Text.Trim() + "' is entered!";
                }
                else
                {
                    Userid.InnerText = "You have connected to the database successfully!";
                    Session["sv_userid"] = tb_userid.Text.Trim();
                    Session["sv_userpass"] = tb_password.Text.Trim();

                    Response.Redirect("~/Edit_User/Edit_User.aspx");
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                Userid.InnerText = "Unable to check against the database: ";
                Userid.InnerText += ex.Message;
            }
            finally
            {
                conn.Close();
            }
            ...

as the message above
"Unable to check against the database: Login failed for user 'WIN08\WIN-BVDIC8GIAHI$'"

was shown by the project. What is the issue with the above codes?
https://skydrive.live.com/#cid=17EC75244BAC022F&id=17EC75244BAC022F!226
0
HuaMinChen
Asked:
HuaMinChen
  • 18
  • 16
1 Solution
 
Robert SchuttSoftware EngineerCommented:
I'm not sure what the skydrive document holds but it's not accessable. You're connecting to the database with a computer account, is that what you want? Then you need to grant that account access to the database. Otherwise, make sure you use a valid user when creating the connection.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks. If you cannot see the file inside Skydrive, can you please refer to this instead?
http://dl.dropbox.com/u/40211031/t147.png

Yes, I do have a proper connection inside the project. What is the reason of the issue I mentioned?

Many Thanks & Best Regards,
HuaMin
0
 
Robert SchuttSoftware EngineerCommented:
Ok, I can see the picture, but it doesn't really change what (to me) seems to be the problem. I'm not sure what the reason is; assuming you don't want to use that '$' account, my first thought is that you don't specify a user in the connection string and if so, you need to make sure that the process running the query (IIS application pool setting) is a user with access to the database (or give that user access to the database).
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HuaMinChenBusiness AnalystAuthor Commented:
Here is the one of 1st project.
    <connectionStrings>
        <add name="Mssqlconn" connectionString="Data Source=192.168.168.1\ss2012;Initial Catalog=web_schema;Integrated Security=True;User ID=ws_login;Password=mypass" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Here is the one of 2nd project
    <connectionStrings>
    <add name="Mssqlconn" connectionString="Data Source=192.168.168.1\ss2012;Initial Catalog=web_schema;Integrated Security=True;User ID=ws_login;Password=mypass" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Many Thanks & Best Regards,
HuaMin
0
 
Robert SchuttSoftware EngineerCommented:
Hmm, I'd have to try that out/search for it:
Integrated Security=True;User ID=ws_login;Password=mypass
This feels contradictory to me but I could be wrong.

Ehh, by the way: second project? I hadn't seen that before. Those connection strings are identical, which is probably the point you're making (one works, the other doesn't?). Definitely worth checking that you use the same publication settings (if any) and that the sites/virtual directories you publish to are using the same settings if applicable, same application pool or inside the application pool settings, the same user.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks. What can be the reasons?
0
 
Robert SchuttSoftware EngineerCommented:
I'm trying to reproduce your situation. In the mean time, have you checked the user running the website? Try under Application Pools in IIS setup on the server. Publication settings I'm not very familiar with myself but on the server you should at least be able to find out what the current settings are.
0
 
Robert SchuttSoftware EngineerCommented:
Also, you could try changing the connection string to use: "Integrated Security=False" (since you do supply a user/password), maybe it's as simple as that.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Many thanks Robert. I do change it to use Integrated Security=False instead. But when running the same project after I've removed the old one and have republished the new project in Win 08 server. When running this again
https://WIN-BVDIC8GIAHI/Login

I am getting this issue.
Server Error in '/Login' Application.
--------------------------------------------------------------------------------

Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: The entry 'Mssqlconn' has already been added.

Source Error:


Line 22:     -->
Line 23:             <add name="Mssqlconn" connectionString="Data Source=192.168.168.1\ss2012;Initial Catalog=web_schema;Integrated Security=False;User ID=ws_login;Password=mypass" providerName="System.Data.SqlClient"/>
Line 24:       </connectionStrings>
Line 25:       <system.web>
 

Source File: C:\inetpub\wwwroot\Login\web.config    Line: 23


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.5456; ASP.NET Version:2.0.50727.5456

while inside the same server, I can login using the same login to "192.168.168.1\ss2012" by Sql server management studio.

Many Thanks & Best Regards,
HuaMin
0
 
Robert SchuttSoftware EngineerCommented:
Instead of adding a new connection it should have been possible to change the existing one (I'm guessing). Strange that it would let you add another one with the same name, how did you make that change?

Probably the line before also holds: <add name="Mssqlconn" ...

Delete that line and you should be ok.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks a lot. Again I've gone to this
https://skydrive.live.com/?cid=17ec75244bac022f#cid=17EC75244BAC022F&id=17EC75244BAC022F!229

after I've changed the name to be "Mssqlconn1" instead.

Many Thanks & Best Regards,
HuaMin
0
 
Robert SchuttSoftware EngineerCommented:
I can't access the file on skydrive, have you made it public?

Any way, it's better to post the error here for future reference (external references have a way of disappearing over time).

Just double checking: have you changed the code to use the new connection as well? The error should at least be different, it's not possible you're still logging in with a trusted connection after setting it to false, you should be using the login id specified.

There is a little query you can run first to see "who you are in the database":
SqlCommand cmd1 = new SqlCommand("Select SYSTEM_USER + ' (' + USER_NAME() + ')'", conn);
string ret_val_str = cmd1.ExecuteScalar().ToString();

Open in new window


On my system, when I log in with trusted security and the application pool set to id "LocalSystem", this shows "dbo (dbo)", I didn't even know but apparently in my local (SQL express) database security settings this machine account has full access. When I change the connection to a specific sql login (user id 'ee'), it shows "ee (ee)". This sort of difference is what I would expect in your case as well.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Please see the attached screenshot. I encountered this issue in Win 08 server after I've published it. Yes I did change the codes before I raised it here!
t150.png
0
 
Robert SchuttSoftware EngineerCommented:
Well it still uses the same login, so something is definitely fishy here. Could it have something to do with the 2 projects you referred to earlier?

Also, can you describe your environment like I did in the last paragraph of my previous post? Which Application Pool identity is used etc?

One of my first options was to give that account access to the database if that is what you want, did you have a look at that or are you sure you don't want to take that route? (even if only for a test)
0
 
HuaMinChenBusiness AnalystAuthor Commented:
The above message is from the below exception
            ..
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT COunt(*) FROM user_master where user_abbr=@par_id and user_password=@par_pass", conn);
                cmd.Parameters.Add("par_id", SqlDbType.VarChar).Value = tb_userid.Text;
                cmd.Parameters.Add("par_pass", SqlDbType.VarChar).Value = tb_password.Text;
                ret_val = Convert.ToInt32(cmd.ExecuteScalar());

                if (ret_val == 0)
                {
                    Userid.InnerText = "Invalid User ID/Password - '" + tb_userid.Text.Trim() + "/" + tb_password.Text.Trim() + "' is entered!";
                }
                else
                {
                    Userid.InnerText = "You have connected to the database successfully!";
                    Session["sv_userid"] = tb_userid.Text.Trim();
                    Session["sv_userpass"] = tb_password.Text.Trim();

                    Response.Redirect("~/Edit_User/Edit_User.aspx");
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                Userid.InnerText = "Unable to check against the database: ";
                Userid.InnerText += ex.Message;
            }
            finally
            {
                conn.Close();
            }
            ...
           
I suspect the exception does come due to "Response.Redirect(...". As I now encounter the problem after I've published it, it is better that you use the same environment like me, which is to publish the projects in IIS of Win 08 server environment, right?
0
 
Robert SchuttSoftware EngineerCommented:
> it is better that you use the same environment like me

Right. Unfortunately that's not an option for me.

How do you figure the Response.Redirect is responsible? Try putting a breakpoint in the code please to see if there's any chance of something else going on than my assumption until now that it's just a problem in the connection.

By the way, the code you posted does nothing to assure me you're setting up the right connection before that. I want to assume you're doing it right but it's not in the code and the results say otherwise. So sorry for doubting you but if you can include the relevant code it would be better. And still better if you can give the description of the 'flow' of what's going on like I asked. Even by just describing it you may realize where the problem is yourself ;-)
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks a lot Robert. How can I know which line is causing the exception to happen, as I am testing the project inside the server machine (I am not running it directly from Web developer). So I cannot have a breakpoint there.
0
 
Robert SchuttSoftware EngineerCommented:
well, I have used a 'low-tech' way to determine that in the past like this:
string whereAreWe = "start";
            try
            {
                whereAreWe = "conn.Open";
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT COunt(*) FROM user_master where user_abbr=@par_id and user_password=@par_pass", conn);
                cmd.Parameters.Add("par_id", SqlDbType.VarChar).Value = tb_userid.Text;
                cmd.Parameters.Add("par_pass", SqlDbType.VarChar).Value = tb_password.Text;
                whereAreWe = "cmd.Execute";
                ret_val = Convert.ToInt32(cmd.ExecuteScalar());

                if (ret_val == 0)
                {
                    Userid.Text = "Invalid User ID/Password - '" + tb_userid.Text.Trim() + "/" + tb_password.Text.Trim() + "' is entered!";
                }
                else
                {
                    Userid.Text = "You have connected to the database successfully!";
                    Session["sv_userid"] = tb_userid.Text.Trim();
                    Session["sv_userpass"] = tb_password.Text.Trim();
                    whereAreWe = "Response.Redirect";
                    Response.Redirect("~/Edit_User/Edit_User.aspx");
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                Userid.Text = "[" + whereAreWe + "] Unable to check against the database: ";
                Userid.Text += ex.Message;
            }
            finally
            {
                conn.Close();
            }

Open in new window

0
 
HuaMinChenBusiness AnalystAuthor Commented:
Many thanks Robert. I tried with these. I don't know why I still get the same message.
            ...
            string temp = "";

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Mssqlconn"].ConnectionString);
            int ret_val = 0;

            try
            {
                conn.Open();
                temp = "1xx";
                SqlCommand cmd = new SqlCommand("SELECT COunt(*) FROM user_master where user_abbr=@par_id and user_password=@par_pass", conn);
                cmd.Parameters.Add("par_id", SqlDbType.VarChar).Value = tb_userid.Text;
                cmd.Parameters.Add("par_pass", SqlDbType.VarChar).Value = tb_password.Text;
                ret_val = Convert.ToInt32(cmd.ExecuteScalar());
                temp = "2xx";

                if (ret_val == 0)
                {
                    Userid.InnerText = "Invalid User ID/Password - '" + tb_userid.Text.Trim() + "/" + tb_password.Text.Trim() + "' is entered!";
                }
                else
                {
                    temp = "3xx";
                    Userid.InnerText = "You have connected to the database successfully!";
                    Session["sv_userid"] = tb_userid.Text.Trim();
                    Session["sv_userpass"] = tb_password.Text.Trim();
                    temp = "4xx";
                    ScriptManager.RegisterStartupScript(Page, this.GetType(), "msgbox", "alert('test3')", true);
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                Userid.InnerText = temp+"Unable to check against the database: ";
                Userid.InnerText += ex.Message;
            }
            finally
            {
                conn.Close();
            }
            ...
0
 
Robert SchuttSoftware EngineerCommented:
well yes it's possible because your initial value for temp is empty so it's probably just the conn.Open() failing and temp is empty so the message is the same.

I would say we're back to square 1: you need to make sure you're connecting in the right way and/or make sure the user that is connecting has the correct permissions in the database.

Can you try initializing temp like this to kill 2 birds with 1 stone:
string temp = "[" + conn.ConnectionString + "]";

Open in new window

0
 
HuaMinChenBusiness AnalystAuthor Commented:
Many thanks. Here is why I've got as per your way.
[Data Source=192.168.168.1\ss2012;Initial Catalog=web_schema;Integrated Security=True;User ID=ws_login;Password=mypass]Unable to check against the database: Login failed for user 'WIN08\WIN-BVDIC8GIAHI$'.
0
 
Robert SchuttSoftware EngineerCommented:
Ok. So it _is_ still using "Integrated Security=True". Please retrace your steps and make sure you try the connection with "Integrated Security=False".
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Many thanks Robert. Within Web.config, it is
Integrated Security=False;

for connectionString. Why is it different from what we have currently shown above?

Many Thanks & Best Regards,
HuaMin
0
 
Robert SchuttSoftware EngineerCommented:
Like I said before, I can't be sure because you haven't posted the part of your code where you create the connection/set the connectionstring.

One reason could be that you created a second connectionstring in your config and the wrong one may be referenced (but that would probably be too easy).

Also, you haven't cleared up the remark you made about a second project which uses its own config and connection so maybe the problem is there, if you want me to think about that, you're gonna have to explain the flow of your project/website at least a little bit.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Many thanks. I expect to call one other project upon that the button is clicked by the currently showed codes. The control is still in the 1st project, isn't it? Can you please provide your email to me for me to send you the project for taking a look, as I can't attach the zip file to this?

Many Thanks & Best Regards,
HuaMin
0
 
Robert SchuttSoftware EngineerCommented:
Well that depends, if you call a function from a dll or another website/web application then the config from that site/application will be used.

In general it might be a good idea to try and find a way to make some sort of "single point of config" so you can have different environments (test/production) which use the same config for all parts of the project/website.

Sure, just use any account on the domain "schutt.nl".
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Can we remove any dll there inside the project for making sure that only the recent connectionString within Web.config file will be used to build the connection?

Many Thanks & Best Regards,
HuaMin
0
 
Robert SchuttSoftware EngineerCommented:
I don't really understand that question. If you start deleting dll's from the project won't it just stop working altogether? Or do you mean integrate the functionality into 1 project? That should be possible.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks. I meant to remove any confusion in the current project to make sure only one connectionString will be applicable for the project.
0
 
Robert SchuttSoftware EngineerCommented:
Ok, I'd have to have a look at the project then. I haven't seen any mail yet, by the way it may also be possible to upload your zip file on http://www.ee-stuff.com/
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks. Here is the one and the password is 1234 to open it.
https://skydrive.live.com/embed?cid=17EC75244BAC022F&resid=17EC75244BAC022F%21231&authkey=AI8SxrYirkEYlts

Many Thanks & Best Regards,
HuaMin
0
 
Robert SchuttSoftware EngineerCommented:
Ok, I downloaded the zip. I see only one project in the solution, so I'm not sure yet where the 2nd connection comes in. In this web.config there is only 1 active connection so that should work ok.

This code should reference the new connectstring:
ConfigurationManager.ConnectionStrings["Mssqlconn"].ConnectionString

Open in new window

0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks a lot.
0
 
Robert SchuttSoftware EngineerCommented:
So, either replace the code
ConfigurationManager.ConnectionStrings["Mssqlconn"].ConnectionString

Open in new window

with
ConfigurationManager.ConnectionStrings["Mssqlconn1"].ConnectionString

Open in new window

OR, as you now have commented out the old connection string in web.config and won't be causing a double entry anymore, just change that entry's name back to "Mssqlconn" (without the 1). Either should work (but don't do both! ;-).

EDIT: edited slightly to match previous edited author comment :-)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 18
  • 16
Tackle projects and never again get stuck behind a technical roadblock.
Join Now