Make SQL_DRIVER_NOPROMPT configurable.

[soundstripe]

I'm opening this issue primarily as a discussion. In desktop applications (specifically, in Windows apps), ODBC drivers often prompt the user directly for credentials. I noticed that pyodbc is specifying SQL_DRIVER_NOPROMPT for SqlDriverConnect and cannot be made to use those driver prompts.

I have already written my own password prompting code as a workaround but would it be a simple change to make this option configurable? Looks like the valid options are constants defined as SQL_DRIVER_PROMPT, SQL_DRIVER_COMPLETE, SQL_DRIVER_COMPLETE_REQUIRED, or SQL_DRIVER_NOPROMPT.

• Python: 3.7+
• pyodbc: 4.0.27
• OS: Windows 10 64-bit
• DB: Db2 for iSeries
• driver: iAccess ODBC driver

[gordthompson]

I just fooled around with this a bit. The options for SQLDriverConnect are here:

/* Options for SQLDriverConnect */
#define SQL_DRIVER_NOPROMPT             0
#define SQL_DRIVER_COMPLETE             1
#define SQL_DRIVER_PROMPT               2
#define SQL_DRIVER_COMPLETE_REQUIRED    3

If I hack

pyodbc/src/connection.cpp

Line 125 in 969ea2c

ret = SQLDriverConnect(hdbc, 0, (SQLCHAR*)wchar.psz, SQL_NTS, 0, 0, 0, SQL_DRIVER_NOPROMPT);

to specify either SQL_DRIVER_PROMPT or 2 then when I try to .connect using the connection string ...

    pyodbc.pooling = False
    conn_str = (
        r'DRIVER=ODBC Driver 17 for SQL Server;'
        r'SERVER=localhost,49242;'
        r'DATABASE=myDb;'
        r'Trusted_Connection=no;'
        r'UseFMTONLY=Yes;'
    )
    cnxn = pyodbc.connect(connection_string, autocommit=True)

... I get the error

Traceback (most recent call last):
  File "C:/Users/Gord/PycharmProjects/py3pyodbc_demo/main.py", line 29, in <module>
    cnxn = pyodbc.connect(connection_string, autocommit=True)
pyodbc.Error: ('HY024', '[HY024] [Microsoft][ODBC Driver Manager] Invalid argument value (0) (SQLDriverConnect)')

[v-makouz]

@gordthompson Did you supply a WindowHandle in SQLDriverConnect?

[gordthompson]

@v-makouz - No, I just changed the one parameter value to see what would happen. I was sort of hoping that the driver and/or DM would take care of the rest, but apparently there's more to it than that.

[v-makouz]

@gordthompson It needs a window handle to generate a dialog box. The following doc has more information, and there is a code sample there that uses the dialog prompt:
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqldriverconnect-function?view=sql-server-ver15

[soundstripe]

If the win32 extensions for python are installed, you could possibly use win32gui.GetDesktopWindow() for this.

[gordthompson]

@soundstripe - That could definitely be a possibility for a standard Python application on Windows. However, pyodbc is a "extension module" written in C/C++ and I have no idea whether it is possible for pyodbc code to detect the window handle of the Python application under which it is running.

[mkleehammer]

GetDesktopWindow returns the handle to the desktop, not a handle to anything in the process. It's been many years since I used it but it does work.

Good suggestion, but since you have a workaround for now, I'm going to mark this as a future item and clear out bugs first. If we get another request, I'll bump the priority.

[MohamedRhimii]

Hello. I'm having the same error.

  File "/app/superset_home/.local/lib/python3.9/site-packages/oci_sqlendpoint_dbapi/odbc.py", line 30, in connect
    return pyodbc.connect(connection_string, autocommit=True, **kwargs)
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [Simba][ThriftExtension] (46) The application had set a SQL_DRIVER_NOPROMPT flag to prevent the driver from opening any dialog or browser Windows. The SSO browser authentication flow requires the driver to open a browser Windows. If you would like to use the SSO browser authentication flow and know that it is safe for the driver to ignore the SQL_DRIVER_NOPROMPT flag, then please set to OCIIgnoreDriverNoPrompt configuration to 1. For details on how to set the OCIIgnoreDriverNoPrompt, please refer to the dr (46) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/dbapi)

Any possible straight-forward solution?

[v-chojas]

The error message tells you what to do.

[MohamedRhimii]

@v-chojas I have found the solution, which, interestingly, is not directly related to the error explained.
My setup involved testing pods within a Minikube VM, where the pods needed to connect to a remote server. This connection required a VPN for successful connectivity. The challenge was to enable the Minikube pods, residing in their own subnet, to communicate through the VPN network that my host machine was connected to. To achieve this, I utilized the ip route add command, effectively routing the pods' traffic through the VPN tunnel I'm connected to on my host machine, thereby establishing a successful connection to the remote server.

For anyone facing a similar issue, the following command resolved my problem:
sudo ip route add [REPLACE_WITH_POD_SUBNET] via [your_minikube_ip]