Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Data Access problem in ASP.NET webform

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
gleznov
Asked:
gleznov
  • 11
  • 2
  • 2
2 Solutions
 
iboutchkineCommented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
iboutchkineCommented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
Anyone?  
0
 
etmendzCommented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
I also just added:

SqlConnection1.Open()

after the SqlConnection1.ConnectionString = connectionString

and before the SqlDataAdapter1.Fill(DS_Login1)

but same results.

JP
0
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
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
 
gleznovAuthor Commented:
WOOHOO!  Some weird tweak worked.

JP
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 11
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now