asked on

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

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!

Rainer Jeschor

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

slakic

Looks like that default listening port has been changed to 1443, you should change it to default 1433. You can find instructions here

Ramesh Babu Vavilla

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

Hamed Nasr

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

iwork

ASKER

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.

Rainer Jeschor

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

iwork

ASKER

Hi Rainer,

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

Is there a way to get this working?

Rainer Jeschor

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

iwork

ASKER

I need the instance name so that I can connect to the DB remotely.

Rainer Jeschor

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.

iwork

ASKER

That's the problem. I can not :(

ASKER CERTIFIED SOLUTION

Rainer Jeschor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

iwork

ASKER

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

iwork

ASKER

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!