Solved

Connection String problems when using a mirror / ASP2 site

Posted on 2007-11-20
13
760 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 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now