How do I connect to the default instance of SQL Server if a "named instance" parameter is required?

Question

I'm installing some software and I need to tell it to use the default instance of SQL Server, however the Installshield GUI insists that I enter in information for a named instance.

The target SQL Server is version 2008R2

I'm hoping there is a clean hack that I can use such as "." or "default" but I haven't guessed it yet. I've also tried MSSQLSERVER, and that didn't work either.

Murphey

The question is old, but I think now I have an answer for you! (see here) – Illustrious

Answer 1

All SQL server instances are stored in the Windows Registry. You can query the registry by using the Windows tool C:\Windows\System32\Regedt32.exe and browse/search there, you can do it by using a language, like C#  (see Example 1 there) or even T-SQL (also known as Transact-SQL).

In this answer, I'll show you how to do it with T-SQL. Use this script, which I found here, to determine ServerName, InstanceName, HostName and PortNumber:

set nocount on
Declare @key Varchar(100), @PortNumber varchar(20)

if charindex('\',CONVERT(char(20), SERVERPROPERTY('servername')),0) <>0 begin
    set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'
end else begin
    set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP'
end

EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @key, 
     @value_name = 'Tcpport', @value = @PortNumber OUTPUT
SELECT  CONVERT(char(20), SERVERPROPERTY('servername')) ServerName,
        CONVERT(char(20), SERVERPROPERTY('InstanceName')) InstanceName,
        CONVERT(char(20), SERVERPROPERTY('MachineName')) as HostName, 
        convert(varchar(10), @PortNumber) PortNumber

If the InstanceName is null, it means there is no named instance configured, then you have two, non-exclusive options:

1. Specify the ServerName to access the default instance
   - or -
   
2. Use the Sql Server Configuration Manager -> SQL Native Client XX.Y Configuration -> Aliases to define an alias. For example:
   
   will set up alias Inst1 for the local SQL server. Specify (local)\Inst1,1433 to access it. Of course, instead of (local) you can also use the ServerName.
   
   IMPORTANT: After setting up the alias, you need to restart the SQL service of the related instance or reboot your PC, otherwise it will not be accessible immediately. If you're using a port other than the default port 1433, you might need to open your local firewall in order to get this working.

---

Note: If you don't have it in the start menu, the SQL Server Configuration Manager can be found in C:\Windows\System32. Look for SqlServerManagerXX.msc (where XX is the SQL version, e.g. XX=11 or XX=13).

Illustrious

Answer 2

Run this query:

SELECT @@SERVERNAME + '\' + @@SERVICENAME AS InstanceName

and use the result as your instance name.

Nellie

I tried this to connect to default SQL Server instance through SSMS but it doesn't work. If I simply try . or <machineName> it works. Query SELECT @@SERVICENAME AS InstanceName returns MSSQLSERVER. <machineName>\MSSQLSERVER doesn't work – Heron

For default instance this nomenclature doesn't work even from code. It has to be either . or <MachineName>. – Heron

The instance name created this way is not correct, if no instance name is specified and there is only a default instance on the server. – Illustrious

Answer 3

If you have a default instance configured just use the server hostname as instance

Tague

Answer 4

Try MSSQLSERVER. That may be the default instance name according to this MSDN page.

Reprisal

Answer 5

The setup wizard will take MSSQLSERVER which means "default" instance.

See step 12 in the MSDN page

Attitudinize