?
Solved

C# ASP.NET Forms Authentication Isn't Work

Posted on 2006-07-02
6
Medium Priority
?
427 Views
Last Modified: 2008-02-01
I have a page that uses a MySQL data. It works fine. Then I wanted to add forms security to it, so I created a table of users, passwords, and roles. After every login and password was invalid, I printed out the exception and this is what it is:
System.Data.SqlClient.SqlException: SQL Server does not exist or access denied. at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at _ASP.LoginPage_aspx.CustomAuthenticate(String username, String password)

Here are my two files:

Global.asax:
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Security.Principal" %>

<script language="C#" runat="server">
  void Application_AuthenticateRequest (Object sender, EventArgs e)
  {
      HttpApplication app = (HttpApplication) sender;

      if (app.Request.IsAuthenticated &&
          app.User.Identity is FormsIdentity) {
          FormsIdentity identity = (FormsIdentity) app.User.Identity;

          // Find out what role (if any) the user belongs to
          string role = GetUserRole (identity.Name);

          // Create a GenericPrincipal containing the role name
          // and assign it to the current request
          if (role != null)
              app.Context.User = new GenericPrincipal (identity,
                  new string[] { role });
      }
  }

  string GetUserRole (string name)
  {
      SqlConnection connection = new SqlConnection
          ("Trusted_Connection=yes;server=localhost;database=xiaoweblogin;uid=root;pwd=");

      try {
          connection.Open ();

          StringBuilder builder = new StringBuilder ();
          builder.Append ("select role from users " +
              "where username = \'");
          builder.Append (name);
          builder.Append ("\'");

          SqlCommand command = new SqlCommand (builder.ToString (),
              connection);

          object role = command.ExecuteScalar ();

          if (role is DBNull)
              return null;

          return (string) role;
      }
      catch (SqlException) {
          return null;
      }
      finally {
          connection.Close ();
      }
  }
</script>

LoginPage.aspx:
<<%@ Import NameSpace="System.Data.SqlClient" %>

<html>
  <body>
    <h1>Please Log In</h1>
    <hr>
    <form runat="server">
      <table cellpadding="8">
        <tr>
          <td>
            User Name:
          </td>
          <td>
            <asp:TextBox ID="UserName" RunAt="server" />
          </td>
        </tr>
        <tr>      
          <td>
            Password:
          </td>
          <td>
            <asp:TextBox ID="Password" TextMode="password"
              RunAt="server" />
          </td>
        </tr>
        <tr>
          <td>
            <asp:Button Text="Log In" OnClick="OnLogIn"
              RunAt="server" />
          </td>
          <td>
            <asp:CheckBox Text="Keep me signed in" ID="Persistent"
              RunAt="server" />
          </td>
        </tr>
      </table>
    </form>
    <hr>
    <h3><asp:Label ID="Output" RunAt="server" /></h3>
  </body>
</html>

<script language="C#" runat="server">
  void OnLogIn (Object sender, EventArgs e)
  {
      if (CustomAuthenticate (UserName.Text, Password.Text)) {
          string url = FormsAuthentication.GetRedirectUrl
              (UserName.Text, Persistent.Checked);

          FormsAuthentication.SetAuthCookie (UserName.Text,
              Persistent.Checked);

          if (Persistent.Checked) {
              HttpCookie cookie =
                Response.Cookies[FormsAuthentication.FormsCookieName];
              cookie.Expires = DateTime.Now +
                  new TimeSpan (0, 0, 0, 8);
          }

          Response.Redirect (url);
      }
      else
          Output.Text = "Invalid login";
  }

  bool CustomAuthenticate (string username, string password)
  {
      SqlConnection connection = new SqlConnection
          ("Trusted_Connection=yes;server=localhost;database=xiaoweblogin;uid=root;pwd=");

      try {
          connection.Open ();

          StringBuilder builder = new StringBuilder ();
          builder.Append ("select count (*) from users " +
              "where username = \'");
          builder.Append (username);
          builder.Append ("\' and cast (rtrim (password) as " +
              "varbinary) = cast (\'");
          builder.Append (password);
          builder.Append ("\' as varbinary)");

          SqlCommand command = new SqlCommand (builder.ToString (),
              connection);

          int count = (int) command.ExecuteScalar ();
          return (count > 0);
      }
      catch (SqlException ex) {
        Response.Write(ex.ToString());
          return false;
      }
      finally {
          connection.Close ();
      }
  }
</script>
0
Comment
Question by:HarryDuno16957
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 27

Expert Comment

by:Sammy Ageil
ID: 17029021
are you using MYSQL or MSSQL?
you are stating MySSQL in your post and your code is using MSSQL
If you are trying to use MySql then you should look into creating membership profile
http://www.codeproject.com/aspnet/MySQLMembershipProvider.asp
0
 

Author Comment

by:HarryDuno16957
ID: 17029404
I am using MySQL, but I am also using .Net 1.1.
0
 
LVL 22

Accepted Solution

by:
Mohammed Nasman earned 2000 total points
ID: 17029450
SqlClient only for Sql server, and will not work with MySql

if you need to work with MySql, you could download the ODBC driver or OleDb drivers from mysql web site
or you can use  mysql connect/net as native .Net data provider for mysql
http://www.mysql.com/products/connector/net

and for  Forms Authentication to work, you have to modify your web.config to allow forms instead of the default one "windows"
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question