ASP.Net Web Site Administration Tool: Could not establish a connection to the database

Question

I really hope that you would be able to help me on this issue. I have read on a few forums and some stackoverflow topics regarding the connection issues and used their advice. Some say just add the server name in front of connection, that works for that specific person and also for my local connection but remote not so much or just enable "allow remote connection on sql server" also did this (was already enabled) but still no help. So this is my problem:

I have created a asp.net mvc 3 website on my local machine and had issues in the beginning of connecting to a remote sql server so I had created the basic database on my local machine as well. I ran the aspnet_regsql.exe on that database, all tables were created. I then went to my web.config and added the connection string with my server name :

<add name="DefaultConnection" connectionString="data source= [***]\SQLSERVER;integrated security = true ;Initial Catalog=[dbname];" providerName="System.Data.SqlClient" />

I have also added the following to the web.config as well:

<membership>
  <providers>
    <clear />
    <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
  </providers>
</membership>
<profile>
  <providers>
    <clear />
    <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="DefaultConnection" applicationName="/" />
  </providers>
</profile>
<roleManager enabled="true">
  <providers>
    <clear />
    <add connectionStringName="DefaultConnection" applicationName="/" name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" />
    <add applicationName="/" name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" />
  </providers>
</roleManager>

Now this all worked for me for developing locally and testing my application. Everything worked, creating users adding them to the db.

Now that I want to go live, I need to create a new db on the live database. I have done all the above steps the same. Only thing that has changed, is that my connection string is now on our remote sql sever ip. (I have allowed remote connections as well).

<add name="DefaultConnection" connectionString="data source=[ip address];integrated security = true ;Initial Catalog=aspnetdb;" providerName="System.Data.SqlClient" />

I still have all the above membership tags in the webconfig.

But still I get the following error when I go to the ASP.Net Web Site Administration Tool: 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. (I have run the aspnet_regsql)

All help will be appreciated thanks!

Answer 1

Remove "integrated security" from your connection string, it will prevent from trying to log in to the SQL server using IIS application pool account