Solved

.NET 3.5 Sql Connection with SQL Server 2008 Multiple Instances Problem

Posted on 2010-08-27
8
449 Views
Last Modified: 2013-11-08
So even though I've been working with SQL Server and .NET for years now, I'm running up against a fairly simple issue I just can't seem to figure out.

We have several .NET 3.5 applications that access a test SQL Server 2000 DB server....let's call it "orange", which resides in a different network. So our application Sql Connection strings' server names are "orange.network.edu" - note this doesn't use an instance name, per se.

And this works great and has worked great for years.

We are in the process of upgrading "orange" to SQL Server 2008 and have just finished migrating all of the DTS packages to SSIS. We migrated it alongside our existing test version of "orange" by creating a second instance of the test DB...which we'll call "orange\v2008". So the this second instance is "orange\v2008"...

The problem is that I can't find any information online as to the correct syntax for a FQDN for this second instance of the new DB - our applications won't connect to "orange.network.edu\v2008" or to "orange\v2008.network.edu". We need to use "orange.network.edu" because the name of "orange" by itself won't resolve properly.

The second instance is using port 1435, it is NOT being blocked by the firewall, the DB is allowing external connections. I have not yet tried an alias but that is my next step...but I'd like to get it to connect first without having to resort to the alias.

Any ideas? (If this doesn't make sense, let me know and I'll try better to explain in more detail.)

Thanks.
0
Comment
Question by:georgeb3
  • 4
  • 3
8 Comments
 
LVL 30

Expert Comment

by:MlandaT
ID: 33543733
Have you tried using the Alias? I think it's definitely going to be your best bet here...


Check: Sql Server Configuration Manager -> SQL Native Client 10.0 Configuration -> Aliases
0
 
LVL 4

Expert Comment

by:dschauhan82
ID: 33543748
SqlConnection con = new SqlConnection("Server=You server name or comp name;Database=Yourdatabasename;Trusted_Connectopn=True");
please add this trusted connection property in your connection string.
http://msdn.microsoft.com/en-us/library/ms156468.aspx.
it may help you..

 
0
 
LVL 1

Author Comment

by:georgeb3
ID: 33544003
MlandaT, I think I will try the Alias option...at a minimum it will help as a diagnosis tool. If I create an Alias and the connection still doesn't work then there is most likely a network or permission issue going on behind the scenes with the new database. However, if the Alias does work maybe their is some weird network resolution/address issue going on.

dschauhan82...I will give that a try and see if that works.

Thanks, George
0
 
LVL 30

Expert Comment

by:MlandaT
ID: 33544015
You can also just use the IP address of orange.network.edu like so:

xxx.xxx.xxx.xxx\v2008

to make:

data source=192.168.0.1\v2008;initial catalog=myDB;persist security info=False;user id=myUser;password=myPwd;
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:georgeb3
ID: 33544436
MlandaT...more info: I tried using the IP address and received a more detailed error message:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

The instance name is correct - I double-checked.
The DB does allow remote connections - also checked that!
0
 
LVL 30

Accepted Solution

by:
MlandaT earned 500 total points
ID: 33546459
That error will normally only come about due to:

1) Firewall - make sure the SQL Server host machine allows access to that firewall (take note of antivirus firewalls as well, and scope of the firewall rules i.e. Private/Public/Domain
2) SQL Server configuration settings for remote connections.

In your case.. you will want to specify the port in the connection string as well...

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

DBMSSOCN=TCP/IP. This is how to use TCP/IP instead of Named Pipes. At the end of the Data Source is the port to use. 1433 is the default port for SQL Server - http://www.connectionstrings.com/sql-server

Hope the little additional info helps :)
0
 
LVL 30

Expert Comment

by:MlandaT
ID: 33556228
PS: Also make sure that the SQL Server instance is configured to use the TCP/IP Protocol.

Sql Server Configuration Manager -> Sql Server Network Manager -> Protocols for V2008
0
 
LVL 1

Author Comment

by:georgeb3
ID: 33557328
MlandaT,

Yes it is configured for TCP/IP...however it is setup for port 1435 since it is the second instance. I think it is a network issue since I can't telnet to port 1435 so I now have the network guys involved. Thanks for all of your input/suggestions...very helpful!

Just to clarify...the port is separated by the IP with a comma and not a colon in the connection string, right?

George
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now