finding the correct connection string for a local SQL instance

I'm trying to build a connection string for a test environment that will connect to the local SQL Server instance on different machines. The purpose of this is so that a developer can checkout the code from TFS, build it, and run the testcases, connecting to his local DB. The problem is that different developer's machines may have different SQL Server setups. In particular, some may be running the full server, others may be running SQL Server Express.

I'm trying to right a utility routine that will take template connection string (e.g., Data Source=(local); Initial Catalog= myDB; Integrated Security=SSPI;) and modify the Data Source to work with the local server.

I've tried using SmoApplication.EnumAvailableServers() (returns an empty table, regardless of whether I user true or false parameters), and SqlDataSourceEnumerator.GetDataSources() (returns 2888 servers from the network, but none on the local machine), SQLCMD -L (returns nothing).

Any suggestions?

In the alternative, is there an easy way to tell whether a particular connection string will connect to a server (without waiting for it to timeout if it doesn't). If I could find the answer to that, I could try the likely suspects until I got one to work.

you might try to get the connection string as following:

1. Create a new blank file and name it test.udl.

2. Double click on it, and a "Data Link Properties" dialog should appear.

3. On "Providers" tab, select "Microsoft OLE DB Provider for SQL Server" or "SQL Native Client"

4. On "Connections" tab, try various settings and use the "Test Connection" button to test them. Click "Ok" when it works.

5. Open the test.udl file in Notepad and copy the line that starts with "Provider=" into your Web.config "ConnectionString" value, BUT delete the little part that says "Provider=SQLNCLI.1;"

If you want each developer to work with their own local SQL server, then the ADO connection string should have the Data Source set to localhost

... ; Data Source=localhost; ...

Additionally, to get a list of current servers, go to the command line and run

osql -L

You can look in the registry to find all local SQL Server instances. This key contains the list: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL.

Each named instance will have a value in this key. For named instances the name of the value is the same as the name of the instance. For the default instance the value will be named MSSQLSERVER.

This will do the trick:

Data Source=.\SQLEXPRESS