Solved

Connection String problems when using a mirror / ASP2 site

Posted on 2007-11-20
13
771 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 500 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
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…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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