Can't connect to SQL Server using instance name

Question

I installed SQL Server 2012 on Windows server 2012, I can connect to the server only if I write . in the server name field.

But I can't connect using the server name MSSQLSERVER.

I am trying to connect using SQL Server Management Studio from the same machine.

I checked that TCP/IP is enabled, IP2 is enabled, port 1433 is configured for all IPs, added an inbound rule for port 1433.

Also tried restarting both the SQL Server service and SQL Server browser service multiple times, but no luck.

Any ideas on what could be causing this problem?

Answer 1

Wow, this is an old post. Still I'm going try to answer this just in case someone else is wondering.

The Short Answer

MSSQLSERVER is typically not used as the name of a Server hosting the SQL Server Object (often referred to as SQL Server). Since you are using SSMS, you need simply define the name of the SQL Host--the actual NetBIOS name, or FQDN, or IP address of the host.

If no instance was created during installation

Server Name: ServerHostName

If an instance was created during installation

Server Name: ServerHostName\InstanceName

or IP address

Server Name: ###.###.###.###\InstanceName 

If a port was defined other than the default port of 1433

Server Name: ServerHostName,####\InstanceName

The Long Answer

First a question. Is the MSSQLSERVER the actual NetBIOS name of your server? When talking about a SQL Server, it can be a little confusing at times as the name SQL Server is used to describe the host as well as the SQL Server Object (which is what most people are referring to in this context).

When using a tool like SSMS to connect to the SQL Server, you are first connecting to the host server, and once connected, you will connect to the SQL Object itself. The "." is not an object, or is not a valid SQL server name, unless you named your SQL server ".".

Regarding the instance, this is usually defined during the installation of the SQL Server (the object). Post installation, if you forget or simply do not know the name of the instance, the fastest way to determine what the instance name of your SQL server object is, is by checking system Services.

services.msc

Take a look at the SQL Services that are running. If you created a SQL instance during the installation of the SQL server object, then the instance name will be in (): i.e. (SQLInstance). On the same token, if you create multiple instances on the same server, then you will see multiple service names like,

Default Instance

SQL Server

With Instances

SQL Server (SQLInstance1)
SQL Server (SQLInstance2)

That should answer an issue where you can connect to one instance, but not another if one instance is running but the other is not.

If you did not create a SQL instance during installation, then the default SQL instance will always be MSSQLSERVER, and you will not need to define this in the Server Name spot of the SSMS.

I hope this helps.

Answer 2

It's a typical problem. Just use cliconfg.exe to create alias with tcp/ip protocol. If unsure do the same alias for 32 and 64 bit.

The 64 bit version is found in: C:\Windows\System32

The 32 bit version is in: C:\Windows\SysWOW64

In General tab enable TCP/IP.

In Alias, create a new alias, insert the name you prefer (with named istance) and select TCP/IP as network libraries.

As server name put the ip and 1433 as port. Don't leave "dynamicaly determine port" selected.

Source for cliconfg locations: http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/08/sql-client-config-32-and-64-bit.aspx