Issues setting secrets: ValueError: Local Secret “MYSECRET” was not found

[RAISS_Zineb]

Hello,

I want to connect to a SQL-Server database that I have Locally on Windows, I’m using Uuntu WSL, I’ve installed Prefect…

I created a flow, and on my code I offset the connection with pyodbc.connect.
During a quick run, I have 3 errors each time:
-ValueError: Local Secret “MYSECRET” was not found
-Error during execution of task: Error(‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][ -1]Server=EQ -EQ6288793\SQLEXPRESS’: file not found (0) (SQLDriverConnect)”)
-OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)

I have already created the var env and I have added it in my code, the agent and in run, I have ODBC DRIVER installed…!
Can you help me at this point or share with me the procedure it takes to connect to SQL server with Prefect

N.B: apart from Prefect on local python, the connection to the database works well.

[anna_geller]

Thanks so much for posting on Discourse!

I assume you are using Prefect Cloud backend, correct? Or are you on Prefect Server? Can you share the output of prefect diagnostics?

If you are on Cloud, you could create the Secret directly from the UI and to use it, you would need to set:

export PREFECT__CLOUD__USE_LOCAL_SECRETS=false

And if you are on Server, check this Discourse topic:

How to set Secrets (e.g. GITHUB_ACCESS_TOKEN) on Server?

The Prefect Server uses local Secrets. Therefore, the local Secret e.g. the Secret for your storage access token must be set: either within the ~/.prefect/config.toml or as an environment variable. We generally recommend setting it via an environment variable, but here are several options how you can configure it. 1) config.toml You can add your Secret within the configuration file as follows: [context.secrets] GITHUB_ACCESS_TOKEN = "your_token" 2) Environment variable set on agent sta…

[RAISS_Zineb]

Thank you Anna.
I’m on Prefect Server, this is how I create my env var “MYSECRET” follows what is on the doc:
1: export PREFECT__CONTEXT__SECRETS__MYSECRET=“MYSECRET”
2: prefect agent docker start --env PREFECT__CONTEXT__SECRETS__MYSECRET=“MYSECRET”
3: I added this line in my script in Flow to call the env var
my_secret_value = Secret(“MYSECRET”).get()
and for agent: python3 -m prefect agent local start --env PREFECT__CONTEXT__SECRETS__POSTGRES_PASSWORD=passwordXXXX
I tested and it’s good I don’t have the error for Secret.
Now I have the third error which is: OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)

[anna_geller]

This is the same error we discussed via Community Slack. You need to install the SQL Server driver.

Can you run in iPython pyodbc.drivers() to see what drivers you have in your environment and to confirm that pyodbc can find those?

[anna_geller]

Copying Slack conversation for completeness.

View in #prefect-community on Slack

@RAISS_Zineb: Hello community,
I tried to connect Prefecta with my SQL server database, but I got this error. who has already had this error, or has an idea about this error?

@Kevin_Kho: Let’s see if someone in the community knows more about this

@Anna_Geller: it’s a driver issue - either it’s not installed in your execution environment, or you don’t point at the proper driver in your pyodbc connection. check those docs for more info

Install the Microsoft ODBC driver for SQL Server (Linux) - ODBC Driver for SQL Server

@RAISS_Zineb: I have already installed ODBC 17

@Anna_Geller: can you share your flow code?

@RAISS_Zineb: normally in the code I work with in DRIVE I use: SQL Server Native Client 11.0
I made some change, and now I have another error message: Error during execution of task: OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0 ) (SQLDriverConnect)’)

@Anna_Geller: You can try to automatically infer driver using this:

driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1]

