How to setup SQL Server remote port – Why is it not listening?

I got a request asking if it is possible for a web application to connect to an SQL server remote port. My answer: “But of course! We just need to open port 1433 on the Windows Firewall and enable TCP/IP.” A request handled very quickly. Or so I thought.

But that was not the case. Not long after I said that, I realized it needs a bit more work. The above two steps will not succeed in opening port 1433. Command “netstat -a” confirms this because the server does not listen to port 1433.

So let’s start from the beginning and list what is necessary to open the port for remote access the proper way.

To begin with, it’s a Windows Server 2019 with MS SQL Express 2017 installed. To access the SQL Configuration Manager, the best way is to open Computer Management. There is a snap-in SQL Server Configuration Manager.

SQL server remote port
SQL Server Configuration Manager

Make sure TCP/IP is “enabled”. Double-click it, go to “IP Addresses tab. Now comes the important part.

SQL server remote port
TCP/IP properties

In the screenshot above you will see several sections. IP1, IP2, IP3 … IPall is the last section. Each section represents the assigned IP addresses to the server. In my case I have an IPv6 public, IPv6 local and IPv4 public address. The field “TCP Dynamic Ports” shows a 0 (zero).

What is a dynamic port?

This is an excerpt from the MS docs: “Once SQL Service restarts, it requests a free port number from the operating system and assigns that port to SQL Server.”
What this means is that each time the SQL service restarts, a new port number will be assigned. This is not really what we want.

So what to do?

  1. To start with decide which IP address you want to use in the respective section. I mean, do you want to open the port for IPv6, IPv4 or both?
  2. Remove the 0 (zero) in TCP Dynamic Ports
  3. Make sure Enabled is set to YES
  4. Scroll down to the last section “IPALL”, replace the 0 (zero) in TCP Dynamic Ports with the port number 1433 in the field “TCP Port”.
  5. Restart SQL service

Lastly, you need to open port 1433 in Windows Firewall. Run “netstat -a” again. The port should now be listed. You are done!

Finally, you could use another port and not just 1433 – no restrictions there. If the SQL server has a public IP as in my case, I would restrict the source IPs that are allowed to connect.

Leave a Reply

five × one =

Close Menu

Contact form:

Privacy Policy

Privacy Policy

Effective date: July 26, 2019

Edy Werder ("us", "we", or "our") operates the https://edywerder.ch website (hereinafter referred to as the "Service").

This page informs you of our policies regarding the collection, use, and disclosure of personal data when you use our Service and the choices you have associated with that data. The Privacy Policy for Edy Werder has been created with the help of TermsFeed.

We use your data to provide and improve the Service. By using the Service, you agree to the collection and use of information in accordance with this policy. Unless otherwise defined in this Privacy Policy, the terms used in this Privacy Policy have the same meanings as in our Terms and Conditions, accessible from https://edywerder.ch

Definitions

  • Service

    Service is the https://edywerder.ch website operated by Edy Werder

  • Personal Data

    Personal Data means data about a living individual who can be identified from those data (or from those and other information either in our possession or likely to come into our possession).

  • Usage Data

    Usage Data is data collected automatically either generated by the use of the Service or from the Service infrastructure itself (for example, the duration of a page visit).

  • Cookies

    Cookies are small files stored on your device (computer or mobile device).

Information Collection and Use

We collect several different types of information for various purposes to provide and improve our Service to you.

Types of Data Collected

Personal Data

While using our Service, we may ask you to provide us with certain personally identifiable information that can be used to contact or identify you ("Personal Data"). Personally identifiable information may include, but is not limited to:

  • Email address
  • First name and last name
  • Cookies and Usage Data

Usage Data

We may also collect information how the Service is accessed and used ("Usage Data"). This Usage Data may include information such as your computer's Internet Protocol address (e.g. IP address), browser type, browser version, the pages of our Service that you visit, the time and date of your visit, the time spent on those pages, unique device identifiers and other diagnostic data.

