Hi

I was following the steps for setting up SQL server 2005 for PWSK. When I came to this step:

1.                  Double click aspnet_regsql.exe in  E:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

2.                  The asp.net sql server wizard will start click next

3.                  Choose configure SQL Server and click next

4.                  Select Server and database; Give database a name, default name is aspnetdb.mdf and choose type of authentication

5.                  Confirm setting and click next

The database pull down box said: <default> when i click on the downward arrow i get the following error message:

Failed to query a list of database names from the SQL server.
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]

So I followed the following steps to change the setting from local connection to local and remote connection:

1.	Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2.	On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3.	On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply. Note Click OK when you receive the following message: Changes to Connection Settings will not take effect until you restart the Database Engine service.
4.	On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

 
And I selected both TCP/IP and name pipes, but when i go bak and changed the <default> to some name i get the following error:

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(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
   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 really donno where I did wrong or what steps i'm missing. And I really am running out of ideas. Please help!!!! Thanks a lot
-yue

I would fire up SQL Server Management Studio and make sure you can look at the istalled databases.

You can use aspnet_regsql.exe to make a script for the nessary tables for aspnet. Then make your new database and excute the script on your new database

did you open sql server config manager and set the protocols to accept tcp/ip or whatever else needed?

Problem solved...it turn out that in ASP.NET SQL server setup wizard when it asks for server name, I have to specifiy the instance name as well as the server name when MS refer to "Server Name" they really mean "Server Instance Name") eg if your server was named 'bigturnip' then you need to specify 'bigturnip\sqlexpress' (where sqlexpress is the instance name - this one just happens to be the default used by SQL Server 2005 SQLExpress). By default the server name was entered as merely "bigturnip" but after i added "\sqlexpress", then everything worked. If anyone else is having this remote connection problem and when they did select remote connection and still does not work this is the way to solve the problem.....

thanks a lot

When I enter machinename\servername instead of servername, I no longer get the Name Pies Provider, error 40.  However I do get the following error: Failed to query a list of database names from the SQL Server. Invalid object name 'sysdatabases'.

I do have an instance of SQL Server 2000 and a named instance of SQL Server 2005 on the same machine.

BettyB.

 

Hi Betty:

I think I read in one of the previous posts, which says that you can't have both Server 2000 and Server 2005 together. There can only be one installed.
hope that helps!
-yue

Fortunately, that is not true.  You can have only one default instance but multiple named instances of any combination of 2000 and 2005.  My 2000 is my default and my 2005 is named.  See the following link:

http://geekswithblogs.net/tmoore/archive/2005/11/13/59971.aspx

BettyB.

 

I have been trying to run this aspnet_regsql all day for 7 hours and this post just solved my problem.  I added the SQL server name after the server name and it worked like a charm.

THANK YOU SO MUCH!!!!!!!!!!!

thanks so much this really helped and it was a great explanation

Hi, I followed the wizard and input the server name as

YOUR-787F71A704\SQLEXPRESS

Then I chose a database named as "Players".

 After two Next, finally I got an error

Setup failed.

Exception:

An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 262 and the SqlException message is: CREATE PROCEDURE permission denied in database 'Players'.

----------------------------------------

Details of failure

----------------------------------------

SQL Server:

Database: [Players]

SQL file loaded:

InstallCommon.sql

Commands failed:

CREATE PROCEDURE [dbo].aspnet_Setup_RestorePermissions

@name sysname

AS

BEGIN

DECLARE @object sysname

DECLARE @protectType char(10)

DECLARE @action varchar(60)

DECLARE @grantee sysname

DECLARE @cmd nvarchar(500)

DECLARE c1 cursor FORWARD_ONLY FOR

SELECT Object, ProtectType, [Action], Grantee FROM #aspnet_Permissions where Object = @name

 

OPEN c1

 

FETCH c1 INTO @object, @protectType, @action, @grantee

WHILE (@@fetch_status = 0)

BEGIN

SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'

EXEC (@cmd)

FETCH c1 INTO @object, @protectType, @action, @grantee

END

 

CLOSE c1

DEALLOCATE c1

END

 

 

SQL Exception:

System.Data.SqlClient.SqlException: CREATE PROCEDURE permission denied in database 'Players'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, 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 sessionState, Boolean isInstall, SessionStateType sessionStatetype)

 

Did you ever figure out this problem? B/C I'm expierencing the same thing.

Try this one

 

http://blog.nsight.dk/PermaLink,guid,78e73b3d-ce29-45bb-bccf-d727f794ceb2.aspx

y71chen:

Problem solved...it turn out that in ASP.NET SQL server setup wizard when it asks for server name, I have to specifiy the instance name as well as the server name when MS refer to "Server Name" they really mean "Server Instance Name") eg if your server was named 'bigturnip' then you need to specify 'bigturnip\sqlexpress' (where sqlexpress is the instance name - this one just happens to be the default used by SQL Server 2005 SQLExpress). By default the server name was entered as merely "bigturnip" but after i added "\sqlexpress", then everything worked. If anyone else is having this remote connection problem and when they did select remote connection and still does not work this is the way to solve the problem.....

 

 

This helped me greatly, Thanks!