@RAISS_Zineb: Not working! the same error Task ‘define_Connection’: Exception encountered during task execution!
Traceback (most recent call last):
File “/home/zineb/.local/lib/python3.8/site-packages/prefect/engine/task_runner.py”, line 880, in get_task_run_state
value = prefect.utilities.executors.run_task_with_timeout(
File “/home/zineb/.local/lib/python3.8/site-packages/prefect/utilities/executors.py”, line 468, in run_task_with_timeout
return task.run(**args, ***kwargs) # type: ignore
File “sql_connection”, line 10, in define_Connection
cnxn = pyodbc.connect(
pyodbc.OperationalError: (‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)

@Anna_Geller: can you share your updated flow? (in text not image)

@RAISS_Zineb:

from prefect import Flow, task
from prefect.tasks.sql_server import SqlServerExecute
from prefect.tasks.secrets import PrefectSecret
from prefect.client import Secret
import pyodbc


@ Task
def define_Connection(query,pwd):
    cnxn = pyodbc.connect(
     "Driver={ODBC Driver 17 for SQL Server};"
     "Server=EQ-EQ6288793\SQLEXPRESS;"
     "Database=OTI_Djoliba;"
     "UID=sa;"
     "Trusted_Connection=no;")
    cursor = cnxn.cursor()
    #insert dataframe
    cursor.execute(query)
    for row in cursor:
         print('row = %r' % (row,))
    cnxn.commit()

@  Task
def define_query():
    return """
    SELECT * FROM {Trafic_OTI_Djoliba};
    """

@ task
def my_task(credentials):
    """A task that requires credentials to access something. Passing the
    credentials in as an argument allows you to change how/where the
    credentials are loaded (though we recommend using `PrefectSecret` tasks to
    load them."""
    pass


with Flow("postgres_example") as flow:
    postgres_pwd = PrefectSecret("POSTGRES_PASSWORD")
    query = define_query()
    define_Connection(query=query,pwd=postgres_pwd)
    my_secret = PrefectSecret("MYSECRET")
    res = my_task(credentials=my_secret)


# Register the flow under the "tutorial" project
flow.register(project_name="Step1")

if __name__ == "__main__":
    flow.run()

@Anna_Geller: so you didn’t implement my suggestion then. I was suggesting:

@task
def define_connection(query,pwd):
    cnxn = pyodbc.connect(driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1], server="EQ-EQ6288793\SQLEXPRESS", database="OTI_Djoliba", uid="sa", pwd="xxx")
    cursor = cnxn.cursor()
    #insert dataframe
    cursor.execute(query)
    for row in cursor:
         print('row = %r' % (row,))
    cnxn.commit()

@RAISS_Zineb: I made the changes requested, but I have an error related to the Secret variable that I created with the value MYSECRET: “ValueError: Local Secret “MYSECRET” was not found.”
Normally it is already created and I added it to the code

@Kevin_Kho: Did you add MYSECRET as an env var to the agent?

@RAISS_Zineb: yes
@Kevin_Kho I checked and I found that this line is missing so I added “my_secret = PrefectSecret(“MYSECRET”)” . Thank you
@Anna_Geller I made the change at the Driver level and I got an error related to driver=[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][-1]: “Error during execution of task : Error(‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][-1]Server=EQ -EQ6288793\SQLEXPRESS’: file not found (0) (SQLDriverConnect)”)”

@Anna_Geller: you still didn’t implement the same syntax I suggested. I suggested using normal keyword arguments such as driver, uid etc, not the single string syntax. Can you look more closely at the syntax I sent you and compare it to your current code? Can you see the difference in how the pyodbc connection is defined in each of those?

@RAISS_Zineb: Yes I did the same as you, the only change in the Driver so I used what you suggested: driver=[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][-1]
here are my connection parameters: cnxn = pyodbc.connect(driver=[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][-1], server=“EQ-EQ6288793\SQLEXPRESS”, database= “OTI_Djoliba”, uid=“sa”, pwd=“xxx”)

@Anna_Geller: exactly, this syntax should work. perhaps you can run in iPython pyodbc.drivers() to see what drivers do you have in your environment and to confirm that pyodbc can find those?

@RAISS_Zineb: with pyodbc.drivers() command, return this
[‘ODBC Driver 17 for SQL Server’]
And now I see another error: Error during execution of task: OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)

[anna_geller]

@RAISS_Zineb can you also check previous conversations about SQL Server and see whether they help you configure that?

I want to create a global temp table in my SQL Server database. Can I use Prefect SqlServerExecute task for it?

TL;DR no, you can’t because this task closes the DB connection once it’s done. And as soon as you close this session, the temp table won’t be available in any other session. So it doesn’t matter whether you use a local or global temp table, for such use case you need to use pyodbc and create your own custom task. View in #prefect-community on Slack [Dominic_Pham] @Dominic_Pham: Hi all, when I create a connection to a db using prefect.task.sql_server and I create a global temp table, will the t…

How can I use SQLServerExecute task?

View in #prefect-community on Slack [RAISS_Zineb] @RAISS_Zineb: Hello everyone I am a beginner with Prefect, in order to create a Data Pipeline, I want to connect it with a SQL DB that I have locally so I used the documentation: SQL Server Tasks | Prefect Docs My questions are: -In order to configure the connection between SQL and Prefect I use the classes and I put it all in a .py file and I do execution?? -If Yes, at the .py code level, do a pyodbc import? If not, how can I do? -for th…

[RAISS_Zineb]

Yes, I checked with pyodbc.drivers() list command
pyodbc.drivers()
[‘ODBC Driver 17 for SQL Server’]

[RAISS_Zineb]

