How do I enable the web server to create a database?

[John Bunch]

I have installed Visual Studio 2005 Team Suite Beta 2, including SQL Server
2005 Express Edition April CTP, on Windows XP Professional SP2, including IIS
5.1. The SQL Server (SQLEXRPESS) service is running under the NT
AUTHORITY\NETWORK SERVICE account. This machine is named VSDev01WSWP01.

I am also running SQL Server 2000 on a Windows Server 2003 computer named
VSDev01SQL02. This computer contains my application's database, which I have
integrated with the tables created by the aspnet_regsql tool.

Here are the first few lines of C:\Inetpub\wwwroot\WebPortal\web.config,
which includes AspNetSqlProvider:
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<add name="SqlServices"
connectionString="Provider=SQLOLEDB;Server=VSDev01 SSQL02;Integrated
Security=SSPI;Data Source=Web Portal;Initial Catalog=WebPortal;" />
</connectionStrings>
<system.web>
<membership userIsOnlineTimeWindow="60">
<providers>
<add description="Web Portal" connectionStringName="SqlServices"
enablePasswordRetrieval="false" enablePasswordReset="true"
requiresQuestionAndAnswer="true"
passwordFormat="Hashed" requiresUniqueEmail="false"
applicationName="/"
name="AspNetSqlProvider"
type="System.Web.Security.SqlMembershipProvider" />
</providers>
</membership>

From Visual Studio 2005's Website menu, I selected ASP.NET Configuration,
then clicked the Provider Configuration link. I then selected "Select a
single provider for all site management data," then clicked the "Test" link
besider AspNetSqlProvider. This produced the following error:

Server Error in '/asp.netwebadminfiles' Application
--------------------------------------------------------------------------------
Failed to generate a user instance of SQL Server due to a failure in
starting the process for the user instance. The connection will be closed.

Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

SQLExpress database file auto-creation error:
....

The error message mentions the need for various forms of access to the
App_Data directory, so I granted Full Control to USERS, NETWORK SERVICE, and
ASPNET. I also made ASPNET a local administrator. I also verified that SQL
Server Express is installed and running.

Therefore, I have reached the conclusion that I need to grant the local
ASPNET account, which theoretically is being used by IIS, dbcreator
priviledge in SQL Server Express. However, this stripped-down product
contains no Enterprise Manager or Query Analyzer, so I do not know how to do
that. How can I grant that priviledge?

[Steven Cheng[MSFT]]

Hi,

Thanks for posting here.
From your description, you've a XP sp2 box with Visual Studio 2005 Team
Suite Beta 2(alsoSQL Server
2005 Express Edition April CTP) installed. When you try to use the
SqlServer Provider for the ASP.NET web application ,here occurs some error
indicate that the sqlserver instance wasn't correctly established, yes?

As for the problem you described, one thing made me a bit confused was
you're configuring the Membership service to use the remote sqlserver
database on a 2003 server, but the error info said something incorrect with
the App_Data folder. Based on my local BETA2 testing, when I don't
explicitly configure a remote datasource for membership and configure to
use the SqlServer Provider, it'll automatically create the required db in
my local SQL Server 2005 Express Edition April CTP and the ASPNETDB.MDF
file will occur in the App_Data sub dir. What'll happen if you remove your
remote db configuration and try using the local SQL Server 2005 Express
Edition to perform as the membership provider? Will it also
throw such error?

In addition, you can also post the this issue to the new MSDN forum on the
web to see whether any other community members have encountered the
similiar problom.

http://forums.microsoft.com/msdn/

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[John Bunch]

Yes, you seem to understand the problem.

I tried to do what you suggested in web.config:
1. In connectionString, I changed the Server to VSDev01WSWP01 (the local XP
machine), then I removed the Server entry completely. Both times I received
the same error as before.
2. in the AspNetSqlProvider provider, I commented out
"connectionStringName=SqlServices". This made it even worse. The error I
received was "Access is denied for the application you are attempting to
administer."

So, that leaves me back where I started.

