Solved

Connection String problems when using a mirror / ASP2 site

Posted on 2007-11-20
13
766 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

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