Solved

Data Access problem in ASP.NET webform

Posted on 2004-10-13
15
266 Views
Last Modified: 2012-06-21
Hi,

      I was working on an inventory system as a windows application, now I need to redo it as a web application.  I started a new ASP.NET web application, put my logo, login/pass labels, a password-style textbox for password and a dropdownlist for users.  I dragged the SQLDataAdapter down and configured it with a select statement, then generated a dataset from it.  I use no password/login for the database.  This worked for a windows application, but when I try and run this program I get:

Server Error in '/OEL2' Application
Login failed for user '(null)' Reason: Not associated with a trusted SQL Server connection

How do I fix this?  I'm going through the book MCAD/MCSD Developing Web Applications w/ MS VB.NET and VC#.NET, but I don't see anything about this error.

JP

PS - I'm going to assign good points to this one because my whole project is at a standstill until I can at the very least begin to get functional Data access
0
Comment
Question by:gleznov
  • 11
  • 2
  • 2
15 Comments
 
LVL 28

Expert Comment

by:iboutchkine
Comment Utility
Are you using database authentication ot Windows authentication? Keep in mind that the user is ASPNET user. He must have all the rights and permissions
0
 

Author Comment

by:gleznov
Comment Utility
I've been messing around and have found that while the computer with the database has .NET framework installed, the database itself has no mention of an ASPNET user in it's profiles.  I'm trying to add one, but it's still not working.  Should I use Windows or DB Authent?  When I use Windows and set it to the computer's name/ASPNET it's still not working.  If I use DB authent, where do I put the password in the VB.NET program?  Do I redo the dataadapter/dataset and it prompts me?

JP
0
 

Author Comment

by:gleznov
Comment Utility
Is there a tool I can use with the .NET Framework somewhere that allows me to install this user, or can it be done easily manually?  I'm scared to mess with it on this primary server computer unless I know absolutely what I'm doing.  Adding a user in SQL Server on the other hand is easy.

JP
0
 
LVL 28

Accepted Solution

by:
iboutchkine earned 250 total points
Comment Utility
If not, then create an ASPNET user on the SQL box.  Assign it a password.
Now change your machine.config file on your IIS machine (and probably your
development machine).  Find the <processModel> section, change the tag
password="AutoGenerate" to the known password. You will have to also reset
the password for ASPNET on your IIS box to match this. You will have to run
iisreset or reboot after this change.

Then in SQL Enterprise Manager assign XXXX\ASPNET (or whatever name - LOCALCOMPUTERNAME\ASPNET)
 to "db_owner" role of your
database you want to connect to.


If they are on the same box you then shouldn't have to mess with the
machine.config, changing passwords, or even creating the ASPNET account.

By default the web page will try to connect to SQL as ASPNET.  This account
will already exist on the box if you have installed the framework.  Sounds
to me like you just need to give suffecient rights to your database inside
of SQL EM.  "db_owner" will do the trick. In the Security branch of EM, goto
Logins. Right-click and create a new Login.  On the General tab browse for
your local ASPNET account. On the Database Access tab, check your database,
and assign db_owner role.


Change security for ASPNET
Right click on the directory where your database is - choose properties - Then the sharing tab - then
click on permissions. Add the ASPNet user ( or the user that aspnet is running under ) to the shared
section Then, make sure it has 'Change' rights - Click Apply and you should be working fine then.
0
 

Author Comment

by:gleznov
Comment Utility
I'm still confused.  Let me ask one question first:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Shouldn't this say "Login failed for user 'ASPNET'" ?

OK I'm confused on the "SQL box" versus using SQL EM.  

We have one computer that has SQL Server and the database I'm needing to access.

Then I have my development machine using VB.NET with IIS but accessing the database on the other computer (when this app is finished, the web thing will be deployed and integrated with our intranet).  

You said to put a password, you also said I should have computername/ASPNET - can I do both?  If I use windows verification I can do computername/ASPNET, but I don't see a password slot - if I choose SQL Server Authentication, I have a password, but I don't get a computername/ASPNET, I just get ASPNET.  Which is better?

The .NET framework is installed on the computer with the database, but for some reason SQL Server has no ASPNET user, so it's not just the security settings.

I added a windows authent named ASPNET, selected the computer's name as Domain, then flipped to the Database Access tab and chose db_owner - the program still gives me the error - what's my next step?

JP
0
 

Author Comment

by:gleznov
Comment Utility
OK Update:

I do at least get:

Login failed for user 'ASPNET'. Reason: Not associated with a trusted SQL Server connection.

now.  here's what I did:

Opened web.config and added a key for user name/password:
<appSettings>
     <add key="mappedname" value="ASPNET"/>
     <add key="mappedkey" value="pass"/>
</appSettings

Then, here's my code (only the last line was there before):

 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        Dim connectionString As String
        Dim user As String = System.Configuration.ConfigurationSettings. _
            AppSettings("mappedname")
        Dim pass As String = System.Configuration.ConfigurationSettings. _
            AppSettings("mappedkey")
        connectionString = "data source=ga1cdc02;initial catalog=crmdb"
        connectionString &= ";user id=" & user
        connectionString &= ";password=" & pass
        SqlConnection1.ConnectionString = connectionString


        SqlDataAdapter1.Fill(DS_Login1)

    End Sub

