0

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')
Oluwafemi Sule
  • 36,144
  • 1
  • 56
  • 81
Kar
  • 790
  • 13
  • 36
  • You can find guidance on resolving your error in [this question](https://stackoverflow.com/questions/22991584/freetds-unable-to-connect-adaptive-server-is-unavailable-or-does-not-exist?rq=1) – Oluwafemi Sule Jun 16 '22 at 07:32
  • `DB-Lib error message 20009` doesn't seem to mesh with `Driver={ODBC Driver 18 for SQL Server}`... perhaps the encoding is messing with it? Also are there `UID` and `PWD` details in the real string for SQL Login authentication? – AlwaysLearning Jun 16 '22 at 09:41
  • @AlwaysLearning Yes – Kar Jun 16 '22 at 12:44

1 Answers1

1

Resolved by adding :1433 port at the end of the string

Kar
  • 790
  • 13
  • 36