Forms-Based Security problem

[chump2877]

I'm running IIS (v.5), Visual Studio 2005 Professional Edition, and SQL Server Express in a virtual operating system (that is running Windows XP Pro) using Microsoft Virtual PC.

In order to create a set of database tables needed to implement forms-based security, I'm running a utility named aspnet_regsql.exe in a command prompt.

When I run this utility, the ASP.NET SQL Server Setup Wizard starts up, as expected. I go through the wizard, and when the wizard tries to setup the DB tables, I get the following error (in the wizard):


Creation or change of the SQL Server database failed.

Setup failed.

Exception:
Unable to connect to SQL Server database.

----------------------------------------
Details of failure
----------------------------------------

System.Web.HttpException: Unable to connect to SQL Server database. ---> System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
--- End of inner exception stack trace ---
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
at System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install)
at System.Web.Management.SqlServices.Install(String database, SqlFeatures features, String connectionString)
at System.Web.Management.ConfirmPanel.Execute()


I Googled the error, and came up with a Microsoft-proposed fix (http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277)for this problem. But, I don't know that this fix is something that I necessarily want or need to do. I mean, why would SQL Server think that I am trying to connect to it via a remote connection? It seems to me that the connection is being made locally, not remotely?

Coincidentally, I am able to install the NorthWind database in SQL Server Express (using Microsoft SQL Server Management Studio Express) inside my virtual operating system. So apparently I can connect to SQL Server Express with Microsoft SQL Server Management Studio Express.

Any help is appreciated here. Thanks.

[nikkiH]

Is the command line part of the virtual environment?

[chump2877]

Is the command line part of the virtual environment?

Yup :)

Another thing:

