?
Solved

SQL Server issue connecting to named instance

Posted on 2016-11-22
6
Medium Priority
?
98 Views
Last Modified: 2016-12-28
This is an odd one that I've never seen before. I'm hoping someone can shed some light.

I have a SQL Server database with a default instance and a named instance. For the sake of this conversation we'll call the named instance SQL2.

Using the local IP address I can connect to the named instance just fine. See screenshot below.
working - localHowever, using the public IP address the connection fails. See similar screenshot below. (Public IP address is made up)
not working - publicFurther complicating things is that if I use the explicit port number instead of the named instance it works again.
working - publicI've never seen a case of a named instance failing to connect like this. There must be some kind of configuration issue I'm overlooking. Can anyone suggest a solution?
0
Comment
Question by:Russ Suter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 41898409
did you tried adding a alias name for the instance? You'll need the SQL Server Configuration Manager. Go to Sql Native Client Configuration, Select Client Protocols, Right Click on TCP/IP and set your default port there.

https://msdn.microsoft.com/en-us/library/ms177440.aspx
<<Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)>>
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41898689
Use Configuration Manager and check what protocols have you enabled for this SQL Server instance.
0
 
LVL 9

Accepted Solution

by:
miron earned 2000 total points
ID: 41899395
Please read the following article: Configure the Windows Firewall to Allow SQL Server Access, URL: https://msdn.microsoft.com/en-us/library/cc646023.aspx

Relevant points:

1_
SQL Server default instance running over TCP TCP port 1433

2_
SQL Server Browser service UDP port 1434 The SQL Server Browser service listens for incoming connections to a named instance and provides the client the TCP port number that corresponds to that named instance. Normally the SQL Server Browser service is started whenever named instances of the Database Engine are used. The SQL Server Browser service does not have to be started if the client is configured to connect to the specific port of the named instance.  

3_
Entire subsection Configure a Windows Firewall for Database Engine Access, URL https://msdn.microsoft.com/en-us/library/ms175043.aspx

Document portion
A_
To open access to SQL Server when using dynamic ports
In the Program dialog box, select This program path. Click Browse, and navigate to the instance of SQL Server that you want to access through the firewall, and then click Open. By default, SQL Server is at C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe. Click Next. ( please note, application Sqlservr.exe location can differ in actual installation )

To summarize, ensure the following ports are opened for successfull connection to SQL Server:

UDP port 1434
TCP port 1433 ( Default instance )
TCP port for application sqlservr.exe ( Named instance dynamic port configuration )

-- cheers
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 20

Author Comment

by:Russ Suter
ID: 41899443
I'm looking into the SQL Server Browser service. The perimeter firewall may be blocking UDP traffic on port 1434. Unfortunately I don't have access to configure that firewall so I've submitted a request. I'll get back to you.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41899447
What for do you need the SQL Browser service running? This is only needed if you use Name Pipes protocol. Is that what you want?
0
 
LVL 20

Author Closing Comment

by:Russ Suter
ID: 41899849
Opening Port 1434 to UDP traffic did the trick.
0

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question