May 22, 2020 at 4:35 pm
Hi
I am working a lot with SQL Server and Python and after updating the SQL Server from 2017 to 2019 I had 2 main Problems with my python functionality.
First (I solved it) was writing to filsystem (for example png or pdf from matplotlib.pyplot)
Second is stil unsolved. My SQL Server have to write some comands to a MQTT Server (in the same LAN on Port 1883). I use paho.mqtt.client.
This was working fine in SQL Server 2017 on a Windowsserver 2016.
After changing to Windows Server2019 and SQL 2019 the same Code stops to work with the Error "OSError: [WinError 10013] Der Zugriff auf einen Socket war aufgrund der Zugriffsrechte des Sockets unzulässig"
First of all yes all the 20 Firewall Rules "Block network access for AppContainer-XX in SQL Server instance MSSQLSERVER" are disabled in this testscenario.
A lot of other Port depending Python Programms are working also on 2019, but all of them on standard Ports like 80, 443.
So a simple Test (I know I dont need @sql but this is only an excerpt) like
CREATE PROCEDURE [dbo].[MQTT_TEST_1](@SQL AS NVARCHAR(MAX))
AS
BEGIN
SET NOCOUNT ON;
execute sp_execute_external_script @language = N'Python',@script = N'
import requests
r = requests.get("https://www.google.com")
r.status_code
print(r.status_code)
print("End")'
RETURN
END
works fine with
DECLARE @sql AS NVARCHAR(MAX)
SET @sql=''
EXEC MQTT_TEST_1 @sql
If I execute the Code with the Paho Client in the MSSQL Management Studio directly
EXEC sp_execute_external_script
@language = N'Python',
@script = N'
import pandas as pd
from pandas import DataFrame
print("Start")
import paho.mqtt.client as mqtt
import sys
#print(sys.version)
OutputDataSet = pd.DataFrame(InputDataSet)
for i, row in OutputDataSet.iterrows():
print("Before Client")
client = mqtt.Client("PAP_SQL")
print("Before Connect")
client.connect("172.20.20.25",1883,60)
print("Before Publish")
client.publish("Demolampe/cmnd/POWER","OFF")
print("Ready")
'
IT WORKS!
If I build a store procedure and call the procedure with EXEC it fails with "OSError: [WinError 10013] Der Zugriff auf einen Socket war aufgrund der Zugriffsrechte des Sockets unzulässig" (Sorry Errormsg in German)
I tried variants "with execute as .." in the stored procedure, but did not work.
Is there anybody able to help me?
Thanks a Lot
Best Regards from Austria
Peter
May 22, 2020 at 9:53 pm
I am not sure on that one but the error is saying (based on google translate):
OSError WinError 10013 Access to a socket was not allowed due to the access rights of the socket
doing a bit of google-fu I came across this article:
When you are putting that into a stored procedure, are you using a port lower than 1024 on the client.connect command? If so, it sounds like you will need to run it with elevated permissions to get it to work.
Based on the error message and what I can find on google, it sounds like it is likely UAC being the problem.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 23, 2020 at 4:55 pm
Hi again
First of all Thanks for answer. I think the right words for looking forward are the "well known ports <=1023" the seem to work in python if python is executed via the launchpad from SQL Server. I doublchecked the port 1883, the port is not in use on the sql Server (netstat -ano | find ":1883"). If I start python in the directory C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES with python.exe, and type (or insert)
import paho.mqtt.client as mqtt
import sys
print(sys.version)
client = mqtt.Client("PAP_SQL")
client.connect("172.20.20.25",1883,60)
client.publish("DemoLamp/cmnd/POWER","ON")
all works fine.
So I think its not a problem of the 2019 Windows Server. Also not a problem of the windows firewall or the defenderconfiguration.
I also tried to enable C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\Launchpad.exe to communicate in Windows Defender Firewall - allowed Apps. But did not help.
No other virus or simmilar protection software in use.
So still no Idea....
Best Regards from Austria
Peter
May 25, 2020 at 3:19 pm
So just to confirm, the stored procedure is the EXACT same query?
That is, you said the following query works:
EXEC sp_execute_external_script
@language = N'Python',
@script = N'
import pandas as pd
from pandas import DataFrame
print("Start")
import paho.mqtt.client as mqtt
import sys
#print(sys.version)
OutputDataSet = pd.DataFrame(InputDataSet)
for i, row in OutputDataSet.iterrows():
print("Before Client")
client = mqtt.Client("PAP_SQL")
print("Before Connect")
client.connect("172.20.20.25",1883,60)
print("Before Publish")
client.publish("Demolampe/cmnd/POWER","OFF")
print("Ready")
'
So your stored procedure is something like:
CREATE PROCEUDRE [dbo].[PythoTest]
AS
EXEC sp_execute_external_script
@language = N'Python',
@script = N'
import pandas as pd
from pandas import DataFrame
print("Start")
import paho.mqtt.client as mqtt
import sys
#print(sys.version)
OutputDataSet = pd.DataFrame(InputDataSet)
for i, row in OutputDataSet.iterrows():
print("Before Client")
client = mqtt.Client("PAP_SQL")
print("Before Connect")
client.connect("172.20.20.25",1883,60)
print("Before Publish")
client.publish("Demolampe/cmnd/POWER","OFF")
print("Ready")
'
and then execute that stored procedure, it fails, right?
I am just trying to figure out what could be causing the problem. One thing - when you connect from your machine to another on a specific port, you are likely not using the same port as the destination. It is possible in some cases, but not likely. So doing a netstat to get everything listening on port 1883 on the SQ SERVER will likely give you no results until you connect to the server.
And to confirm, 172.20.20.25 is a server that is NOT the SQL Server, right?
Another thought, you say that the command succeeds when you run it manually. Does it succeed if you run it twice? I am wondering if MAYBE the tool that is listening only allows 1 connection and you are not closing the connection. If the server is designed to only allow a single connection on the port, it may be refusing the connection when you run it a second time as you are not closing the connection after connecting. Quick google of mqtt tells me that you should call client.disconnect() when you are done. My guess is that the timeout (parameter 3 of connect) is seconds (maybe?), so the connection will likely be consumed until you disconnect. The error you are getting could very well be (and in my opinion likely is) that the query ran, took hold of the port and held it. Then you ran the stored procedure which has the destination socket locked and therefore the connection failed.
Also, using a TRY-CATCH block on the connection will allow you to grab the error rather than leave SQL to interpret it and pass it back. The link at the end of my post has examples of how to handle connection success and connection failed.
Link:
http://www.steves-internet-guide.com/client-connections-python-mqtt/
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply