Can not connect to default instance (MSSQLSERVER) without giving port in the connection string

iwork
iwork used Ask the Experts™
on
I installed MSSQL server 2008 R2 with all the default options. When I try to connect as:

machinename/MSSQLSERVER: I can not connect.

but when I connect as:

machinename/MSSQLSERVER,1443: I can connect successfully.

Here are a few things that I tried:

1. I enabled ports 1433 and 1434 in windows firewall
2. Disabled windows firewall
3. All the SQL services are up and running such as SQL Browser, Server etc.
4. Named Pipes and TCP/IP is enabled under each section in Configuration manager.

Any ideas?...Please help me soon!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013
Awarded 2012

Commented:
Hi,
Have you tried to connect to
Machinename
only?
HTH
Rainer

Commented:
Looks like that default listening port has been changed to 1443, you should change it to default 1433. You can find instructions here
what is the exact error you are getting ,
if it is network related then try to access the db server witht he  IP name followed witht the Instance Name,

if you server error no 18456, it is related with the login credentials with the user level
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Hamed NasrRetired IT Professional

Commented:
try:
In Sql Server Configuration Manager
Sql native client.. Configuration - Client Protocols
TCP/IP: properties. Check and set Default Port.

Author

Commented:
Thank you all for getting back to me.

1. I did try with machine name only and that works BUT that will not solve my purpose, since I need to connect to the machine remotely as well. I did check that the remote connection is enabled.

2. Default listening port is definitely 1433, I looked at it again.

3. My exact error is: TITLE: Connect to Server
------------------------------

Cannot connect to machinename\mssqlserver.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (Microsoft SQL Server, Error: 87)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=87&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
4. I have set default ports to 1433 in configuration manager. Also, dynamic ports are not set to zero.
Most Valuable Expert 2013
Awarded 2012

Commented:
Hi,
so where are you not able to connect?

The connection with MACHINENAME works, or not?
You might also be able to use the full qualified domain name, e.g.
yourservername.yourdomain.tld (you can find this in the properties of your server)

As an alternative, you might be able to use the IP address as well or you can even create a custom name and create an entry in your local hosts file like
YourCustomName    ServerIP

HTH
Rainer

Author

Commented:
Hi Rainer,

The connection with the machine name works but machinename/instancename does not work.

Is there a way to get this working?
Most Valuable Expert 2013
Awarded 2012

Commented:
Why do you need the instance name?
MSSQLSERVER is the default instance after a "standard" installation and it is not necessary to use it in the connection.

HTH
Rainer

Author

Commented:
I need the instance name so that I can connect to the DB remotely.
Most Valuable Expert 2013
Awarded 2012

Commented:
You already should be able to connect to the default instance from remote using either machine name, FQDN or IP without port and without instance name.

Author

Commented:
That's the problem. I can not :(
Most Valuable Expert 2013
Awarded 2012
Commented:
Hi,
I tried to reproduce:
SQL Server 2008R2, default instance
my servername is "MSS"
Firewall ports are open (client, server), server is configured to allow remote connections
SQL Service listens on all IP addresses (server has multiple NICs) on port 1433
SQL Server management studio is configured with default network protocol

Testing:

1. Only machinename

mss - Success

2. machinename+port

mss,1433 - Success

3. machinename+instance

mss\MSSQLSERVER - Failed

4. machinename+instance

mss\. - Failed

5. machinename+instance+port

mss\MSSQLSERVER,1433 - Success
IMHO you can not address the default instance by instance name. When you use the port, the instance name is ignored.

KR
Rainer

Author

Commented:
Thanks Rainer for doing all this. I'll accept this as the solution.

Author

Commented:
I found a way eventually:

Create an alias under SQL native client.

Create alias name as : servername\MSSQLSERVER
Port:1433
Protocol: TCPIP
Server: servername


Restart the service and have fun!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial