1

I have a source SQL Server database installed in the Azure VM instance, and I wanted to sync the data to the Bigquery. I have chosen Apache Airflow to create a DAG to do this job. The source system can only be connected via the VPN network.

I connected my local system to the network and used Python Package pymssql to query the database. I was able to connect to it and execute queries. I have developed a DAG pipeline and tested it in my local environment. Now I wanted to deploy that DAG pipeline in the GCP composer environment.

Before creating a composer environment, I have successfully created a VPC network between GCP to Azure. And to confirm the connection is successful I created a VM instance and did telnet to the database IP to make sure we're able to ping the database server. From the VM instance, we were able to ping the database server. But when creating a composer environment in the same network and deploy the DAG code, it throws the below error

Traceback (most recent call last):
  File "/home/airflow/gcs/dags/source.py", line 201, in <module>
    conn = pymssql.connect(server='x.x.x.x', user=<username>, password=<password>, database=<dbname>)
  File "src/pymssql/_pymssql.pyx", line 652, in pymssql._pymssql.connect
pymssql._pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (x.x.x.x)\nNet-Lib error during Connection timed out (110)\nDB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (x.x.x.x)\nNet-Lib error during Connection timed out (110)\n')

The following is the libraries that is used

  • pymssql == 2.2.2

Facing this error for the last couple of days. I know there should be an issue in network configuration but couldn't able to find out that error. Any help is appreciated. Thanks!

Improve this question
2
  • Hi @Madhi. Can I help you with more information? If you think that my answer helped you, please consider accepting it by clicking the check mark (✔️) on the left side under the vote arrows. Should change the color to green. I'd really appreciate it. Thanks! Commented Dec 29, 2021 at 4:34
  • 1
    Nestor, we are going with the pyodbc approach, but still facing an issue in the composer environment. currently working on setting up a required drivers to the write path to resolve the error Commented Dec 29, 2021 at 8:43

1 Answer 1

Reset to default
1

I can suggest to you 3 ways to try to solve this issue, based on empirical cases:

1.- Most of the cases where people faced similar issues got solved using TSQL (installing freetds-bin). The following commands can help you:

sudo apt update
sudo apt install python3-pip, freetds-dev, freetds-bin, libssl-dev

Please ensure that you change "tds version = auto" to "tds version = 7.4" in /etc/freetds/freetds.conf, by:

sudo nano /etc/freetds/freetds.conf

modify to: [global] ... tds version = 7.4 port = 1433

To run Airflow CLI commands in Cloud Composer, follow these commands:

gcloud composer environments run ENVIRONMENT_NAME \
--location LOCATION \
SUBCOMMAND \
-- SUBCOMMAND_ARGUMENTS

Replacing the following:

-ENVIRONMENT_NAME with the name of the environment.

-LOCATION with the Compute Engine region where the environment is located.

-SUBCOMMAND with one of the supported Airflow CLI commands.

-SUBCOMMAND_ARGUMENTS with arguments for the Airflow CLI command.

2.- Ensure that the TCP/IP access for the Azure SQL Server is enabled. For that, open the access to your 127.0.0.1:1433 in the SQL server Configuration Manager in Azure, for example:

a. Start\All Programs\Microsoft SQL Server 20XX\Configuration Tools\SQL Server Configuration Manager.

b. SQL Server Network Configuration\Protocols for MSSQLSERVER.

c. TCP/IP\Properties\IP Addresses. Find 127.0.0.1 and change the "Enabled" to "Yes". Repeat it for all the IPs if you want or need.

3.- Finally, if the previous steps don’t work, try using a different library, pyodbc for example, by:

import pyodbc
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=SERVERNAME;DATABASE=testdb;UID=me;PWD=pass')
cursor = cnxn.cursor()
cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
    print row.user_id, row.user_name

Use the following URLs as guidance: Adaptive Server is unavailable or does not exist, pymssql.OperationalError: DB-Lib error message 20009, and Access Airflow command-line interface.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.