In this article (http://msdn2.microsoft.com/en-us/library/x28wfk74.aspx), there is the following blurb:

"The Aspnet_regsql tool should not be used with a local installation of SQL Server Express running in user instance mode (that is, the connection string contains User Instance=true)."
A friend of mine seems to think the user instance mode is the culprit, and directed me to an article about user instances (http://msdn2.microsoft.com/en-us/library/bb264564.aspx#sqlexpuser_topic0).

I haven;t explored this idea yet (or read the article yet). I'm not sure how I would change the default connection string -- possibly in the machine.config or a web.config somewhere?....

Any ideas are welcome here.

[nikkiH]

Try what this guy did?
http://weblogs.asp.net/lhunt/archive/2005/09/26/425966.aspx

He specified the connection string and the database on the command line.

[chump2877]

I read the article at the link you posted.

From the article:

Creating a new DB in VisualStudio.NET 2005 is as simple as "Select APP_DATA node -> Add New Item -> Sql Database" and wah-lah you have a new aspnet.mdf file located in your APP_DATA folder.

Does this mean that you can create a separate aspnetdb database for each VS2005 web app project (i assume an .mdf file is a database file)? I was under the impression that there was only one such database created, for all VS2005 projects...and that that single database was created automatically with the aspnet_regsql.exe utility?

aspnet_regsql -A all -C "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\MyProject\APP_DATA\aspnetdb.mdf"

How much of this command do I need? Can I omit the database part and expect that the aspnetdb database will be created automatically? What does the "-A all" part of the command mean? And will there be a problem here because the User Instance parameter of the connection string is set to True?

I know so very little about command line scripting, so further clarification would help...

Thanks.

[chump2877]

Well I tried the following, and you can see the error that occurred:

http://www.mediamogulsweb.com/client/images/cmnd_prompt.jpg

Any ideas why this is happening?


Edit: Also, FYI, the ASP.NET SQL Server Setup Wizard never fired up when I executed this command.

[chump2877]

I created a SQL database named aspnetdb in a new VS2005 project, per the instructions in the article you posted.

Then I tried the following in a command prompt, and got another error (in the command prompt):


C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql -A all -C "Data Sour
ce=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\Documents an
d Settings\Admin\My Documents\Visual Studio 2005\Projects\Testing\Testing\App_Da
ta\aspnetdb.mdf"
Start adding the following features:
Membership
Profile
RoleManager
Personalization
SqlWebEventProvider
...........
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The
SQL error number is 5133 and the SqlException message is: Directory lookup for t
he file "C:\Documents and Settings\Admin\Local Settings\Application Data\Microso
ft\Microsoft SQL Server Data\SQLEXPRESS\C:\Documents and Settings\Admin\My Docum
ents\Visual Studio 2005\Projects\Testing\Testing\App_Data\aspnetdb.mdf.mdf" fail
ed with the operating system error 123(The filename, directory name, or volume l
abel syntax is incorrect.).
CREATE DATABASE failed. Some file names listed could not be created. Check relat
ed errors.
Creating the C:\Documents and Settings\Admin\My Documents\Visual Studio 2005\Pro
jects\Testing\Testing\App_Data\aspnetdb.mdf database...
SQL Server:
Database: [C:\Documents and Settings\Admin\My Documents\Visual Studio 2005\Proje
cts\Testing\Testing\App_Data\aspnetdb.mdf]
SQL file loaded:
InstallCommon.sql
Commands failed:
DECLARE @dbname nvarchar(128)
DECLARE @dboptions nvarchar(1024)
SET @dboptions = N'/**/'
SET @dbname = N'C:\Documents and Settings\Admin\My Documents\Visual Studio 2005\
Projects\Testing\Testing\App_Data\aspnetdb.mdf'
IF (NOT EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE name = @dbname))
BEGIN
PRINT 'Creating the ' + @dbname + ' database...'
DECLARE @cmd nvarchar(500)
SET @cmd = 'CREATE DATABASE [' + @dbname + '] ' + @dboptions
EXEC(@cmd)
END
.SQL Exception:
System.Data.SqlClient.SqlException: Directory lookup for the file "C:\Documents
and Settings\Admin\Local Settings\Application Data\Microsoft\Microsoft SQL Serve
r Data\SQLEXPRESS\C:\Documents and Settings\Admin\My Documents\Visual Studio 200
5\Projects\Testing\Testing\App_Data\aspnetdb.mdf.mdf" failed with the operating
system error 123(The filename, directory name, or volume label syntax is incorre
ct.).
CREATE DATABASE failed. Some file names listed could not be created. Check relat
ed errors.
Creating the C:\Documents and Settings\Admin\My Documents\Visual Studio 2005\Pro
jects\Testing\Testing\App_Data\aspnetdb.mdf database...
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds
ParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,
Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult res
ult, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(String file, String server,
String database, String dbFileName, SqlConnection connection, Boolean sessionSta
te, Boolean isInstall, SessionStateType sessionStatetype)
.
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>

[nikkiH]

I've never used this, so I'm just guessing here.
aspnet_regsql -A all -C "Data Sour
ce=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\Documents an
d Settings\Admin\My Documents\Visual Studio 2005\Projects\Testing\Testing\App_Da
ta\aspnetdb.mdf"

If you look at the path it tried to use, it concatenated the datasource and the argument to get the path.

Directory lookup for the file "C:\Documents
and Settings\Admin\Local Settings\Application Data\Microsoft\Microsoft SQL Serve
r Data\SQLEXPRESS\C:\Documents and Settings\Admin\My Documents\Visual Studio 200
5\Projects\Testing\Testing\App_Data\aspnetdb.mdf.mdf" failed

Try omitting the datasource, changing the path you pass as the argument, stuff like that, to get it to use the right path to your database.

[Freon22]

Been keep up with this thread. Now I don't need to create a full instance ASP.NET Membership database. But you never know what you may want to do down the road. So I tryed the samething as you, to create the database using aspnet_regsql.exe in a command prompt. I also got the same error, I notice that when the Wizard came up it showed my computer name. Also in the dropdown list at the bottom if I clicked on it I would get an error NOT the list of databases that I should be getting.

Anyway I made sure that SQLEXPRESS was setup for remote access and I made sure that the firewall was set to allow remote access.
http://www.datamasker.com/SSE2005_NetworkCfg.htm
I then tryed again and got the same error, I then went to this site to make sure I was doing this all correctly.
http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

I then tryed again and failed again. This is when I remembered that you need more then just your computer name. So I tryed again but this time when the Wizard came up, where it shows my computer name I added \SQLEXPRESS to it.

That did it now when I clicked the dropdown list at the bottom I got my list of databases. But I took it one step more I left it showing <default> and finished running the Wizard and I now have a new database called aspnetdb with all the memberships and rolls. Hope this helps!

[nikkiH]

I think he didn't want to set it up as remote access, though?

[chump2877]

Well theoretically I don;t see why I would need to set up remote access, since I'm connecting to the database locally (the command line script is being run on the same machine that is running SQL Server Express).

Unless my understanding of what "remote access" is is incorrect. I interpreted remote access to mean accessing SQL Server Express from another machine.

Given this interpretation, I assumed that the ASP.NET error message (in the ASP.NET SQL Server Setup Wizard) was guessing what the problem was, but it was probably wrong -- since my setup didn;t fit the mold of this diagnosis.

In any event, I also tried copying the database created here:


I created a SQL database named aspnetdb in a new VS2005 project, per the instructions in the article you posted.


...to the following directory, since that is where ASP.NET seems to be looking for the database file (although it seems like the utility I'm running should really be creating the database itself in that location, and then adding the necessary tables): C:\Documents
and Settings\Admin\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\

This resulted in the following error:


C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql -A all -C "Data Sour
ce=.\SQLEXPRESS;Integrated Security=True;User Instance=True"
Start adding the following features:
Membership
Profile
RoleManager
Personalization
SqlWebEventProvider
..........................
An error occurred during the execution of the SQL file 'InstallCommon.sql'. The
SQL error number is 1802 and the SqlException message is: CREATE DATABASE failed
. Some file names listed could not be created. Check related errors.
Cannot create file 'C:\Documents and Settings\Admin\Local Settings\Application D
ata\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\aspnetdb.mdf' because it alre
ady exists. Change the file path or the file name, and retry the operation.
Creating the aspnetdb database...
SQL Server:
Database: aspnetdb
SQL file loaded:
InstallCommon.sql
Commands failed:
DECLARE @dbname nvarchar(128)
DECLARE @dboptions nvarchar(1024)
SET @dboptions = N'/**/'
SET @dbname = N'aspnetdb'
IF (NOT EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE name = @dbname))
BEGIN
PRINT 'Creating the ' + @dbname + ' database...'
DECLARE @cmd nvarchar(500)
SET @cmd = 'CREATE DATABASE [' + @dbname + '] ' + @dboptions
EXEC(@cmd)
END
.SQL Exception:
System.Data.SqlClient.SqlException: CREATE DATABASE failed. Some file names list
ed could not be created. Check related errors.
Cannot create file 'C:\Documents and Settings\Admin\Local Settings\Application D
ata\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\aspnetdb.mdf' because it alre
ady exists. Change the file path or the file name, and retry the operation.
Creating the aspnetdb database...
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolea
n breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObj
ect stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cm
dHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, Tds
ParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,
Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult res
ult, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(String file, String server,
String database, String dbFileName, SqlConnection connection, Boolean sessionSta
te, Boolean isInstall, SessionStateType sessionStatetype)

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>


I've got one more idea for what to do here, and then I think I'm going to give up and take Freon's advice, and attempt to set up remote connections (though, logically, this still makes no sense to me)....

[Freon22]

I don't think he has to have remote access. I just stopped remote access on my system then I tryed it and it still works. He should try ComputerName\SQLEXPRESS in the Wizard first I think that was his problem. The wizard only bring up the computer name he still has to add \SQLEXPRESS to it. He can then try the dropdown database list at the bottom. If he get the list then the wizard is connected to the sqlexpress server.

[chump2877]

Problem solved....the utility worked using the following command line script:


C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql -A all -C "Data Sour
ce=.\SQLEXPRESS;Integrated Security=True"
Start adding the following features:
Membership
Profile
RoleManager
Personalization
SqlWebEventProvider
.......................................
Finished.
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>


Apparently they weren't kidding around here:


In this article (http://msdn2.microsoft.com/en-us/library/x28wfk74.aspx), there is the following blurb:


"The Aspnet_regsql tool should not be used with a local installation of SQL Server Express running in user instance mode (that is, the connection string contains User Instance=true)."



You don't need to enable remote connections.

Thanks for everyone's help. :)