Can't open my SQL database - why?

[FuzzBox]

Hi all,
I’m running through a simple SQL server web application and I get an error message that reads:

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'COCLAP-9\ASPNET'.

Here is my connection string:

Dim objConnection As New SqlClient.SqlConnection _
("server=COCLAP-9\VSDOTNET;database=LOTR;trusted_connection=true")

As you can see, the connection string reads COCLAP-9\VSDOTNET (and not 'COCLAP-9\ASPNET' as in the error message - Hhmm ). When I created the database I left the ‘Use Windows NT Integrated Security’ option button checked.

My server name is COCLAP-9 and the database name is LOTR. Which are correct in the connection string

when I run the application it shows the Default.aspx page as expected but as soon as I try to Open to the database, withthis line objConnection.Open() I get the error message.

I can’t see anything wrong here, can you?
Why do I need to login on a simple database application?

Any ideas?

Thanks for your time

[bob323]

I am not a real wiz at this, but I think you do need to put in the login and password, even if you just go login="" pwd=""

[FuzzBox]

Sorry Bob that didn't work either. I'm sure my connection string is correct without using the login and password parameters. You can add them is you want the security. At least that's the way I see it.

[FuzzBox]

I forgot to mention that the Database and the web application are on the same PC. Also, this priciple worked with the VS.NET Beta 2 version! The only thing to change is the server name (which is correct)

Any ideas why this is the case?

[FuzzBox]

Something more to add:
I think I need to removed the trusted_connection and add a password and user name. But I'm confused now. I need a user name and password to log on to my PC at work. Surely this can't have anything to do with it, can it?

I created a small database myself on the PC (I'm trying to learn SQL server by giving myself a project) through Server Explorer. Then I want to add to it through ASP.NET. Simple enough really, but this has held me up for a while now. I really want to understand what's going on and it seems I need further help.
Anyway, what should I do about a user ID and Password? Do I need to congfigure them somewhere? Or do I make them up?

Thanks for you time

[Musician]

The error means that the use ASPNET which is the user that asp.net applications use has not got sufficient rights to access the instance of sql server.
I had similar problems and in the end I allowed both windows and sql server security and created a login name and password in sql server which solved my problems.
Then again I think there is a setting in the web.config that you can change to tell your asp.net app to use a different identity which is "impersonate" by default. It may need to be changed to work in windows authentication.

[FuzzBox]

Musician, thanks for your reply. I'm new to this SQL server stuff so I'm not sure what you mean. I understand what you're saying about User ASPNET and access rights. My SQL Server is supplied from VS.NET, does this matter?

I have had a look in the Web Config and found:
sqlConnectionString="data source=127.0.0.1;user id=sa;password="

Why is the password not complete?
How does this connection string compare to my connection string?
How do I get SQL Server to "impersonate" ?

Any further ideas?

thanks for your time

[Musician]

That connection string you have quoted is I believe in the session tag. It is meant for using a database for storing session variables and you alter it if you decide to use that session storage method.
So it has nothing to do with your problem.
The security area of web.config is:-

<authentication mode="Windows" />

I had a similar problem to you as I said and it seemed like it just stopped working. I could not find a solution so I used the sql server enterprise manager to allow sql server authentication on the database too. If you haven't got enterprise manager then I'm not sure how you can change the authentication mode.
Can you create a connection to the database in the server explorer of vs.net?
If so drag the connection onto a webform and see if the connection it creates will work. Other than that I can only suggest you read up on the security options of web.config of which I am far from being an expert unfortunately.

[FuzzBox]

Musician, thanks for that. I'll give it a go over this weekend along with some reading on general SQL server.

Cheers