Requirement: Connect to MSSQL Server from Airflow
Airflow Version - 2.3.0
Gave URI as the connection in AWS SecretManager as below
mssql+pyodbc:///?odbc_connect=TrustServerCertificate%3Dyes%3BDriver%3D%7BODBC%20Driver%2018%20for%20SQL%20Server%7D%3BServer%3DXXXXXXXXXXXXX%3BApplicationIntent%3DReadOnly
Error:
Traceback (most recent call last):
File "src/pymssql/_pymssql.pyx", line 646, in pymssql._pymssql.connect
File "src/pymssql/_mssql.pyx", line 2126, in pymssql._mssql.connect
File "src/pymssql/_mssql.pyx", line 716, in pymssql._mssql.MSSQLConnection.__init__
File "src/pymssql/_mssql.pyx", line 1835, in pymssql._mssql.maybe_raise_MSSQLDatabaseException
File "src/pymssql/_mssql.pyx", line 1852, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (:None)\n')
I am using MsSqlHook
as below
def get_db_hook(self):
return MsSqlHook(mssql_conn_id=self.mssql_conn_id)
def execute(self, context):
ti = context['task_instance']
self.get_s3_key_and_stage_path(ti)
self.log.info(f'Executing:{self.sql}')
ti = context['task_instance']
self.query_all(self.sql, ti, db_name='SQL Server')