HarryDuno16957
asked on
C# ASP.NET Forms Authentication Isn't Work
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.SqlE xception: SQL Server does not exist or access denied. at System.Data.SqlClient.Conn ectionPool .GetConnec tion(Boole an& isInTransaction) at System.Data.SqlClient.SqlC onnectionP oolManager .GetPooled Connection (SqlConnec tionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlC onnection. Open() at _ASP.LoginPage_aspx.Custom Authentica te(String username, String password)
Here are my two files:
Global.asax:
<%@ Import Namespace="System.Data.Sql Client" %>
<%@ Import Namespace="System.Security .Principal " %>
<script language="C#" runat="server">
void Application_AuthenticateRe quest (Object sender, EventArgs e)
{
HttpApplication app = (HttpApplication) sender;
if (app.Request.IsAuthenticat ed &&
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;s erver=loca lhost;data base=xiaow eblogin;ui d=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.Sql Client" %>
<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.GetRed irectUrl
(UserName.Text, Persistent.Checked);
FormsAuthentication.SetAut hCookie (UserName.Text,
Persistent.Checked);
if (Persistent.Checked) {
HttpCookie cookie =
Response.Cookies[FormsAuth entication .FormsCook ieName];
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;s erver=loca lhost;data base=xiaow eblogin;ui d=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>
System.Data.SqlClient.SqlE
Here are my two files:
Global.asax:
<%@ Import Namespace="System.Data.Sql
<%@ Import Namespace="System.Security
<script language="C#" runat="server">
void Application_AuthenticateRe
{
HttpApplication app = (HttpApplication) sender;
if (app.Request.IsAuthenticat
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;s
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.Sql
<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.GetRed
(UserName.Text, Persistent.Checked);
FormsAuthentication.SetAut
Persistent.Checked);
if (Persistent.Checked) {
HttpCookie cookie =
Response.Cookies[FormsAuth
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;s
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>
ASKER
I am using MySQL, but I am also using .Net 1.1.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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