# __init__.py
conn_str = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';Authentication=ActiveDirectoryMsi;'
def get__data(query):
odbc_conn = urllib.parse.quote_plus(conn_str)
engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % odbc_conn)
try:
engine.connect()
print("Connected to SQL Server...")
print("Running query...")
result = pd.read_sql_query(query, engine)
result = result.reset_index()
print("Query completed...")
return result
finally:
if engine != None:
engine.dispose()
``` Error message:
Result: Failure
Exception: DBAPIError: (pyodbc.Error) ('FA004', "[FA004] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Failed to authenticate the user '' in Active Directory (Authentication option is 'ActiveDirectoryMSI').\nError code 0xA190; state 41360\n (0) (SQLDriverConnect)")
(Background on this error at: http://sqlalche.me/e/dbapi)
Stack: File "/azure-functions-host/workers/python/3.7/LINUX/X64/azure_functions_worker/dispatcher.py", line 315, in _handle__invocation_request
self.__run_sync_func, invocation_id, fi.func, args)
File "/usr/local/lib/python3.7/concurrent/futures/thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
File "/azure-functions-host/workers/python/3.7/LINUX/X64/azure_functions_worker/dispatcher.py", line 434, in __run_sync_func
return func(**params)
File "/home/site/wwwroot/GetQIRDQReport/__init__.py", line 253, in main
html_body = render_qir_dq_report(as_of_date)
File "/home/site/wwwroot/GetQIRDQReport/__init__.py", line 65, in render_qir_dq_report
""".format(as_of_date=as_of_date))
File "/home/site/wwwroot/GetQIRDQReport/__init__.py", line 36, in get_gpms_edw_data
engine.connect()
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/engine/base.py", line 2218, in connect
return self._connection_cls(self, **kwargs)
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/engine/base.py", line 103, in __init__
else engine.raw_connection()
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/engine/base.py", line 2318, in raw_connection
self.pool.unique_connection, _connection
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/engine/base.py", line 2289, in _wrap_pool_connect
e, dialect, self
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/engine/base.py", line 1555, in _handle_dbapi_exception_noconnection
sqlalchemy_exception, with_traceback=exc_info[2], from_=e
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
return fn()
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
return _ConnectionFairy._checkout(self)
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
rec = pool._do_get()
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/pool/impl.py", line 139, in _do_get
self._dec_overflow()
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
exc_value, with_traceback=exc_tb,
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/pool/impl.py", line 136, in _do_get
return self._create_connection()
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
return _ConnectionRecord(self)
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
self.__connect(first_connect_check=True)
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/pool/base.py", line 657, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/util/langhelpers.py", line 69, in __exit__
exc_value, with_traceback=exc_tb,
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/pool/base.py", line 652, in __connect
connection = pool._invoke_creator(self)
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
return dialect.connect(*cargs, **cparams)
File "/home/site/wwwroot/.python_packages/lib/site-packages/sqlalchemy/engine/default.py", line 490, in connect
return self.dbapi.connect(*cargs, **cparams)
``` requirements.txt
azure-functions
numpy
pandas
sqlalchemy
datetime
matplotlib
seaborn
pyodbc
```txt
azure-functions==0.22
azure-functions-worker==3
@kummerer94 please refer to git repo link below to use msi to connect to Azure databases .hope this helps to solve the issue . here we go @TroyWitthoeft https://github.com/ssachde5/msi-azure-functions-linux
@ssachde5 Awesome!
@ssachde5 Amazing. Thank you!
Thanks. It does look like it should work on Linux. We would have to try this out and see if it works. I am not sure what the above timeout error means. We can use this issue to track this also wanted to let you know it might be a while before we investigate further. If you are able to verify that it works on App service plan that would help a lot.