sp_execute_external_script permission denied - What a b*tch!

STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Noa Brodezky

sp_execute_external_script permission denied - What a b*tch!

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:

  1. The Windows firewall on the machine is disabled.

  2. Redshift connection directly from ODBC had no issues.

  3. 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

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle