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>
