I'm trying to write an ASPX page that uses an OleDbDataAdapter to get 
data from a SQL Server database.  When I test the connection, it 
returns success, and when I preview the data using the Adapter's 
"preview data" right-click option, it works, but when I load the actual 
page, I get the error "Cannot open database requested in login 
'cookiecutter'. Login fails." 


It seems to work when I use a pre-existing database like Northwind or 
Portal, but not when I try creating a new one.  But it looks like I'm 
creating them correctly.  Here are the exact steps: 


1) In Visual Studio 2003 I go to Server Explorer, right-click "Data 
Connections" and pick "Create new SQL Server database".  I specify 
BENNETTS_3_2002\NetSDK as the server name and "cookiecutter" as the new 
database name.  I select "Use Windows NT Integrated Security" as well. 


2) The connection now appears, as expected, under Data Connections.  If 
I open its properties it shows BENNETTS_3_2002\NetSDK as the server 
name, Use Windows NT Integrated Security is selected, and 
"cookiecutter" is listed as the database to select.  If I click "Test 
Connection" it returns success. 


3) In the ASP.Net project, I drop a new instance of an OleDbDataAdapter 
onto a Web form, specify BENNETTS_3_2002\NetSDK.cookiecutter.dbo as the 
data connection, and specify "SELECT wbuser.* FROM wbuser" as the 
select command, the adapter gets created successfully.  If I 
right-click the adapter and click "Preview Data" I can see the contents 
of the wbuser table being loaded. 


4) BUT now if I put some code in the codebehind page like 
DataSet objDataSet = new DataSet(); 
this.oleDbDataAdapter1.Fill(objDataSet); 


when I actually try loading the page in the browser, I get the error 
"Cannot open database requested in login 'cookiecutter'. Login fails." 


Any idea what could cause this, if test-connection and preview-data 
were reporting success?  Could it have something to do with how I'm 
creating the new database?  Because if I go through the same steps 
above but use a pre-existing database like Northwind or Portal, it 
works.  It only fails for the new databases I create.  But I thought I 
was creating them correctly, choosing "Use Windows NT Integrated 
Security". 


Help much appreciated, thanks! 


-Bennett

The problem is the user context under which ASP.Net runs. You are
using NT authentication for database. When you used VS.NEt to create
the database and later on testing, the connection sees your
credentials as the user calling into the database so it works fine.
But when you used ASP.Net page to call into the database, it choked.
The reason being that ASP.Net provess runs under ASPNET user account
which is not a previleged account and has no rights to connect to
database. Here are the options that you have..

1. Tuen off Anonymous authentication for your web app in IIS and only
keep windows auth.
2. In web.config make sure that you have windows authentication set.
3. tuen on impersonation in web.config.

Now you should be all set.

Thanks for your help.  I'm trying to keep it simple though and just
change permissions on the database itself, so that each new ASP.Net
application can access it, without making lots of changes on the
ASP.Net applications.  (One reason I don't want to change too many
settings in the project is that I usually then deploy the project to a
remote server using "Project->Copy Project", and the only thing I have
to change is the database connection string.  The more settings I
change in the project itself, the more I'm worried about breaking
something when deploying it somewhere else.)

My two web applications 'localbrainjammer' and 'cookiecutter' have all
of the exact same IIS settings (anonymous access enabled, using the
account IUSR_BENNETTS_3_2002, where "BENNETTS_3_2002" is my computer
name).  However when either of them attempt to access a database table
on BENNETTS_3_2002\NetSDK.Portal (a database that I think was
installed with the .Net Framework SDK), they succeed, but when they
try to access a database table in the dattabase
BENNETTS_3_2002\NetSDK.cookiecutter (a new database I created in
Server Explorer in VS .Net 2003), they get the access denied error.

How do I edit the permissions on the
BENNETTS_3_2002\NetSDK.cookiecutter database so that I can access it
from the ASP.Net projects that I've created, without changing settings
in the ASP.Net projects themselves?

Thanks again for your help so far!

I found out how to fix it -- someone pointed me in the direction 
of the Microsoft SQL Web Data Administrator for MSDE: 
http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57... 
and after I downloaded that program, it was obvious that the ASPNET 
user was already added to Northwind and Portal but not to the 
'cookiecutter' database, so I used the tool to add the ASPNET user to 
that database and everything worked.  (The other option was to set up 
my application to do impersonation so that when accessing the aspx 
pages, the codebehind code would run as my username, but I couldn't get 
that working.) 

Since Microsoft gives this tool away for free I don't know why they 
just didn't go ahead and bundle it with MSDE to save people a lot of 
headaches, but anyway, that's one way to do it.

Approach of giving ASPNET user access to server works but it is not a
very safe choice. You pretty much open your DB open to a low
previlleged account.

Impersonation option will not work if your database lives on a
separate machine than web server because credetials don't cross
machine boundary unless kerberos is enabled for servers.

If its all intranet stuff then giving ASPNET user access to DB will kind of work.