Trouble connecting to Microsoft SQL Server with Windows Authentication

Question

I am building a flask project for internal use at my company. I start by using flask and postgre and used sqlalchemy and set up that connection using the model formats like below:

app = Flask(__name__)

app.config.from_object('config')

db = SQLAlchemy(app)

and that worked well. Now for security reasons we are switching to using Microsoft SQL Server with window authentication on an IIS server but i am having a lot of trouble connecting. I changed the database URI to

SQLALCHEMY_DATABASE_URI = "mssql+pyodbc://server/database"

I've checked the SQL Sever configuration and I am pretty sure that is correct. However I get the error

psycopg2.OperationalError: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

I have researched this issue and have used the pool_pre_ping solution but I get the same error (shown below)

SQLALCHEMY_ENGINE_OPTIONS = {"pool_pre_ping": True}

I have seen a lot of answers around this stuff that use the sqlalchemy.engine stuff but if possible I was hoping to use all of the db.models I have already written out but if that's not possible I can rewrite some stuff. Any help appreciated.

Dale K · Accepted Answer · 2020-09-09 01:08:19Z

1

Windows Authentication will become a problem when you deploy to production. I recommend creating a SQL user to connect to your database.

Try this connection string:

mssql+pyodbc://<UID>:<PWD>@<SERVER>/<DATABASE>?driver=ODBC+Driver+17+for+SQL+Server;Trusted_Connection=no

Also make sure you've installed pyodbc msft docs

pip install pyodbc

edited Sep 9, 2020 at 1:08

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Sep 8, 2020 at 23:24

PGHE

2,11217 silver badges26 bronze badges

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

6 Comments

mrman456 Over a year ago

Thank you for responding. i tried the above connection string and am now getting a " could not connect to server: Connection refused (0x0000274D/10061)" any thoughts? Also, could you explain what you mean about creating a sql user to connect to db.

PGHE Over a year ago

WIndows Auth users a user on your domain e.g. 'Domain\User', a SQL user is created on the SQL Server see.

PGHE Over a year ago

If you want to use Windows Auth, remove the UID and PWD from the connection string and change trusted_connection=no to Trusted_connection=yes.

mrman456 Over a year ago

I am unfortunately getting the same error when trying to use windows auth

PGHE Over a year ago

Ignore Flask & SQLAlchemy for now. Can you connect to you database with pyodbc?. Follow the example from Microsoft. Once you've figured out the correct connection string, use that in your app.

|

Collectives™ on Stack Overflow

Trouble connecting to Microsoft SQL Server with Windows Authentication

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related