Avatar of iwork
iwork
 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!
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
iwork

8/22/2022 - Mon
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
iwork

ASKER
Thanks Rainer for doing all this. I'll accept this as the solution.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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!