Solved

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

Posted on 2010-08-27
8
453 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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
 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

807 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