CODEWITHSUNDEEP
sql-serverlinuxamazon-ec2amazon-rdspyodbc
David
David

Reputation: 583

How to connect to RDS SQL server using pyodbc in Amazon Linux AMI?

I'm setting up a pyodbc connection between aws ec2 linux machine and rds sql server. The connection is working when I use my local windows machine and able to edit the database.

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

Followed this tutorial and installed the drivers but still facing issues

cat /etc/odbcinst.ini 

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1
UsageCount=1

cat ~./odbc.ini
cat: ~./odbc.ini: No such file or directory

The code snippet is as follows:

db = pyodbc.connect("Driver={Microsoft ODBC Driver 17 for SQL Server};"
                      "Server=<Server Name>;"
                      "Database=<DB Name>;"
                      "uid=<username>;pwd=<password>"
                      )

The error is as follows:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'Micro soft ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

Update: Sqlcmd to query the database from the terminal is working

Upvotes: 4

Views: 5255

Answers (2)

David
David

Reputation: 583

db = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};"
                      "Server=<Server Name>;"
                      "Database=<DB Name>;"
                      "uid=<username>;pwd=<password>"
                      )

Finally this worked for me. Just removed the word Microsoft from the driver name.

Upvotes: 1

GiovaniSalazar
GiovaniSalazar

Reputation: 2094

find your driver like this

cnxn = pyodbc.connect('DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1};SERVER='+server+';DATABASE='+database+';uid='+username+';pwd='+ password)

Upvotes: 0

Related Questions