Unable to connect to SQL Server using email address

[schajee]

Environment

• Python: 3.7.11
• pyodbc: 4.0.32
• OS: Windows 10 x64
• DB: MSSQL 2017
• driver: ODBC Driver 17 for SQL Server

Issue

cnxn = DRIVER={ODBC Driver 17 for SQL Server};SERVER=domain.net;DATABASE=database;UID=abc@example.org;PWD=password

I'm trying to login to a remote SQL Server using an email address as the username, i.e. UID=abc@example.org.

PyODBC attempts to connect to example.org as server, even though I've specified a separate Server configuration.

Cannot open server "example.org" requested by the login. The login failed. (40532)

I've tried every which way to escape, but nothing works. I can login just fine using SSMS.

Is it not possible to have an email address as a username and be able to connect?

[schajee]

Ps. The Server uses the Azure Active Directory - Password setting. Maybe that has something to do with it.

[v-chojas]

Ps. The Server uses the Azure Active Directory - Password setting. Maybe that has something to do with it.

Are you using Azure SQL DB or regular SQL Server (either in an Azure VM, or elsewhere) ? You say that it's "MSSQL 2017", but all of the signs point to the former. In case of AAD password authentication you also need to add Authentication=ActiveDirectoryPassword to the connection string. See here for more information:

https://docs.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory

[schajee]

Yes, that seems to have done the trick. Thanks.

[schajee]

Re-opening...

• I can connect to Azure SQL from within a containerized application deployed on my Windows machine, but
• I cannot connect to the same server from the same containerized application deployed on an Ubuntu server

Networking-wise, ports are open.

Need to confirm if I can connect to an Azure SQL using SqlAuthentication.

[v-chojas]

What's the error?

Check the firewall of the server in the Azure portal.

[answerquest]

Cross-sharing what has worked for me, from a containerized application deployed in Redhat OpenShift:

By using redhat's python image in the Dockerfile, the AzureAD login is working for me. I did not need to do any of this extra stuff; it connected to the DB in the same way non-AD connections happen; just with an extra "Authentication=ActiveDirectoryPassword" added in the connection string.

Dockerfile:

FROM registry.access.redhat.com/ubi8/python-38
USER root
RUN curl https://packages.microsoft.com/config/rhel/8/prod.repo -o /etc/yum.repos.d/mssql-release.repo
RUN yum remove unixODBC-utf16 unixODBC-utf16-devel 
ENV ACCEPT_EULA=Y
RUN yum install -y unixODBC unixODBC-devel 
RUN yum download -y msodbcsql17
RUN rpm -Uvh --nodeps msodbcsql17*rpm

# Python dependencies
RUN pip install --upgrade pip setuptools wheel
RUN pip install pyodbc SQLAlchemy pandas

EXPOSE 8000
COPY . /app/
RUN chmod +777 /app
WORKDIR /app

CMD python /app/launch.py

Now I'm not sure if at your end you'll be able to pull this red hat docker image or not - pls try and see.

In the program, credentials are loaded like so:

DRIVER: "{ODBC Driver 17 for SQL Server}"
SERVER: "tcp:<my-database>.database.windows.net,1433"
DATABASE: "officeride-reporting-qa"
UID: "<the org email account>",
PWD: "<the password>",
OTHER: "Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword"

Note the "Authentication=ActiveDirectoryPassword" part at the end.

Code in program, assuming above creds are loaded into a "params" dict:

import sqlalchemy as db
import pyodbc
import urllib.parse
import pandas as pd

def initiateDB():
    dbString =  urllib.parse.quote_plus(r'DRIVER={};SERVER={};DATABASE={};UID={};PWD={};OTHER={}'\
        .format(params['DRIVER'], params['SERVER'], params['DATABASE'], params['UID'], params['PWD'], params['OTHER']))
    conn_str = r'mssql+pyodbc:///?odbc_connect={}'.format(dbString).strip()
    engine = db.create_engine(conn_str,echo=False, pool_recycle=3600)
    return engine

engine = initiateDB()
df = pd.read_sql('table1', engine)
print(df) 

So, somehow in the redhat image it looks like pyodbc is taking care of AD auth under-the-hood, while on using regular python image or ubuntu image like FROM ubuntu:16.04 I was never able to get through. No idea why this difference is there.