It still doesn't work, but it's trying to log in with ASPNET and the password I gave it in SQLServer EM.  Why it's not working?  Dunno.  Data source may be wrong - that's the computer name on the network - should I have left it myserver or something else?

JP
0
 

Author Comment

by:gleznov
Comment Utility
After trying myserver (no sql server on this machine, and that was the error I got) I realized I probably had it right, but something about it just still isn't working.

JP
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:gleznov
Comment Utility
Anyone?  
0
 
LVL 6

Expert Comment

by:etmendz
Comment Utility
A typical connection string:
"Data Source=PCNameOrIPAddress;Initial Catalog=DBName;User ID=userID;Password=pw;Persist Security Info=True"

The userID must be existing in SQL Server. Do NOT use Windows authentication. It is safest to assign AT LEAST one SQL Server login account for the exclusive use of a web site/ASP.Net application. You can call these your "application login accounts". Users and administrators must use a different account assigned to them. Helps a lot when you start tracing and tracking SQL Server user activities...

You may store the entire connection string complete with user ID and password in your config file. Make this work first and then think about encryption later which should be easy to add.

Have fun...
0
 

Author Comment

by:gleznov
Comment Utility
But I still get:

Login failed for user 'ASPNET'. Reason: Not associated with a trusted SQL Server connection.

Here's the code I have now:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim connectionString As String
        Dim user As String = System.Configuration.ConfigurationSettings. _
            AppSettings("mappedname")
        Dim pass As String = System.Configuration.ConfigurationSettings. _
            AppSettings("mappedkey")
        connectionString = "data source=ga1cdc02;initial catalog=crmdb"
        connectionString &= ";user id=" & user
        connectionString &= ";password=" & pass
        connectionString &= ";Persist Security Info=True"
        SqlConnection1.ConnectionString = connectionString


        SqlDataAdapter1.Fill(DS_Login1)

    End Sub

This creates:
"data source=ga1cdc02;initial catalog=crmdb;user id=ASPNET;password=pass;Persist Security Info = True"
for the connectionString.  Then SqlDataAdapter1.Fill(DS_Login1) crashes it out.  I'm not even sure if it's using that connection info though - should I not have dragged/dropped a dataadapter and configured it with a SQL command, then generated a dataset from it? (that's SqlDataAdapter1 and DS_Login1, respectively)  

JP

0
 

Author Comment

by:gleznov
Comment Utility
I also just added:

SqlConnection1.Open()

after the SqlConnection1.ConnectionString = connectionString

and before the SqlDataAdapter1.Fill(DS_Login1)

but same results.

JP
0
 

Author Comment

by:gleznov
Comment Utility
Could someone please just run down again everything I'm supposed to do in:

SqlServer (this is what I've created ASPNET w/ a password in)
IIS (I haven't done anything in this on either my computer or the server (database) computer)

SQL box, IIS box - I don't know what that means?  Is that the machine that sqlserver is on and the machine IIS is on (development)?  Does this mean to make an account under sqlserver/IIS respectively on those computers, or is making a SQL box/IIS box user something different?

JP
0
 
LVL 6

Assisted Solution

by:etmendz
etmendz earned 250 total points
Comment Utility
0
 

Author Comment

by:gleznov
Comment Utility
OK,

     After a week with two threads on here, going all through MSDN, working with another IT guy who can't turn anything up, let me start all over with what I've got now:

     I have a program on my machine.  This program is also referenced from a server machine's IIS as a virtual directory which references the physical directory on my machine.  On this server machine is a SQL Server database I need access to using my web application program.  

      On the server machine, I have an administrator name/password.  I have created the same admin name/password in SQL Server on that machine with full access to the database table I'm trying to access.  When I try passing this as a name/password for authentication, it doesn't work.  When I pass nothing, it still doesn't work (anonymous).

     The error I get every time I run the program is this:

Login failed for user '(null)'.  Reason: Not associated with a trusted SQL Server connection.
or
Login failed for user 'admin'.  Reason: Not associated with a trusted SQL Server connection.

I have no idea how to get around this, and noone's been able to help, so I'm hoping maybe someone will come up with a few new ideas to try.  My code is as follows:

   Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim connectionString As String
        'Dim user As String = System.Configuration.ConfigurationSettings. _
        '    AppSettings("mappedname")
        'Dim pass As String = System.Configuration.ConfigurationSettings. _
        '    AppSettings("mappedkey")
        connectionString = "data source=ga1cdc02;initial catalog=crmdb"
        'connectionString &= ";user id=" & user
        'connectionString &= ";password=" & pass
        'connectionString &= ";Trusted_Connection=true"
        'connectionString &= ";Persist Security Info=False"
        SqlConnection1.ConnectionString = connectionString
        'SqlConnection1.Open() '?

        SqlDataAdapter1.Fill(DS_Login1)
    End Sub

The remmed out part is what I unrem when I want to pass a username/password - the web.config file has them set up.  

Any ideas?

JP
0
 

Author Comment

by:gleznov
Comment Utility
WOOHOO!  Some weird tweak worked.

JP
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now