However, I want to empathize that in the original problem, the error message
stated, "The web server account used to connect to SQL Server Express must
have rights to create a new database." Since I am running IIS 5.1, the
message stated that the default web server account is the local ASPNET
machine account. I want to try granting this account the dbcreator
priviledge, but I do not know how. SQL Server Express does not seem to come
with Enterprise Manager or Query Analyzer, so I do know how to enter a
command to grant that priviledge.

Furthermore, when I try to connect to SQL Server Express from VSDev01SSQL02
using either Enterprise Manager or Query Analyzer from SQL Server 2000, I
receive the message, "SQL Server does not exist or access denied." I am
logged in as a Domain Admin and using Windows authentication.

[Steven Cheng[MSFT]]

Thanks for your response and the further detailed description.

After my last message, I sudently realize that you created your ASP.NET 2.0
website through HTTP in IIS while I always use FileSystem. When using
FileSystem, the VS.NET 2005 use test webserver to run the web app / admin
app, and by default it use NTLM authentication , so the executing account
is the current logon user. However, when using Http sever(IIS), by default
the web application integrated windows authentication and allow anomymous
in IIS virtual dir. Then, we navigate to the web admin page, the current
executing context is the IIS's anomymous account. Since anomymous account
is a very restricted account, it hasn't the sufficient permissions to
create the sqldatabase. As far as my local tests, I got this behavior. So
I think you can try the following things:

1. Create a new web application through HTTP in IIS. And test SQLserver
provider in web admin page to see whether the error also occur.

2. If occur, check the IIS virtual dir's "directory security" setting to
see whether allow anonymous access is checked, if so uncheck it (make sure
integrated windows ) is used. Then, restart web admin page and retry.

3. Creating a new web application through File System to see whether we can
correctly test the ASPSqlProvider

#When perfom the above tests, don't add any custom configuration in
web.config since there have default confifured providers for membership or
other services in machine level config.

Hope helps. Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[John Bunch]

Thank you for your suggestions, but I was still unable to create the database.

Here is what I did:
1. Using Visual Studio 2005 Beta 2, I created an ASP.NET Web Site through
HTTP. I then selected ASP.NET Configuration from the Website menu, and
tested the AspNetSqlProvider. I received the same error as before.

2. In IIS, I unchecked anonymous access in the virtual directory's
directory security and made sure that Integrated Windows authentication was
checked. I retested, but received the same error.

3. Using Visual Studio 2005 Beta 2, I created another ASP.NET Web Site
through the File System, but the AspNetSqlProvider failed as before.

[John Bunch]

Although I have still had no success creating the database, here is what I
have tried over the weekend:

I tried some new connection strings in web.config. Here is the updated
file, showing everything I tried:
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<add name="SqlServices"
connectionString="Provider=SQLOLEDB;Integrated Security=SSPI;Data
Source=VSDEV01SSQL02;Initial Catalog=WebPortal;" />
<add name="SqlServicesOLEDB"
connectionString="Provider=SQLOLEDB;UID=developer; PWD=secret;Data
Source=VSDEV01SSQL02;Initial Catalog=WebPortal;" />
<add name="SqlServicesODBC"
connectionString="DSN=WebPortal;Initial
Catalog=WebPortal;UID=developer;PWD=secret;" />
</connectionStrings>
<system.web>
<membership userIsOnlineTimeWindow="60">
<providers>
<add
name="AspNetSqlProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="SqlServices"
applicationName="/WebPortal"
requiresUniqueEmail="false"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
passwordFormat="Hashed"
description="Web Portal"
/>
</providers>
</membership>

Also, I have discovered a very quick way to generate a more specific error
message that seems to be related to this problem:
1. From Visual Studio's Website menu, select ASP.NET Configuration.
2. Select the Security tab.
3. The following message appears:
"There is a problem with your selected data store. This can be caused by an
invalid server name or credentials, or by insufficient permission. It can
also be caused by the role manager feature not being enabled. Click the
button below to be redirected to a page where you can choose a new data
store.

The following message may help in diagnosing the problem: 'Unable to connect
to SQL Server database.'"

