use `sqlqlchemy_url` property in `get_uri` for `mssql` provider

[rawwar]

related: #38195

use sqlalchemy's URL object to create a URI in the get_uri method.

[github-actions[bot]]

This pull request has been automatically marked as stale because it has not had recent activity. It will be closed in 5 days if no further activity occurs. Thank you for your contributions.

[dabla]

I've remarked we had the same issue with SAP Hana and now also with MSSQL, so I started debugging and trying some stuff out. At the end I came up with this solution, and that's patching the following method, which makes it finally work:

 def get_sqlalchemy_engine(self, engine_kwargs=None):
        """
        Get an sqlalchemy_engine object.

        :param engine_kwargs: Kwargs used in :func:`~sqlalchemy.create_engine`.
        :return: the created engine.
        """
        if engine_kwargs is None:
            engine_kwargs = {}
        return create_engine(self.get_uri(), **engine_kwargs)

I've changed it to this:

 def get_sqlalchemy_engine(self, engine_kwargs=None):
        """
        Get an sqlalchemy_engine object.

        :param engine_kwargs: Kwargs used in :func:`~sqlalchemy.create_engine`.
        :return: the created engine.
        """
        if engine_kwargs is None:
            engine_kwargs = {}
        engine_kwargs["creator"] = self.get_conn  # mind here to pass the callable, not the result of get_conn as it will be used internally to create the connection!
        return create_engine(self.get_uri(), **engine_kwargs)

What do you guys think about this solution?

[emredjan]

This breaks MS SQL connections if you're using a non-default sqlalchemy scheme (like mssql+pyodbc). See: #42664

[rawwar]

I am not working on this PR anymore. Closing the PR.