I did what we discuss on Slack, but it doesn’t give me results, for sharing it’s not the same problem as me, I can’t connect to my database!
On the logs I find that there is a problem with the define connection function on which there are the parameters to connect to the database :
Task ‘define_connection’: Finished task run for task with final state: ‘Failed’; pyodbc.OperationalError: (‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)

[anna_geller]

Can you try that without Prefect? Does it work this way? If so, can you share the code without Prefect that works for you but doesn’t work with Prefect?

[RAISS_Zineb]

Yes,
I tested the same code without Prefect, on Jupyter notebook, it works

def define_Connection(query,pwd):
cnxn =pyodbc.connect(driver=[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][-1], server=“EQ-EQ6288793\SQLEXPRESS”, database=“OTI_Djoliba”, uid=“sa”, pwd=“Zineb@21”)
cursor = cnxn.cursor()
#insert dataframe
cursor.execute(query)
for row in cursor:
print(‘row = %r’ % (row,))
cnxn.commit()
cursor.close()
query=’’‘SELECT * FROM Trafic_OTI_Djoliba’’’
define_Connection(query,“Zineb@21”)

[RAISS_Zineb]

[anna_geller]

Nice. You would need to do the same from your Prefect flow then.

Btw I hope this is not your real password - please don’t share any sensitive data here, it’s all publicly available

[RAISS_Zineb]

No it’s not the real user & password, Thanks Anna.
I created a new script, on which I created a new flow.
But I can’t find my flow on UI prefect server and when I execute it with the command: python3 file_name this error shows me: " my_secret_value = Secret(“MYSECRET”).get()
File “/home/zineb/.local/lib/python3.8/site-packages/prefect/client/secrets.py”, line 131, in get
raise ValueError(
ValueError: Secrets should only be retrieved during a Flow run, not while building a Flow."
I just copy the same code and I add the part of the SECRET and the flow

[RAISS_Zineb]

@anna_geller

You would need to do the same from your Prefect flow then.

Hi Anna,
I did the same thing from : Error during execution of task: OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)the flow, please find the code below, but still the same error message.

from prefect import Flow, task
from prefect.tasks.sql_server import SqlServerExecute
from prefect.tasks.secrets import PrefectSecret
from prefect.client import Secret
import pyodbc



@task
def define_Connection(query,pwd):
   cnxn =pyodbc.connect(driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1], server="aaaaaaaa\SQLEXPRESS", database="DB_name", uid="name_user", pwd="XXXXX")
    cursor = cnxn.cursor()
    #insert dataframe
    cursor.execute(query)
    for row in cursor:
         print('row = %r' % (row,))
    cnxn.commit()
    cursor.close()


with Flow("Connection") as flow:
    postgres_pwd = PrefectSecret("POSTGRES_PASSWORD")
    #query = define_query()
    query='''SELECT * FROM Table_name''
    define_Connection(query,"PPPPPPP")
    my_secret_value = Secret("MYSECRET").get()


flow.register(project_name="Step1")

if __name__ == "__main__":
    flow.run()

[anna_geller]

@RAISS_Zineb

ValueError: Secrets should only be retrieved during a Flow run, not while building a Flow."

You can’t register and run your flow at the same time try removing the line:

flow.register(project_name="Step1")

[RAISS_Zineb]

for the error:ValueError: Secrets should only be retrieved during a Flow run, not while building a Flow." the problem was in the line: my_secret_value = Secret(“MYSECRET”).get()
I deleted it and the flow was runner and create. but I still get the error: pyodbc.OperationalError: ('HYT00', '[HYT00][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
I see that my connection on Prefect cannot communicate with my database which is local.
Is there anyone who is already connected to a SQL DB with Prefect?

[anna_geller]

For sure, but the issue is with your DB connection definition, not with Prefect or SQL Server You need to do the same what you did in your local script in your Prefect flow - perhaps switch to environment variables instead of using Prefect Secrets?

[RAISS_Zineb]

@anna_geller

You need to do the same what you did in your local script in your Prefect flow - perhaps switch to environment variables instead of using Prefect Secrets?

Yoop, a problem that I have is not prefect or sql or my BD but earlier to communicate between the two, something in the config I miss I don’t know is what in Secret or what!
For the creation of the Secret I followed the doc: Secrets | Prefect Docs

[anna_geller]

You may switch to environment variables. This will perhaps be easier for you.

LMK if you have any more Prefect-specific questions.

[RAISS_Zineb]

@anna_geller

environment variables

Ok, For information, before runner flow I run the Prefect Agent which calls the env var with the command: python -m prefect agent local start --env PREFECT__CONTEXT__SECRETS__POSTGRES_PASSWORD=password_of_DB