Trouble accessing db while using ASP.Net

B

BFlaherty

Group,


I read Jeff Prosise's article: "An Introductory Guide to Building and
Deploying More Secure Sites with ASP.NET and IIS, Part 2" as well as
part 1. I think that articles are great. I had no problems running
the example code in the first article, but I am having problems with
the example as outlined in Figure 4. Specifically, I seem to be
having troubles opening a connection to the database, and that's why I
am posting to this group. When I try to open the connection
("connection.Open ();"), I am receiving this exception:

"Exception Message: Login failed for user 'FormUser'. Reason: Not
associated with a trusted SQL Server connection.
Stack Trace: 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)
Invalid login
User name: jeff"
[end of exception]


The user "FormUser" was created by using this script:

EXEC sp_addlogin 'FormUser', 'formuser', 'WebLogin'
EXEC sp_grantlogin 'BILLDESKTOP\FormUser'
EXEC sp_grantdbaccess 'BILLDESKTOP\FormUser', 'formuser'


I have attached a copy of my LoginPage.aspx as well as the associated
web.config file. Can you tell me what is going wrong here? I have
tried to do research on this error, and as far as I can see, I have
taken care of all of the isses. Also, if I turn impersonation on, I
get the same error, but complaining about a differant user.


I am using MSDE and Windows XP Pro.

Also, I did send a similar EMail to the author, but I am sure that he
gets a lot of bulk email from confused people like me, so I hope that
one of you could help me.

Thanks,
Bill

_________________________________________
login.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))
FormsAuthentication.RedirectFromLoginPage (UserName.Text,
Persistent.Checked);
else
Output.Text += "Invalid login" + "<br>" + "User name: " +
UserName.Text;
}

bool CustomAuthenticate (string username, string password)
{
SqlConnection connection = new SqlConnection
("server=BILLDESKTOP\\JMSDEV1;database=weblogin;Integrated
Security=SSPI;uid=FormUser;pwd=formuser");

try {
connection.Open (); //this is where the excpetion is thrown

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 se) {//this is where the exception is caught
Output.Text += "Exception Message: " + se.Message + "<BR>";
Output.Text += "Stack Trace: " + se.StackTrace + "<BR>";
return false;
}
finally {
connection.Close ();
}
}
</script>

____________________________
web.config:

<configuration>
<system.web>
<authentication mode="Forms">
<forms loginUrl="LoginPage.aspx" />
</authentication>
</system.web>
</configuration>
 
J

Jared

I think Integrated Security is used for a trusted connection only, I don't
think you can supply a user name and password with it. Try it with the
following connection string.

"server=BILLDESKTOP\JMSDEV1;database=weblogin;User
ID=FormUser;Password=formuser;Trusted_Connection=False;"

server=BILLDESKTOP\\JMSDEV1;database=weblogin;uid=FormUser;pwd=formuser

BFlaherty said:
Group,


I read Jeff Prosise's article: "An Introductory Guide to Building and
Deploying More Secure Sites with ASP.NET and IIS, Part 2" as well as
part 1. I think that articles are great. I had no problems running
the example code in the first article, but I am having problems with
the example as outlined in Figure 4. Specifically, I seem to be
having troubles opening a connection to the database, and that's why I
am posting to this group. When I try to open the connection
("connection.Open ();"), I am receiving this exception:

"Exception Message: Login failed for user 'FormUser'. Reason: Not
associated with a trusted SQL Server connection.
Stack Trace: 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)
Invalid login
User name: jeff"
[end of exception]


The user "FormUser" was created by using this script:

EXEC sp_addlogin 'FormUser', 'formuser', 'WebLogin'
EXEC sp_grantlogin 'BILLDESKTOP\FormUser'
EXEC sp_grantdbaccess 'BILLDESKTOP\FormUser', 'formuser'


I have attached a copy of my LoginPage.aspx as well as the associated
web.config file. Can you tell me what is going wrong here? I have
tried to do research on this error, and as far as I can see, I have
taken care of all of the isses. Also, if I turn impersonation on, I
get the same error, but complaining about a differant user.


I am using MSDE and Windows XP Pro.

Also, I did send a similar EMail to the author, but I am sure that he
gets a lot of bulk email from confused people like me, so I hope that
one of you could help me.

Thanks,
Bill

_________________________________________
login.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))
FormsAuthentication.RedirectFromLoginPage (UserName.Text,
Persistent.Checked);
else
Output.Text += "Invalid login" + "<br>" + "User name: " +
UserName.Text;
}

bool CustomAuthenticate (string username, string password)
{
SqlConnection connection = new SqlConnection
("server=BILLDESKTOP\\JMSDEV1;database=weblogin;Integrated
Security=SSPI;uid=FormUser;pwd=formuser");

try {
connection.Open (); //this is where the excpetion is thrown

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 se) {//this is where the exception is caught
Output.Text += "Exception Message: " + se.Message + "<BR>";
Output.Text += "Stack Trace: " + se.StackTrace + "<BR>";
return false;
}
finally {
connection.Close ();
}
}
</script>

____________________________
web.config:

<configuration>
<system.web>
<authentication mode="Forms">
<forms loginUrl="LoginPage.aspx" />
</authentication>
</system.web>
</configuration>
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exception when accessing mdf file when using IIS and aspnet_wp.exe 4
ASP.NET web.config MSDN Example 6
accessing db without calling a function? 1
using asp.net databinding controls is more efficient or buildingstrings manually? 2
Page shows blanc when using template cc and expressions (<%# %>) 3
File Upload question 3
ASP.NET connection file problem 2
membership createuserwizard add extended property. 2

Top