7

I'm trying to deploy an asp.net application to a server using a SQL Server instance for the ApplicationServices membership database.

The problem is, I'm getting an error that says

The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory

I got this error when I initially tried to deploy the aspnetdb.mdf itself with the application.

I got this error when I then scrapped that plan and decided to do a web.config transform so that in Debug I use the Express database, but on Release the connection string goes to SQL Server.

I got this error again when I decided out of curiosity to remove all references to the express database from the code, so there could be no possible way anything would be looking for the Express database. No luck.

Does anyone have any ideas about this? I have deleted and re-installed the web site in IIS each time, noting that there is no App_Data being deployed and no mention of the .mdf file in web.config - to no avail. It still thinks there's a connection string telling it to look for a SQL Server Express database :/

Edit: Here's the connection string I'm using. Pretty standard, I think, but I could always be wrong.

Data source=HERP;Initial Catalog=DERP;Integrated Security=True
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Can you post the connection string? – Adam Robinson Dec 21 '11 at 22:02
  • 2
    Can you show us your web.config? You can xxxx out anything sensitive. – Joel Etherton Dec 21 '11 at 22:05
  • You should probably add any connection string from web.config (search for `data source=`) and the part of your code which generates the error – Andomar Dec 21 '11 at 22:23
  • 1
    Whats the rest of the error message? – benni_mac_b Dec 21 '11 at 22:23
  • @Andomar - that is the only connection string currently in web.config. I took the other one out to try to eliminate the error (it didn't work) –  Dec 22 '11 at 01:47
  • @benni "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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) " –  Dec 22 '11 at 01:50

2 Answers2

8

Could the error refer to the default connection string from the machine.config file (the LocalSqlServer one)? This could be happening, considering that the default membership provider uses this connection string:

The following default membership element is configured in the Machine.config file [...]:

<membership>
<providers>
<add name="AspNetSqlMembershipProvider" [...]
connectionStringName="LocalSqlServer" [...]

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thank you so much - that's apparently the connection string it was looking for. I was able to employ a hacky immediate fix by simply specifying the machine.config connection string as my desired SQL Server connection string (mentioned above), but do you know the standard/non-hacky way of fixing this so it uses my connection string and not the LocalSqlServer one? Thanks again, it was a huge help. –  Dec 22 '11 at 16:13
  • You can override anything in `machine.config` in your own `web.config`. See http://msdn.microsoft.com/en-us/library/aa479307.aspx#configaspnet_sql_topic2: `The easiest way to have your application automatically take advantage of your newly created SQL database is to just replace the connection string value of this LocalSqlServer setting in your app's local web.config.` – Remus Rusanu Dec 22 '11 at 17:57
  • This helped me figure out my issue as well.. worked fine on one machine, but not the other, though they had the same connection string. Turns out the web.config was missing the node. – lambinator May 16 '12 at 19:13
6

Do not forget to clear the connectionStrings first:

<connectionStrings>
   <clear />
   <add name="LocalSqlServer" connectionString="Data Source=(local);Initial Catalog=aspnetdb;Integrated Security=True" providerName="System.Data.SqlClient"/> 
</connectionStrings>
Gerard
  • 2,649
  • 1
  • 28
  • 46