C# Connection string to Named SQL Instance...

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.
LVL 1
stewfidgeonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bgungorCommented:
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
adwivCommented:
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
stewfidgeonAuthor Commented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

bgungorCommented:
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
stewfidgeonAuthor Commented:
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
bgungorCommented:
Thank you.

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


Bg
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stewfidgeonAuthor Commented:
Cheers fella.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.