SQL Server log in failed - why?

[FuzzBox]

Could someone explain why I can’t get access to my SQL database. After all I did create it myself It’s a simple database with two linked tables.

The database is on the same PC as SQL server. I have a web page that will display the contents of the database on a data grid at the click of a button (based on a Karl Moore Tutorial). My details:

Operating System Win 2k
IIS version 5
I’m using the NETSDK version of SQL server
My PC name is COCLAP-9
My SQL server is called COCLAP-9\NETSDK
My database is called KeepFit

So, my connection string should look like this:

Dim objConnection As New SqlClient.SqlConnection _
("server=(local)\NETSDK;Trusted_Connection=yes;database=KeepFit")

I have been told that I can’t use Trusted_Connection but since that both the database and the application are on the same PC this is ok. This also worked under the Beta 2 version, but not now.

I had to reinstall my Win 2k since Beta 2 because I could not uninstall Beta 2 no way, no how, without reformatting the hard disk!

Could Win 2k security be linked to the following error message? Anyway, when I hit F5 I get :
Cannot open database requested in login 'KeepFit'. Login fails. Login failed for user 'COCLAP-9\ASPNET'.

The Source Error is
objConnection.Open()

I have read a lot of Microsoft's documentation and have played with machine.config, web config, passwords, etc.. but to no avail. I have tried all my work colleagues but none have used SQL server. I’m really stuck on this one, so any help would be appreciated.

Thanks for you time

[axion_sa]

Have you checked the Users for your database?

[shpadoinkal]

I am experiencing the same exact problem.

I am reading the Karl Moore book, and I am currently on page 202 going over SQL transactions. I got to the first part where you can test the find users feature, and I keep getting an error. I think there is a problem with my SQL configuration. The error message is :

Cannot open database requested in login 'KeepFit'. Login fails. Login failed for user 'JBECKSERVER\ASPNET'.

I am not sure where I can set logins. I did try OSQL and I added a login and password. When I try using this instead of a trusted connection I get the following error message:

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


Any help from the master would be greatly appreciated.
Thanks,

Jay

[FuzzBox]

axion_sa,
I have checked the Users for the database. I have it as ASPNET, is this correct? However, I'm using Windows NT Integrated Security Therefore, If I'm using this then I should use Trusted_connection=yes/true Is that correct then?

What about Users and Passwords, is this important now? I have ASPNET as an Administrator now, rather than a Debugger User. What should I do?

Hey shpadoinkal, how you doing with this?

Thanks for your time

[Serge]

Yes trusted_connection has to be in place. ASPNET account has to exist in windows. So the connection string has to be something like this:

server=COCLAP-9\NETSDK;database=KeepFit;trusted_connection=yes

[FuzzBox]

Hey Guys,
I got my DB to work by changing ASPNET from a Debugger to an Administrator. Administators have complete and unrestricted access to the PC or Domain

Change it in the Users and Passwords in Control Panel. You have to reboot and then it's away. Kuwel

[Musician]

Well giving administrator rights to ASPNET user is not the best solution.
You need to give the ASPNET windows account rights to access the database.
When you install the NETSDK databse it has scripts that installs databases and also gives rights to the "guest" to access them.
This should be sufficient. To do this execute the following:-

use databasename
EXEC sp_adduser 'guest'

If you have problems running this then perhaps give the ASPNET user administrator rights to do it and then put it back to user.

[gijsj]

If you use Windows Integrated Security in IIS (or Basic Auth.) you could also set impersonation=true in web.config and enable the specific windows account access to sql.

[FuzzBox]

Hhmm, that sounds interesting. I'll have play over this weekend.

shpadoinkal did you do this? Have you sorted your problem, if so how did you do it?


Thanks for input

[FuzzBox]

I found sometime this morning and…

Musician, I'm very new to this stuff. Could you explain again for a beginner please.
Do I give ASPNET a windows account like this?

From Users and Passwords, select the Users tab highlight ASPNET
Click on Properties… There are two tabs General and Group Membership. The General tab seems straight forward (entries from top to bottom: ASPNET, aspnet_wp account, Account for running ASP.NET Worker process)
The Group Members tab poses the problem. Could you explain from here. OR am I totally wrong here?

How would I execute the commands? Where do I do that?

I seem to be sinking…

thanks for your time

[Musician]

The commands I gave you are sql server commands. Nothing to do with windows.
I am talking about giving rights to the windows user ASPNET to sql server. This is done IN sql server.

[FuzzBox]

Aahh! I'm using the NETSDK version of SQL Server. I understand it is fully functional but a cut down version for VS.NET. So, all my work is done within the VS.NET environment. So, how do I issue these commands from VB.NET?

Or should I go for the Web Config idea, impersonation=true ?


Thanks for your time

[Musician]

If you have no standard way of issuing commands to sql server then try the impersonate=true method