Python in SQL Server 2019

  • peter-661823

    SSC Enthusiast

    Points: 178

    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

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 22759

    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:

    https://stackoverflow.com/questions/2778840/socket-error-errno-10013-an-attempt-was-made-to-access-a-socket-in-a-way-forb

    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.

  • peter-661823

    SSC Enthusiast

    Points: 178

    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

  • Mr. Brian Gale

    SSC-Insane

    Points: 22759

    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/

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply