Cannot connect to SQL Server Database

Question

I cannot connect to SQL Server Database.

I am trying to setup Membership with roles on my website using the MSDN Tutorial. It used to work only locally on my machine, however after following advice given on different forums, and on stackoverflow, the situation worsened, and now I can't even connect to the SQL Server Database.

When I run the aspnet.regsql.exe utility, I get the following error message:

"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: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to 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()"

Also, I am unable to use the ASP.NET Website Adimistration Tool. When I click on the Security tab I receive the following error:

"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."

So, I click on the 'Choose Data Store' button as instructed, which allows me to select a a Provider. The provider is named 'AspNetSqlProvider', it has a Test link beside it. I click on Test and receive the following error message:

"Could not establish a connection to the database. 
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider."

The only other option on this page is to click "Back".

Can somebody please help me sort this out? It's been over a week now and I've searched high and low for answers and have been given so many links to posts that have been of no help. I'd appreciate any help at all.

I have tried running the Configuration Surface Tool and enabling pipes, local/remote connections, though this has made no difference at all.

I have even gone so far as to completely uninstall Visual Web Developer and SQL Server 2005 and 2008 and delete all traces left behind of these applications, and then re-downloaded them all and re-installed them all again.

No luck so far.

Please help Thanks

Bael

Edit:

I have since tried disabling my firewall changing the port, with no luck.

SOLUTION:

Yes, this may be a little drastic. And I think that this solution should only be a last resort when all else fails.

I managed to fix whatever was wrong by uninstalling SQL Server 2005 and 2008. And then Registering for, downloading SQL Server Express Edition 2008 (with Management Tools) and installing them.

After installation was complete, I closed Visual Web Developer, opened it again, and clicked on the Security tab after opening ASP.NET Website Administration Tool and everything has just "worked" since...

Answer 1

Can you connect using sqlcmd locally? Can you connect using sqlcmd from a remote machine?

Are you using a SQL username/password, or trusted Windows authentication? If the latter, run CommandPrompt/Powershell as a different user and see if you can connect.

My guess is that you haven't added logins to SQL, and that you're running aspnet.regsql.exe without escalated privileges. If it runs successfully from a Command Prompt that was Run As Administrator, then this is a useful sign.

There are a number of potential issues, but the escalated privileges is something I see often these days. It's better to create a dedicated account, give that an appropriate amount of access to the SQL instance, and then run Command Prompt as that account (shift-right-click to get the old 'Run as' option).