A few weeks ago, a customer contacted me with an interesting problem.
They recently upgraded the SQL Server from 2017 to 2019 and started to get Permission denied error while running a sp_execute_external_scripts.
Their server is running on AWS and they tried to run Python code, but this problem can occur in any configuration while trying to exec sp_execute_external_scripts in SQL Server 2019.
But back to our specific case, the customer also informed me with a few important notes:
The Windows firewall on the machine is disabled.
Redshift connection directly from ODBC had no issues.
Changing the Launchpad service account to a local admin didnโt solve the issue.
The first thing we checked is the use of SQL Server Native Client ODBC driver and we got the following error: "Named Pipes Provider: Could not open a connection to SQL Server"
Next, we tried โServer=tcp: XXXXโ in the connection string, and we got this error: "TCP Provider: An attempt was made to access a socket in a way forbidden by its access permissions"
The errors mentioned above points to one of the two, firewall or antivirus. Since it didnโt appear they were running any antiviruses, we checked the firewall topic once again. Windows suggested the firewall was OFF, but it also noted it is managed by the system administrator. Meaning the firewall is managed by the Group Policy!
So, here is a cool trick I just learn from this incident. You can run "netsh advfirewall show allprofiles" (from an Admin command prompt) to identify what type of profile a user has. We got a long list of profiles and 2 of them where on state ON.
Here are the profiles with ON state:
Domain Profile Settings:
----------------------------------------------------------------------
State ON
Firewall Policy BlockInbound,AllowOutbound
LocalFirewallRules N/A (GPO-store only)
LocalConSecRules N/A (GPO-store only)
InboundUserNotification Disable
RemoteManagement Disable
UnicastResponseToMulticast Enable
Logging:
LogAllowedConnections Disable
LogDroppedConnections Disable
Private Profile Settings:
----------------------------------------------------------------------
State ON
Firewall Policy BlockInbound,AllowOutbound
LocalFirewallRules N/A (GPO-store only)
LocalConSecRules N/A (GPO-store only)
Stateย - show whether this profiles is available and useable
Firewall Policyย - show Inbound and Outbound connection policy whether inbound or outbound connection can be made in this profile by default.
Local Firewall Rules -ย shows whether localย firewall rules exists or Group Policy inherited.
Inbound User Notification - show if an inbound connection is established whether a notification will be shown users desktop
Remote Managementย - specifies whetherย remote management ports and mechanisms can be used.
Loggingย - is partition about creating logs.
Log Allowed Connectionsย - shows the configuration whether allowed connections will be logged
Log Dropped Connections -ย showsย the configuration whether droppedย connections will be logged
File Nameย - specifies the path and name of the firewall log.
Max File Sizeย - shows the size of a log file which can be the maximum
Therefore, we requested the domain admins to disable the firewall entirely, just for troubleshooting purposes, so we could run the sp_execute_external_scripts again and see if the firewall rules indeed is been activated from the group policy and causing the issue. So we did it and... VOILA! Problem solved!
Well.. actually.. it wasn't. It didn't solve the issue and we still got the same f*&#ing error.ย ย That was very surprising.
So, what's next? What we are missing?
With Microsoft help (Hi Joshua Somers, you were a huge help!), we gathered network traces for both cases. The run with SQL 2017 which worked, and with 2019 which didn't.
The first goal was to verify a successful network request and receiving communication. We saw the working situation (SQL 2017) in the trace, and we didn't see the non-working (2019),so it was looked like something was blocked.
Joshua advised us to check 2 network isolation settings:
Subnet definitions are authoritative - If you enable this policy setting, it turns off Windows Network Isolation's automatic discovery of private network hosts in the domain corporate environment. Only network hosts within the address ranges configured via Group Policy will be classified as private.
Private network ranges for apps โ A comma-separated list of IP address ranges that are in your corporate network. If you enable this policy setting, it ensures that apps with the Work Networking capability have appropriate access to your corporate network. These addresses are only accessible to apps if the app has declared the Work Networking capability. Windows Network Isolation attempts to automatically discover private network hosts. By default, the addresses configured with this policy setting are merged with the hosts that are declared as private through automatic discovery. To ensure that these addresses are the only addresses ever classified as private, enable the "Subnet definitions are authoritative" policy setting.
You enable the above setting as follows:
Run "gpedit.msc"
Enable "Subnet definitions are authoritative"
Enable "Private network ranges for apps" and set it to the client's subnet.
And now VOILA! Problem solved! This time for real ๐
This post was originally published on Noa's blog