Skip to content

SQL Server – PolyBase Services when listening on all IP is disabled

Introduction

You will find a lot of blogs explaining how to install the PolyBase feature with a SQL Server database instance. You will also learn how to configure it and how to use it in these blogs. But I could not find any solution for one case I was faced to.

When you have several SQL Server instances on a server you will either allocate a fix port per instance or a dedicated IP per instance, or probably both.

Of course if you define a dedicated IP address to your SQL Server instance you will automatically disable the TCP/IP Listen All property and you will set a fix port to this specific IP address.

Doing so, the best practice is to remove the fix port in the IPAll Section of the IP Address configuration.

These are the best practices that every DBA is applying considering the security policies.

What about Polybase services

When installing the PolyBase feature, you have probably noticed the 2 new services running on your server
– SQL Server PolyBase Data Mouvement
– SQL Server PolyBase Engine

Now that your standard configuration is set, you have to restart your services.
Restarting your SQL Server Service will automatically restart the services that are depending on it
– SQL Server Agent
– SQL Server PolyBase Data Mouvement
– SQL Server PolyBase Engine

When the configuration set above will apply, you will have the surprise that the 2 PolyBase services won’t start anymore.

Annoying, especially if the security policy prevent me to enable listening on all IP Addresses.

Here is the trick

In fact the fix is quite simple, I found it after trying multiple setting possibilities.

It seems that the PolyBase services are only checking the port in the IPAll Section and not the one set on the IP address you enabled.
Therefore if you let the IP address Listen All property to “No” and setting the fix port in the IPAll section to the same port set on the dedicated IP address of your SQL Server instance, the PolyBase services will be able to start again

Conlusion

With this configuration you won’t break you policies and best practices using PolyBase feature.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Christophe Cosme

Christophe Cosme

With more than 30 years of experience in IT architecture, development, and implementation, Christophe Cosme specializes in Microsoft SQL Server administration and development, Business Intelligence with the Microsoft BI suite and cloud services, Power BI Azure service and Report Server, and Azure/AWS cloud architecture and engineering. His background also covers project management and business analysis in global organizations. Prior to dbi services, he was Solution Lead Business Intelligence at Hoffmann-La Roche in Basel and Senior Manager Head of Business Intelligence at Swiss International Air Lines. Christophe holds a Master's degree in Engineering, Automation and IT from ENSISA.View Author posts

25 posts since November 2018

Most read in Business Intelligence

The Business Intelligence posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *