connect python code to SQL server Availability readonly DB failing to connect

Question

Hi We need to connect SQL server availability group via python code as bellow . how error not happy as facing error

import pyodbc 
print('Getting Database connection: MS SQL - AG secondary')
conn = pyodbc.connect('Driver={SQL Server};'
                              'Server=seconderyservername;'
                              'Database=DBname;'
                              'Trusted_Connection=yes;' 
                              'readonly=true;')
cursor = conn.cursor()
cursor.execute("SELECT @@servername")
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The target database ('') is in an availability group and is currently access ible for connections when the application intent is set to read only

Step done:

changing server= listnername ie then connect to primary server success. looks like readonly-true doent apply correctly

changing server= Servername ie then i get following error.

checked readonly routing and its working redirect connection to secondary site:

sqlcmd    -S Listener  -d DB -K ReadOnly

any one can help please

Answer 1

The driver SQL Server is a dusty and mouldy relic from the previous millennium, and knows nothing about availability groups. You need at least SQL Server Native Client 11.0. Best is to use ODBC 18 SQL Server Driver which is the most recent ODBC driver.

In difference to SQL Server, these drivers do not come with the OS, but you need to install them separately.

Also readonly=true should be Application Intent=ReadOnly.