Connection string for default instance like for named instance

Question

In my .NET application I am connecting to Microsoft SQL Server 2005 or 2008 database. User selects instance which the application shows it and then application should do something with this instance. I take instance names from the registry, HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL.

I do not know if user selects default instance or named instance (and there is no such information in the Instance Names registry values). However, in order to connect to an arbitrary instance, I should use either

Server=(local)

or

Server=MSSQLSERVER\instance_name

in my ADO.NET connection string. Can I use only one connection string template? I tried to use Server=MSSQLSERVER\MSSQL10.MSSQLSERVER for my default SQL Server 2008 instance, but connection failed.

adam0101 · Accepted Answer · 2017-12-13 21:06:11Z

13

If the instance name is MSSQLSERVER then you must use a template like Server=servername. For all other instance names use a template like Server=servername\instancename. You cannot specify the default instance name explicitly.

As a side note you should not peak the registry for that. First of all, is undocumented and subject to arbitrary and unannounced changes. Second, is incorrect since instances that exist in the registry might not be running. And third is again incorrect because even running instances might not be connectable to if the SQL Browser service doesn't advertise them.

The recommended and supported way to detect existing instances is to use the SQL Browser broadcasting functionality, and they can be leveraged from the client code by using SmoApplication.EnumAvailableSqlServers.

edited Dec 13, 2017 at 21:06

adam0101

31.5k22 gold badges102 silver badges193 bronze badges

answered Aug 10, 2010 at 14:51

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Over a year ago

Thank you for detailed answer. I have 2008 Server Express but my default instance is NOT MSSQLSERVER but MSSQL10.MSSQLSERVER. How do I?

Remus Rusanu Over a year ago

The instance name is MSSQLSERVER. What you listed there is not an instance name, is an instance key, and I'm not going to explain what that is because, again, you should use the supported way instead (SMO).

Jesse Seger Over a year ago

Thanks for answering this years ago. Is this documented somewhere?

Mitch Wheat · Accepted Answer · 2010-08-10 13:33:03Z

0

You should use Server=servername\instance_name or Server=address\instance_name

answered Aug 10, 2010 at 13:33

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Collectives™ on Stack Overflow

Connection string for default instance like for named instance

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related