?
Solved

C# Connection string to Named SQL Instance...

Posted on 2003-02-19
7
Medium Priority
?
2,851 Views
Last Modified: 2010-07-27
Aye aye,

I'm trying to open an SqlConnection to a named instance of sql server 2000. I've tried several variations of the connection string at the end of my post, all to no avail.

Can someone please post an example of opening such a connection to a named instance of sql server?

SqlConnection conn = new SqlConnection("Initial Catalog=trslive;Data Source=MyServer\\MyInstanceName;user id=stew;password=beer");

I need a speedy soloution so am awarding 200 pts.  Please note that I MUST open the connection in the manner shown above as I'm working with a core framework that I cannot change.....  and also I MUST connect to a NAMED instance....   grr....  

Thanks,
Stew.
0
Comment
Question by:stewfidgeon
[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
  • 3
  • 3
7 Comments
 
LVL 4

Expert Comment

by:bgungor
ID: 7982939
First, I have examined your connection string, and there appears to be nothing wrong with it.  I built a test connection string using a UDL file (removing the provided for SqlClient), and made the following:

Password=test;Persist Security Info=True;User ID=sa;Initial Catalog=ECPROD;Data Source=(local)

The "\\" between the server and instance is the correct syntax, but you might want to try putting @ in front of the string to make it literal (instead of allowing escape sequences in the string), and just use one "\".

Then your string would look like:

SqlConnection conn = new SqlConnection(@"Initial Catalog=trslive;Data Source=MyServer\MyInstanceName;User ID=stew;Password=beer");

If you can provide the actual error you receive, it would help.  If you are getting the all powerful "SQL Server does not exist or access denied", try the following:

1) Verify that your SQL Server instance is actually running, and that you can connect to it using Query Analyzer.

2) Make sure you are using mixed mode security. Open Enterprise Manager, right click on the SQL Server instance, and choose Properties.  Click the Security tab and verify that SQL Server and Windows authentication is selected.  If Windows Only is selected, you cannot use a username and password, but must use NT security.  You will get an error saying you are not from a trusted connection if you don't.

3) Verify your user has access to the database requested, and has database owner priviledges (for test, you can change settings later).

Hope this helps, please provide more information if these tips don't help.

Bg
0
 
LVL 2

Expert Comment

by:adwiv
ID: 7985117
Hi stew,
    I could connect to a named server using both the following connection strings on my machine.

SqlConnection conn = new SqlConnection("Initial Catalog=Northwind;Server=ADWIV\\MyInstance;user id=sa;password=");

and

SqlConnection conn = new SqlConnection("Initial Catalog=Northwind;Data Source=PRIYA\\MyInstance;user id=sa;password=");

I think the problem on your end must be one of those suggested by bgungor. What is the exception message?

Cheers,
Ajay
0
 
LVL 1

Author Comment

by:stewfidgeon
ID: 7992309
Hi Guys,

Yeah, the error is the legendary "SQL Server does not exist or access denied" message. Most helpful of M$....

Interrestingly, after much examination, my connection string works on all but one of my named instances (the first one I tried). It's called myserver\INTERNALSYSTEMS    .... I can't find reference to INTERNALSYSTEMS being a reserved db name, and I can connect to it through query analyser.

The db config is exactly the same as my other two named instances and my default instance.....

I've tripple checked security settings, logins, permissions, everything is identical to my other instances.

Any ideas? Corrupt installation?

Cheers,
Stew.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:bgungor
ID: 7993773
If you can connect to query analyzer using the same credentials that you are using in your connection string, then I am stumped.  I notice your instance name is approaching the 16 character limit, but since it isn't, I would try to remove it and reinstall, so see if that solves the problem.  It may also be worthwhile to install SP3, to see whether that makes a difference, but don't forget you need to install SP3 on each instance.

Out of curiosity, how many instances are you running?  There is a limitation on the number installed on any one computer, but I am sure you knew that.

Bg
0
 
LVL 1

Author Comment

by:stewfidgeon
ID: 8007494
Aye aye BG,

I'm running three instances in total. The default, and two named instances.

All three instances have the same patching level - though not sp3...

I wonder if my SqlConnection object is counting the server name in the 16 character name? That'd make sense as 'INTERNALSYSTEMS' is by far my longest instance name... all of the others are 16 chars or less, including the server name.

I just realised I may be missing a few .NET service packs on my install so this could well be the problem... It'd nicely explain how I can connect through Query Analyser...

In the absence of any other suggestions, and because I don't have time to set up a proper test for this (client commitments), I'll take you comment as the answer BG.

Cheers,
Stew.
0
 
LVL 4

Accepted Solution

by:
bgungor earned 800 total points
ID: 8013278
Thank you.

Let me know if you have other questions regarding this thread.


Bg
0
 
LVL 1

Author Comment

by:stewfidgeon
ID: 8015146
Cheers fella.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

771 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