Connection to the database on MSDE

Hi,

I installed MSDE and the sample databases with it. They work fine.

Well I am using the server explorer of visaula studio standard 2002. I can see all the sample databases there and connect to them as well.

1.
---------------------
Now i created a new database named "testDb" and in a similar fashion i am using windows login thingy.

When i write my connection string in the program now it doesnt work

string connectionStr =@."Server=IMRAN\NetSDK;" + "Integrated Security=SSPI;" + "Connection Timeout=7;" + "Database=testDB;";

If i replace my Database clause to Northwind it works absolutely fine.

The ERROR it gives is:

Cannot open database requested in login 'testDB'. Login fails. Login failed for user 'IMRAN\ASPNET'.

Why is this happenning? :(.

============================================

2.
--------
How can i configure the MSDE to use the SQL Authentication.
It doesnt allow me to do that?

Thanx Heaps,
ImranHi Guys,

I got the solution for the first problem of mine.

We need to Grant Access to the ASPNET user

The ASP.NET process runs under the MACHINENAME\ASPNET account. In order for the data access samples to access the database, this user must be granted rights to the sample databases. To manually grant access to the ASPNET user, replace MACHINENAME with the name of your computer, and run the following commands from the command line. Note: The path to the osql.exe command must be located in your PATH environment variable.

osql -E -S (local)\NetSDK -Q "sp_grantlogin 'MACHINENAME\ASPNET'"

osql -E -S (local)\NetSDK -d DATABASENAME -Q "sp_grantdbaccess 'MACHINENAME\ASPNET'"
osql -E -S (local)\NetSDK -d DATABASENAME -Q "sp_addrolemember 'db_owner', 'MACHINENAME\ASPNET'"

and if you are using the Sql Server Enterprise then just open the Enterprise manager and give the database rights to the [machine_name]\ASPNET user in the database users

Hope that can be useful for someone there.

Thanx.
Imran|||Hi,
Glad you found an answer. BTW, ASPNET is the default user name for Windows 2000 and XP, but it's 'NETWORK SERVICE' on Windows 2003 Server running native mode with IIS 6 (default). I found that out the hard way. After adding 'NT AUTHORITY\NETWORK SERVICE' as a login to SQL Server (MSDE), I was able to have my application access the database using the VS and IE browsers - clearying a major roadblock for me.

Also, you can use the Web Data Administrator to create a new login, if you don't have Enterprise manager, or don't want to use OSQL.

MS sure didn't make ASP.NET appliaction database access clear and simple! Maybe ASP.NET 2.0 will, but at what cost?

Good luck with your projects.