locked
Set up Database using SQL Server 2005 RRS feed

  • Question

  • User-280902410 posted
    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 <o:p></o:p>

    2.                  The asp.net sql server wizard will start click next <o:p></o:p>

    3.                  Choose configure SQL Server and click next <o:p></o:p>

    4.                  Select Server and database; Give database a name, default name is aspnetdb.mdf and choose type of authentication <o:p></o:p>

    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


    Friday, April 7, 2006 6:02 PM

All replies

  • User1654268034 posted
    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
    Sunday, April 9, 2006 2:39 PM
  • User419789063 posted
    did you open sql server config manager and set the protocols to accept tcp/ip or whatever else needed?
    Monday, April 10, 2006 5:11 PM
  • User-280902410 posted
    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.....
    Monday, April 10, 2006 6:54 PM
  • User-1749337000 posted

    thanks a lot

    Thursday, April 27, 2006 4:25 AM
  • User2038212817 posted

    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.

     

    Friday, May 26, 2006 7:26 PM
  • User-280902410 posted
    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

    Wednesday, May 31, 2006 8:00 PM
  • User2038212817 posted

    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.

     

    Wednesday, May 31, 2006 8:15 PM
  • User-1971668430 posted

    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!!!!!!!!!!!

    Friday, June 22, 2007 3:36 PM
  • User1477134808 posted
    thanks so much this really helped and it was a great explanation
    Tuesday, August 7, 2007 1:46 PM
  • User-1418567014 posted

    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)

     


    Thursday, January 31, 2008 10:00 AM
  • User1328605685 posted

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

    Tuesday, May 20, 2008 4:18 PM
  • Friday, July 25, 2008 6:13 AM
  • User-2073628509 posted

    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!

    Thursday, March 12, 2009 3:26 AM
  • User1527211669 posted

    thank you very much

    it is really useful

    Friday, July 23, 2010 2:45 AM
  • User163762185 posted

    thanks a lot it solved my problem

    Friday, November 26, 2010 8:22 PM
  • User2128776521 posted

    All -

    This is a follow-up to the thread above with (yet another) workaround.

    I had tried fully qualifing the server name as...

    MyMachineName\SqlExpress

    ...but I was still getting the dreaded error...

    Failed to query a list of database names from the SQL server. Invalid object name 'sysdatabases'.

    ...so then I found a hint that one can try to manually type in the database name (rather than use the dropdownlist) and that worked for me.

    HTH.

    Thank you.

    - Mark Kamoski

    Tuesday, May 31, 2011 4:30 PM
  • User-370644568 posted

    Hi chen,

    Thanks a lot .Even I had the same issue

    Tuesday, July 26, 2011 10:58 PM