?
Solved

Connection String problems when using a mirror / ASP2 site

Posted on 2007-11-20
13
Medium Priority
?
777 Views
Last Modified: 2012-08-13
I installed a forum based on ASP2 and using SQL2005 as database ...

The original web.config is

<configuration>
      <system.web>
            <identity impersonate="true" userName="mirror\sql" password="Spyware43" />
  <pages validateRequest="false" />
      </system.web>

      <appSettings>
            <add key="DatabaseString" value="Driver={SQL Server};Server=sql1.mirror.com;Database=forum;Trusted_Connection=yes;" />
            <add key="DatabasePrefix" value="DMG" />
            <add key="LongDateFormat" value="MMMM d, yyyy" />
            <add key="ShortDateFormat" value="M/d/yyyy" />
            <add key="DateTimeFormat" value="M/d/yyyy h:mm:ss tt" />
      </appSettings>

</configuration>


So I tried now to configure it using the mirror so I changed the string to

<add key="DatabaseString" value="Driver={SQL Server};Server=sql1.mirror.com; Failover Partner=sql2.mirror.com;Database=forum;Trusted_Connection=yes;" />

I also tried adding the ports as well ...

But the error I get is

Server Error in '/' Application.
--------------------------------------------------------------------------------
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "forum" requested by the login. The login failed.
ERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "forum" requested by the login. The login failed.
ERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute

When connecting to the website locally.

Remotely I get  

Server Error in '/' Application.
--------------------------------------------------------------------------------
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".

Any help is highly appreciated
0
Comment
Question by:Cassavatech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20319028
Did you create the user in the database forum and map it to the login you use to conenct ot the SQL Server ?

Hope this helps ...
0
 
LVL 2

Author Comment

by:Cassavatech
ID: 20319049
Yes I did .. it is perfectly working when I just use one connection.

For example, I can failover the database, change the connection string to SQL2 (mirror) and the forum is working again. I fail it back over to SQL1 (principal), change the connection string accordingly and its all working fine again.
0
 
LVL 18

Accepted Solution

by:
Yveau earned 1500 total points
ID: 20319130
So the issue is that you cannot connect to the mirror database ?
That is correct, that is only there for High Availability purposes. You cannot connect to it as long as it is the mirror.

When you want to 'automatically' failover the app as well, try:
<add key ="DatabaseString" value="Server=Server1;Failover Partner=Server2;Initial Catalog=forum;Trusted_Connection=yes;"></add>

Hope this helps ...
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 2

Author Comment

by:Cassavatech
ID: 20319275
Hiya,

I tried that .. so the complete web.config would be

<configuration>
      <system.web>
            <identity impersonate="true" userName="mirror\sql" password="Spyware43" />
  <pages validateRequest="false" />
      </system.web>

      <appSettings>
            <add key ="DatabaseString" value="Server=sql1.mirror.com;Failover Partner=sql2.mirror.com;Initial Catalog=forum;Trusted_Connection=yes;" />
            <add key="DatabasePrefix" value="DMG" />
            <add key="LongDateFormat" value="MMMM d, yyyy" />
            <add key="ShortDateFormat" value="M/d/yyyy" />
            <add key="DateTimeFormat" value="M/d/yyyy h:mm:ss tt" />
      </appSettings>

</configuration>

As a result I get the error

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

So when I add the driver as in the original web.config

<add key ="DatabaseString" value="Driver={SQL Server};Server=sql1.mirror.com;Failover Partner=sql2.mirror.com;Initial Catalog=forum;Trusted_Connection=yes;" />

I get the error

ERROR [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'DMG_SETTINGS'.

And it hasn't even failed over yet ....
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20320232
the failover connection string only works for .net version 2 or higher ... do you have that ?
0
 
LVL 2

Author Comment

by:Cassavatech
ID: 20320338
Yes it does run .net 2.0.50727
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20326086
I noticed you use the SQL ODBC driver ... try using the native SQL Server connection. You probably need to install that first ... I do not know if the failover is supported by the ODBC driver ... come to think of it ... it probably is not ...

Just install the connectivity part of the SQL Server installation to the webserver.

Hope this helps ...
0
 
LVL 2

Author Comment

by:Cassavatech
ID: 20333815
How would I have to change the connection string ?
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20336080
The part
    Driver={SQL Server}
is not in my connection strings ... and it uses the native connection ... I think that could be it.
Not sure ...

Hope this helps ...
0
 
LVL 2

Author Comment

by:Cassavatech
ID: 20341697
As I mentioned above ..if I don't use Driver=(SQL Server) etc. I get the error

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20352625
it look slike you do not have the proper provider. Then the connection switches to ODBC, but there is no DSN on the system.

>>This <customErrors> tag should then have its "mode" attribute set to "Off".
Try doing that and tell us what the error message is ... it might set us on the right track ...

Hope this helps ...
0
 
LVL 2

Author Closing Comment

by:Cassavatech
ID: 31410098
This was pretty much the right solution - I had a problem somewhere else too
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20381485
Glad I could be of any help !
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question