Improve company productivity with a Business Account.Sign Up

x
?
Solved

Connection String problems when using a mirror / ASP2 site

Posted on 2007-11-20
13
Medium Priority
?
784 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
  • 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

587 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