Tuesday, July 14, 2020

Ethics in Modern Data at the Houston SQL Server and BI User Groups, July 2020

On July 14, Christine and I presented at the Houston SQL Server and BI User Groups' virtual meeting. Our joint presentation is one of our favorites to present, Ethics in Modern Data. 

Thanks to everyone who joined the virtual user group meeting and asked great questions at the end!

This is an important topic that lives at the crossroads of both of our careers, my wife's career in organizational psychology and human resources, my career in data, and our joint passion for history and civil rights. It's important to understand that when dealing with bias, outcomes matter, intentions don't. While many of our examples come from the historical context of the United States, not all, and we have added addition context to an international audience.

Video recording from the GoToWebinar: https://1drv.ms/v/s!ArJKDbZJcmzGm8dN9ljGMZGWSjXybQ?e=ZRemJW

Monday, July 13, 2020

On Dynamic Ports, Named Instances, and Firewalls

Recently I had a client and their vendor trying to configure a firewall for a new installation of SQL Server 2019 on Windows Server 2019.

Connections to the SQL Server were being blocked by the firewall. Looking at their firewall logs, they saw connections blocked on port TCP 49153. Why? Why not TCP 1433?

This was a pretty simple problem to solve, and not uncommon to face right after installing a SQL Server named instance.

Even though there was only one SQL Server instance on the server, the instance was installed as a named instance (servername/instancename), not the default instance (servername). A Windows Server can host many SQL instances, but only one can be the default instance, and there doesn't have to be a default instance.

Default instances use TCP 1433 by default, but this isn't "the default port for SQL Server" necessarily. Named instances can be configured to use TCP 1433, but by default, named instances are configured to use dynamic ports in a range between 49152–65535. Most importantly, a SQL Server using a dynamic port will change port every time the Database Engine service starts up. This is incompatible with port-based firewall rules unless you unwisely choose to specify a huge range of port exceptions for your firewall (not recommended).

(But why do local connections still work, like a local installation of SQL Server Management Studio or an application running on the same server as SQL Server? Because those connections probably aren't using using the TCP protocol but the Shared Memory protocol. Connections to SQL Server from a client running on the same server always try to use Shared Memory first, and are faster for it.)

For connections external to the SQL Server, the SQL Browser (UDP 1434) handles the connection and routes traffic to the correct TCP port for each instance, even if there is only one instance on the server. (The SQL Browser isn't required, if you'd rather always specify a port number in all connection strings, or set up SQL alias(es).)

The solution to the dynamic port problem is to change the SQL Server to use a static port. You can configure a named instance to use TCP 1433, though it may be misleading and confusing if another SQL Server default instance is ever installed on this server. Instead, choose any port in that same range, for example the one most recently dynamically assigned.

How do you configure a SQL Server instance to use a static port instead of a dynamic port? (This change won't take affect until you restart SQL Server service, so I recommend you only make this change during a maintenance window.)
  1. On the server, open Sql Server Configuration Manager, expand SQL Server Network Configuration, and go to Protocols for InstanceName, click on properties of the TCP/IP protocol.
    (As a book author, can I just say how impossible it is not to notice little inconsistencies like the capitalization of "Sql" vs "SQL" in the same screen...)
  2. By default, in the "Protocol" tab, you'll see that Listen All is enabled by default. SQL Server will listen on all network adapters, including the loopback adapter(s). That's not necessary. You can disable unused adapters, and on the "IP Addresses" tab, enable only the network adapters needed.

    If you want to keep using Listen All, then on the "IP Addresses" tab, only use the IPAll section to set the static port. If the Listen All is Yes, only the TCP Port and TCP Dynamic Port values under the IPAll section will be honored.
    If you want to disable unneeded network adapters, on the "IP Addresses" tab, you'll need to enable each adapter(s) desired, and configure the TCP Port setting for each. For many SQL Servers and most standalone instances, there is only one network adapter needed, though others like the loopback adapter will appear.
  3. Either under IPAll or each individual enable adapter, delete the 0 in the "TCP Dynamic Ports" field. When 0 is provided in this field, SQL Server listens on a dynamic port. You must leave it blank to use a static port.
  4. Either under IPAll or each individual enable adapter, provide the static port you want to use in the "TCP Port" field.
  5. Make the change to your port-based firewall to allow inbound traffic on the TCP port you've chosen.
  6. Restart the affected SQL Server instance.
For example, if you want to use only the primary internal IP address, and configure it to use port 52057 (a random selection in the allowed range), this is what your screens should look like, with Listen All disabled.

If you need then to configure Windows Firewall, it would look something like this in an Inbound rule. Here's an example with a rule to allow two different SQL instances, each configured for a different static port.

Wednesday, July 01, 2020

Presenting on Ethics in Modern Data at Houston HASSUG/SQL BI Group July 2020 Meeting

Looking forward to presenting with my wife Christine Assaf our talk on Ethics in Modern Data at the Houston Area SQL Group and BI Group's virtual meeting on Tuesday July 14, 11:30amCT.

This talk is a review of ethical considerations, many ripped from headlines, into analysis and collection for modern data, including the impact on society. This session will explore a variety of considerations that modern data scientists and data practitioners must account for when gathering and presenting data, including topics on bias, construct analysis, and machine learning.

See you then, free to join from anywhere!