Version 2021
Windows Authentication
PostgreSQL supports single sign-on using SSPI (what other databases call Windows Integrated Authentication). PostgreSQL uses SSPI in negotiate mode, which uses Kerberos when possible and automatically falls back to NTLM in other cases. SSPI authentication only works when both the server and client are running Windows, or, on non-Windows platforms, when GSSAPI is available. When using Kerberos authentication, SSPI works the same way GSSAPI does
See SSPI Authentication on the PostgreSQL site for more details.
The following example demonstrates how to use Windows authentication after PostgreSQL installed on Windows server.
Prerequisites
-
Join your machine to a domain before PostgreSQL is installed.
-
Run the PostgreSQL installer to install PostgreSQL Server on a Windows machine.
How to Use Windows Authentication
The steps in the following example are for the Administrator user. For steps 1-3, see to KB17037: How to setup Windows Authentication for MicroStrategy Web for details.
-
Link MicroStrategy users to the Windows account.
-
Enable Windows authentication at the application server level.
-
Configure MicroStrategy Web for Windows authentication.
-
Configure the PostgreSQL database:
-
Modify
pg_ident.conf
to add a map for your SYSTEM-USERNAME to PG-USERNAME. -
Modify
pg_hba.conf
to enable SSPI to include the above map and restart PostgreSQL. -
You can add
include_realm = 0
in thepg_hba.conf
file to strip off the realm name from the authenticated user principal. With this setting, you do not need to set the mapping. However, this is discouraged and is primarily available for backwards compatibility, as it is not secure in multi-realm environments unlesskrb_realm
is also used. It is recommended to leaveinclude_realm
set to the default (1
) and to provide an explicit mapping inpg_ident.conf
to convert principal names to PostgreSQL usernames.
-
DSN Configuration
Set up one of the following protocols:
- Enter the general connection information.
-
Go to the Security tab.
-
From the Authentication Method drop-down, select 4-Kerberos Authentication and enter the Service Principal Name.
-
Enter the required database connection information and user name. You don’t need to enter the password for the user as SSPI authentication is used.
-
Click Test.
Test the Connection on MicroStrategy Web
-
In MicroStrategy Web, create a new data source for PostgreSQL. Select the DSN created in DSN Configuration.
-
Enter the user name. In this example, Administrator is used.
-
Enter any characters in the password field, as the accurate password is not required for this user.
-
Click Save.
- Select the new data source. You can see the namespaces and tables are successfully retrieved, even if an incorrect password is entered.
You can also use DSN-less connections. The following are the examples of DSN-less connection strings for Windows authentication:
DRIVER={MicroStrategy ODBC Driver for PostgreSQL Wire Protocol};HostName=; PortNumber=; Database=;AuthenticationMethod=4; ServicePrincipalName=;
The PostgreSQL JDBC driver currently doesn’t support Windows authentication.