Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2860
  • Last Modified:

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.
0
stewfidgeon
Asked:
stewfidgeon
  • 3
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
stewfidgeonAuthor Commented:
Cheers fella.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now