Tracking & Cookies Data

We use cookies and similar tracking technologies to track the activity on our Service and we hold certain information.

Cookies are files with a small amount of data which may include an anonymous unique identifier. Cookies are sent to your browser from a website and stored on your device. Other tracking technologies are also used such as beacons, tags and scripts to collect and track information and to improve and analyse our Service.

You can instruct your browser to refuse all cookies or to indicate when a cookie is being sent. However, if you do not accept cookies, you may not be able to use some portions of our Service.

Examples of Cookies we use:

  • Session Cookies. We use Session Cookies to operate our Service.
  • Preference Cookies. We use Preference Cookies to remember your preferences and various settings.
  • Security Cookies. We use Security Cookies for security purposes.

Use of Data

Edy Werder uses the collected data for various purposes:

  • To provide and maintain the Service
  • To notify you about changes to our Service
  • To allow you to participate in interactive features of our Service when you choose to do so
  • To provide customer care and support
  • To provide analysis or valuable information so that we can improve the Service
  • To monitor the usage of the Service
  • To detect, prevent and address technical issues

Transfer Of Data

Your information, including Personal Data, may be transferred to - and maintained on - computers located outside of your state, province, country or other governmental jurisdiction where the data protection laws may differ than those from your jurisdiction.

If you are located outside Switzerland and choose to provide information to us, please note that we transfer the data, including Personal Data, to Switzerland and process it there.

Your consent to this Privacy Policy followed by your submission of such information represents your agreement to that transfer.

Edy Werder will take all steps reasonably necessary to ensure that your data is treated securely and in accordance with this Privacy Policy and no transfer of your Personal Data will take place to an organization or a country unless there are adequate controls in place including the security of your data and other personal information.

Disclosure Of Data

Legal Requirements

Edy Werder may disclose your Personal Data in the good faith belief that such action is necessary to:

  • To comply with a legal obligation
  • To protect and defend the rights or property of Edy Werder
  • To prevent or investigate possible wrongdoing in connection with the Service
  • To protect the personal safety of users of the Service or the public
  • To protect against legal liability

As an European citizen, under GDPR, you have certain individual rights. You can learn more about these guides in the GDPR Guide.

Security of Data

The security of your data is important to us but remember that no method of transmission over the Internet or method of electronic storage is 100% secure. While we strive to use commercially acceptable means to protect your Personal Data, we cannot guarantee its absolute security.

Service Providers

We may employ third party companies and individuals to facilitate our Service ("Service Providers"), to provide the Service on our behalf, to perform Service-related services or to assist us in analyzing how our Service is used.

These third parties have access to your Personal Data only to perform these tasks on our behalf and are obligated not to disclose or use it for any other purpose.

Links to Other Sites

Our Service may contain links to other sites that are not operated by us. If you click a third party link, you will be directed to that third party's site. We strongly advise you to review the Privacy Policy of every site you visit.

We have no control over and assume no responsibility for the content, privacy policies or practices of any third party sites or services.

Children's Privacy

Our Service does not address anyone under the age of 18 ("Children").

We do not knowingly collect personally identifiable information from anyone under the age of 18. If you are a parent or guardian and you are aware that your Child has provided us with Personal Data, please contact us. If we become aware that we have collected Personal Data from children without verification of parental consent, we take steps to remove that information from our servers.

Changes to This Privacy Policy

We may update our Privacy Policy from time to time. We will notify you of any changes by posting the new Privacy Policy on this page.

We will let you know via email and/or a prominent notice on our Service, prior to the change becoming effective and update the "effective date" at the top of this Privacy Policy.

You are advised to review this Privacy Policy periodically for any changes. Changes to this Privacy Policy are effective when they are posted on this page.

Contact Us

If you have any questions about this Privacy Policy, please contact us:

  • By email: info@edywerder.ch