sql connection for ASP.Net vs VB.Net

[Schimsky]

Hi,

I'm really new to sql server. I just installed MSDE 2000 on our company's network server. Then I quickly wrote a VB.Net program to access a database on the server. I used this code:

Dim objConn As New SqlConnection("workstation id=steve;packet size=4096;integrated security=SSPI;data source=SQLSERVER;persist security info=False;initial catalog=TXNIC")

Dim objCommand As New SqlCommand
Dim objDa As New SqlDataAdapter
Dim objDs As New DataSet

objCommand.CommandText = "Select * From Candidates"
objCommand.Connection = objConn
objDa.SelectCommand = objCommand
objDa.Fill(objDs, "Table1")
MessageBox.Show(objDs.Tables("Table1").Rows(10).Item("LastName"))
objConn.Close()

The above Code Worked perfectly!!!

Then I took the same code as above, (except for the Messagebox line), and placed in an ASP.Net web page. It bombed with this error:

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

Does anyone know how to fix this?

Thanks.

Steve

 

[shatch]

The connection made with ASP.net uses the system account on the web server, try changing the connection string to use SQL authentication and not Windows

data source=localhost;initial catalog=testdb; UID=myuser; PWD=mypass

replace the uid and pwd with whatever you use and see if that works.

 

[NoCoolHandle]

shatch's response is correct and will work fine if you accept non-trusted (sql server) logins - like the infamous SA account...

If you don't, you will need to make sure of the account that your webserver is using to connect to SQL.. USUALLY (90% of the time) it is ASPNET, but I have found that depending on how I author the site, it could also very easily be the iusr_machineName account.

To find out open IIS MAnager/Site/Properties/Directory Security

Click Edit on the Anonomous Access/Authentication Section and then see what account iis uses.. Provide that account a login and useraccount on your MSDE database, or just change it to ASPNET and then add the Account as a login and also as a valid user of the database you are building your objects in.. Then be prepared to add permissions to access your tables/views/storedprocs/functions..

Don't want to make it sound daunting, but there are a few steps you need to look after to make SQL work.


HTH


Rob