adapter preview-data succeeds, but ASPX page says 'Cannot open database'

[bennett]

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

 

[bennett]

By the way I found out why this wasn't working:
The databases that are automatically installed as part of the .Net SDK
(like Portal and Northwind) apparently have the ASPNET user already
added as a user, but the new databases that I was creating by
right-clicking the server name in Visual Studio and picking "New
database", did not.

So, when I was testing the adapter and previewing data, I was doing
that under my own username and it was working, but when running the
page as an ASP.Net app, it was running under the ASPNET user and
couldn't access the database.

This probably would have been obvious long ago except that MSDE doesn't
come with any GUI that lets you browse databases and see the users that
are added to each database; however someone pointed me in the direction
of the Microsoft SQL Web Data Administrator for MSDE:
http://www.microsoft.com/downloads/...98-c57a-419e-acbc-2a332cb7f959&displaylang=en
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.