Pyodbc.connect is not connecting with created login and user

Question

Been stuck on this for a few days. Seen quite a few stack overflow posts on this which hasn't resolved for me and read the microsoft and pyodbc docs also but seems like my issue on this may be niche and would like some help.

Goal: I want to connect to sql server via a python script using pyodbc. I've built a docker-compose.yml which for the sql server image essentially points at a Dockerfile with build: ., then the dockerfile runs a setup.sql script is run so I create a db, user and login automatically instead of creating it every time I spin up the container.

On my laptop (checked ODBC data source admin) I have ODBC Driver 17 for SQL Server, and that's why I've used in in below code:

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=kafkaUser;PWD=Kafka_Us3R!;')

(I've seen on other posts that using trusted_connection=yes; should resolve this, but sadly doesn't for me.) NOTE: I get this error whether I use sa, kafkaUser, or kafkaLogin with their designated passwords. I've just copied and pasted above line after last try before resulting to posting here.

Dockerfile:

FROM mcr.microsoft.com/mssql/server
WORKDIR /topics

# Env vars for sql server
ENV ACCEPT_EULA Y
ENV MSSQL_SA_PASSWORD <YourStrong!Passw0rd>
ENV MSSQL_PID Developer
ENV MSSQL_HOST localhost
ENV MSSQL_USER kafkaUser
ENV MSSQL_PASSWORD Kafka_Us3R!
ENV MSSQL_DATABASE testdb

EXPOSE 1433:1433

COPY topics/proposal-created-hl/setup.sql setup.sql
COPY topics/proposal-created-hl/setup_database.sh setup_database.sh
COPY topics/proposal-created-hl/entrypoint.sh entrypoint.sh

RUN /opt/mssql/bin/sqlservr & ./setup_database.sh

setup.sql

-- MSSQL file for local testing with docker container
IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'testdb' )
    CREATE DATABASE [testdb];
GO

USE [testdb]
GO

IF NOT EXISTS ( SELECT name FROM sys.server_principals WHERE name = 'kafkaLogin' )
    BEGIN
        CREATE LOGIN [kafkaLogin] WITH PASSWORD = 'Kafka_Us3R!', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
        ALTER SERVER ROLE [sysadmin] ADD MEMBER [kafkaLogin];
        ALTER LOGIN [kafkaLogin] WITH DEFAULT_DATABASE = [testdb];
        CREATE USER [kafkaUser] FOR LOGIN [kafkaLogin];
    END
GO

Error:

pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'kafkaUser'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'kafkaUser'. (18456)")

setup_database.sh:

# Resource: https://stackoverflow.com/questions/58309452/docker-initialize-database-tables-and-records-in-sql-server

#!/usr/bin/env bash
# Wait for database to startup 
sleep 20
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourStrong!Passw0rd>' -i setup.sql

I have checked this also while getting in the container (via docker exec) that the db, user and login have been created. So what detail am I missing here that has caused me to get this error? I tried the solutions of various posts of people coming across the same error but this hasn't resolved for me. Is there something else I need to look at?

Answer 1

I found the solution to this problem I was having and will leave the question open as I didn't see a similar solution to the same problem on other stack overflow posts or github solutions.

So turns out is was a docker port issue. So in my Dockerfile I had above, the EXPOSE line somehow did not actually expose the port I wanted. I had to add that into my docker-compose.yml file such like:

# This file is to be run for local testing

version: '3'

services:
  sql-server-db:
    build: .
    container_name: sqlserver
    ports:
      - "1433:1433"    # <----------------- like this

Then I logged into mssql locally by using localhost, 1433 as the server name inside my pyodbc.connection string like so:

    cnxn = pyodbc.connect(
        'DRIVER={ODBC Driver 17 for SQL Server}; \
        SERVER=localhost, 1433; \
        DATABASE=testdb; \
        UID=kafkaLogin; \
        PWD=Kafka_Us3R!; \
        ENCRYPT=no' 
    )

Then this worked for me. I hope this helps if anyone else comes across the same issue.