Active Directory to authenticate user access to Postgres DB through ODBC driver. Is this possible?

Question

I need for a user to logon to a Postgres DB through either MS Excel or Power BI, without entering any credentials, using solely their Active Directory login. I'm mainly a BI Developer and not usually part of the infrastructure side of things, but I've been tasked with finding out if this is at least feasible.

Am I right in thinking that this would be a pretty normal task within Microsoft infrastructure?

1. User signs on with AD to domain/their machine
2. AD grants access to something like SQL server (I take through some native MS protocol, not ODBC)
3. Excel(MS Query)/PowerBI from the users desktop rides that connection to allow the tool(s) seamless access to data as defined by their AD access rights.

The aim is to remove any additional credential entry by the user into either of the tools, because there'd be a high user turnover and user access to data sets must be stringently managed.

I imagine there would be back and forth between the AD admins and the DBA to facilitate user creation and access control at both ends, and I know postgres itself is AD/LDAP compliant, so some kind of access is possible. I'm just not sure if point 3, the seamless connection is possible.

Answer 1

Active Directory has 3 kinds of authentication: NTLM (NT LAN Manager), Kerberos (GSSAPI), and LDAP. Active Directory is essentially a fancy LDAP server with extra authentication mechansims

LDAP is centralized authentication, but not single sign-on. Your password is the same as when you login to your computer, but you need to login to the database again

NTLM is single sign-on, but only works on Windows. PostgreSQL supports it via SSPI

Kerberos is also single sign-on, but works with Windows and UNIX/Linux/Mac too. You login to your computer, get a Kerberos ticket, and Postgres uses this ticket to authenticate you. Postgres needs to be setup as Service Principal in Active Directory

I would say that Kerberos or SSPI is what you want.

Start with https://www.postgresql.org/docs/current/static/auth-methods.html

Answer 2

Yes i belive it is possible to achieve what you are asking. Postgres supports AD authentication.

Edit. You would need to check that the odbc driver you are using supports windows authentication also.

Excel and PowerBI can both handle windows authetication. But whether you can get the combination of all of the above together is another story.