MSSQLConnection Active Directory Authentication

[JarriqTheTechie]

The feature will allow users to authenticate on a MSSQL server using their current windows user session. Currently there is no way to do this using the orm.

[josephmancuso]

This is not an ORM function. This would be an authentication function in the framework you are using

[JarriqTheTechie]

I think this is an important feature. It has to do with how one connects to the database. In some environments active directory is used to authenticate an application against the database instead of using a database username and password. This feature would be on the same level as any other database connection/configuration item.

Currently, this orm uses pyodbc for connecting to mssql. This can be enabled by simply adding Trusted_Connection=yes

pyodbc.connect('Driver={SQL Server};' 'Server=SERVER_NAME;' 'Trusted_Connection=yes;')

Notice, no username or password in the connection string. pyodbc takes care of these things behind the scene. This feature can be implemented in less than 20 lines of code and maybe touches 2 files in the orm ConnectionResolver.py & MSSQLConnection.py. If implemented correctly its a non-breaking feature.

[josephmancuso]

Oh ok I see. I thought this was related to some kind of application level authentication. Can you open a PR for this?

[josephmancuso]

This should be done by setting a connection setting like this:

'mssql': {
        'driver': 'mssql',
        'host': os.getenv('MSSQL_DATABASE_HOST'),
        'user': os.getenv('MSSQL_DATABASE_USER'),
        'password': os.getenv('MSSQL_DATABASE_PASSWORD'),
        'database': os.getenv('MSSQL_DATABASE_DATABASE'),
        'port': os.getenv('MSSQL_DATABASE_PORT'),
        'prefix': '',
        'log_queries': True,
        'options': {
            'trusted_connection': True
       }
    },

Then inside the connection we can do something like:

f"DRIVER={mssql_driver};SERVER={self.host},{self.port};DATABASE={self.database};UID={self.user};PWD={self.password}{'Trusted_Connection=yes' if self.options.get('trusted_connection') else ''}"

[JarriqTheTechie]

I’ll create the PR this weekend.

[josephmancuso]

@JarriqTheTechie https://orm.masoniteproject.com/installation#mssql

[JarriqTheTechie]

This is wonderful. Thanks for the update.