I have verified that the server name and credentials are correct by
connecting to the database from another workstation with Enterprise Manager.
I am logged in as a domain administrator, and the "developer" login that I
tried using SQL Server Authentication is a member of every role in the
database, including the aspnet roles. I am not using the ASP.NET 2.0 roles,
so I did not enable the "role manager feature," and I do not even know how to
do that.

[Steven Cheng[MSFT]]

Thanks for the further followup.

Really very strange.
As for enable the RoleManager you can just put the
<roleManager enabled="true" />
in your web.config though I remember that there is a certain check option
in the web admin page.

In addition, I didn't put any explicit declaration of the providers in my
web.config, I used the default configured in the machine.config. So My
web.config is like below:

===============================
......
<system.web>
<authorization>
<allow roles="admins" />
<allow users="?" />
</authorization>
<roleManager enabled="true" />
<authentication mode="Forms" />
</system.web>
..............
===============================

Then, I'm able to create the ASPNETDB.MDF file under App_Data folder. Have
you also tried this?

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[John Bunch]

Although you did not directly solve my problem, today I was able to solve it
by studying the machine.config file that you mentioned (I did not even know
it existed before today).

Here is what I did. First, I commented out the "LocalSqlServer" connection
string in machine.config:
<connectionStrings>
<!-- <add name="LocalSqlServer" connectionString="data
source=.\SQLEXPRESS;Integrated
Security=SSPI;AttachDBFilename=|DataDirectory|aspn etdb.mdf;User
Instance=true" providerName="System.Data.SqlClient" /> -->
</connectionStrings>

Second, I replaced it in web.config and added the new provider:
<?xml version="1.0" encoding="utf-8"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<!-- The following line replaces the equivalent line in
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\CONF IG\machine.config,
which has been commented out. Technically, VSDev01SSQL02 is not a local
SQL Server, but it works, as opposed to SQL Express, which does not. -->
<add name="LocalSqlServer"
connectionString="Integrated Security=SSPI;Data
Source=VSDev01SSQL02;Initial Catalog=WebPortal;"
providerName="System.Data.SqlClient" />
<!-- This is an identical connection string, but given a new name
to emphasize that it will always be on a separate machine that
contains the application data. -->
<add name="SqlServices"
connectionString="Integrated Security=SSPI;Data
Source=VSDev01SSQL02;Initial Catalog=WebPortal;"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<membership
defaultProvider="SqlProvider"
userIsOnlineTimeWindow="60">
<providers>
<add connectionStringName="SqlServices" applicationName="/visual studio
2005"
requiresUniqueEmail="false" enablePasswordRetrieval="false"
enablePasswordReset="true" requiresQuestionAndAnswer="true"
passwordFormat="Hashed" description="Web Portal" name="SqlProvider"
type="System.Web.Security.SqlMembershipProvider" />
</providers>
</membership>

Please note that one of the primary issues was that I was previously using
SQLOLEDB as my provider in the connection string, and that is what caused the
connection to fail. When I switched to the format in machine.config, as
shown above, I was able to connect to the database.

Thank you for your help.

[Steven Cheng[MSFT]]

Thanks for your followup.

I'm also very glad that you've overcome the problem.
Enjoy Whidbey ! :-)

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[derekgreer]

I'm having the same problem getting SQLExpress to auto create m
database. I've been able to get the database created automatically o
several occations through the 2005 web admin site, but it seems to hav
just stopped working.

I get the following error:
Could not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Sit
Administration tool, use the aspnet_regsql command-line utility t
create and configure the database, and then return to this tool to se
the provider.

The Event Viewer has the following error:

Failed to generate a user instance of SQL Server due to a failure i
starting the process for the user instance. The connection will b
closed. [CLIENT: <local machine>]
I have default machine.config and web.config files, so I have change
nothing from the default installation of VS2005 Beta. In fact,
uninstalled everything and reinstalled everything and still have th
problem. Please help.

Derek Greer
FedEx Corporate Service

--
derekgree
-----------------------------------------------------------------------
derekgreer's Profile: http://www.highdots.com/forums/member.php?userid=25
View this thread: http://www.highdots.com/forums/showthread.php?t=76717

[jtl 12345678]

just configure the developer's rights of the computer,that's ok !
*** Sent via Developersdex http://www.developersdex.com ***