On this page:

• Password storing problem
• Windows Authentication overview
• TCP port discovery and SQLBrowser service
• GSS and SSPI in Postgres
• Mapping between OS and DB logins
• Setting up Windows Authentication in WiltonDB
• User logins audit
• Windows Authentication connections from other clients

Storing DB password in config files may be problematic from the security point of view. Especially when a desktop "thick" client application is used. Common scenario is when user should be able to login to DB from their workstation, but must not be able to copy and share DB password. At the same time, desktop app process running under this user credentials must be able to read the password and represent it in memory in plain-text form. This creates a security problem that is hard to solve.

There are ways to work around this problem, for example:

1. do not store the password at all - prompt interactive user to provide the DB password; this approach has an overhead of creating/managing DB logins for each user
2. store password in config file in encrypted form; how to handle the encryption key depends on actual security requirements - it can vary from "the encryption key is embedded in app sources" to "store encryption key on a hardware USB token"; also password can be stored per-user with user-specific (prompted or derived) encryption key.

Usually such workarounds are either insecure or create additional friction with login process.

Single Sign-On allows to solve the "password storing" problem properly. In this case the user is assumed to be already logged into their OS account (using OS password or some other security means). And when desktop application (running under this user credentials) performs login to DB, some networking authentication protocol and OS facilities are used by DB server to verify user credentials (perform authentication) and determine user permissions (perform authorization).

In MSSQL Windows Integrated Authentication is the form of SIngle Sign-On when Kerberos or NTLM protocols and LSA memory access facilities are used by MSSQL instance to verify user credentials and log them in under DB account with appropriate permissions.

Excluding the cloud specifics (Entra ID etc), there are 2 main ways Windows Integrated Authentication can operate: standalone or driven by Active Directory.

When user's workstation is a standalone machine that is not controlled from Active Directory, then NTLM protocol is used. Client application requests a "security context" from OS ans sends its details to server. Server verifies the security context, generates a "challenge" message and sends it back to client. Based on this challenge, client generates a "response" message that includes OS user password (secret) hash and sends it back to server to complete authentication.

In Active Directory environment either NTLM or Kerberos protocol can be used. In general the login process is similar (Kerberos specifics lie outside of the scope of this article), just the information about user credentials/permissions comes from Active Directory.

When TCP protocol is used to connect to MSSQL, the client application needs to obtain the TCP port to open the connection to. MSSQL client libraries/applications allow to specify an "instance name" (like "SQLEXPRESS") instead of an actual TCP port, for example:

sqlcmd -S tcp:DESKTOP-JS80C9O\SQLEXPRESS ...

In this case client library queries the SQL Browser service using SQL Server Resolution Protocol (SSRP) - sending a message to UDP port 1434 and receiving the list of active MSSQL instance names and their corresponding TCP ports.

WiltonDB does not have a notion of "instance name" and does not support TCP port resolving with SSRP protocol. If non-default (not 1433) TCP port is used, it must be explicitly specified using hostname,port syntax, for example:

sqlcmd -S tcp:DESKTOP-JS80C9O,1433 ...

GSSAPI is an industry-standard protocol for secure authentication defined in RFC 2743. Postgres supports DB authentication with GSSAPI using MIT Kerberos protocol implementation. Postgres can use MIT Kerberos along with Windows Integrated authentication when logins are coming from Active Directory environment. But usually it is only used when Postgres instance runs on non-Windows platform.

For its Windows version Postgres implements SSPI protocol so it can work with Windows Authentication using NTLM or Kerberos protocols under the hood.

Upstream Babelfish project supports GSSAPI for TDS logins, it can be used with AWS Directory Service. Though GSSAPI support is NOT included with WiltonDB Linux builds.

When OS user is logging into DB using Single Sign-On, DB server obtains OS user name as a part of login process. But then DB server needs to find out which DB account to use for this incoming OS user. To solve this, Postgres supports pg_ident.conf user name maps.

Such OS-to-DB user name mapping is currently NOT supported in WiltonDB for TDS connections.

Windows Authentication is supported in WiltonDB 3.3 beginning with update 10.15.1.

First make sure that connection can be established from SSMS using normal password login and a Windows machine name as a hostname:

By default WiltonDB server is listening for TDS connections (on port 1433) only on 127.0.0.1 IP address:

> netstat -na

Active Connections

  Proto  Local Address          Foreign Address        State
  ...
  TCP    127.0.0.1:1433         0.0.0.0:0              LISTENING
  TCP    127.0.0.1:5432         0.0.0.0:0              LISTENING
  ...

Windows machine name may be resolved not to 127.0.0.1 but to external IP address:

> ping -4 DESKTOP-JS80C9O

Pinging DESKTOP-JS80C9O [192.168.178.57] with 32 bytes of data:
Reply from 192.168.178.57: bytes=32 time<1ms TTL=128
...

In this case SSMS connection will fail with the following message:

To solve this listen_addresses configuration parameter needs to be changed from localhost to the corresponding machine name or to * value (that will enable listening on IP address 0.0.0.0). This will effectively enable remote access to this instance of WiltonDB, it is advised to change the password for default superuser wilton before doing this. Changing this parameter requires Windows service restart:

The second required step is to create a DB login that will be used for OS users connecting to DB using Windows Authentication.

There are two possible options:

1. create a DB login for each OS user; login name must be exactly the same as OS user name; it is created in domain\username format with the following restrictions:

• spaces in a domain name (for example, NT AUTHORITY\LOCAL SERVICE user) must be replaced with dash -
• @ symbol is in a user name (for example, MicrosoftAccount\[email protected] user) must be replaced with underscore _

create login [MicrosoftAccount\alex_wiltondb.com] from windows

2. alternatively, create a single wilton_winauth login, that will be used as a fallback for all OS users; this special login cannot be used with password connections, password below is specified just to pass T-SQL syntax requirements, its value is unimportant:

create login wilton_winauth with password = 'unused'

Now connect from SSMS using the same machine name and choosing Windows Authentication. Specifying TCP port is optional, port 1433 will be used by default:

After connection is established DB login will be displayed in Object Explorer:

By default the new login can create objects in master database (in guest schema), but won't have any permissions in other databases. DB users can be created for this login in particular DBs for fine-grained permission control.

WiltonDB does NOT support neither pg_ident.conf OS-to-DB user mapping (like in Postgres) nor automatic import of AD users (like in MSSQL). Windows Authentication users will be logged in either under the DB login that matches OS user name exactly, or under fallback wilton_winauth DB login (if it exists).

Original OS user name can be looked up from the same session using the following query:

select setting from pg_settings where name = 'wilton_winauth_os_user'

setting
----------------------------------
MicrosoftAccount\[email protected]

Current login name can be looked up using suser_name function:

select suser_name()

suser_name
----------------------------------
MicrosoftAccount\alex_wiltondb.com

The following query can be used to lookup the backend PID for current connection:

select pg_backend_pid()

pg_backend_pid
--------------
5248

The following syntax can be used to open Windows Authentication connections to WiltonDB from other client tools/libraries:

sqlcmd:

sqlcmd -S tcp:DESKTOP-JS80C9O,1433 -C -E

bcp:

bcp master.guest.tab1 out tab1.bcp -S tcp:DESKTOP-JS80C9O,1433 -n -T

ADO.NET:

Data Source=DESKTOP-JS80C9O,1433;Integrated Security=True;Trust Server Certificate=yes

mssql-jdbc (make sure that mssql-jdbc_auth-x.x.x.x64.dll is in PATH):

jdbc:sqlserver://DESKTOP-JS80C9O:1433;integratedSecurity=true;trustServerCertificate=true

PyODBC:

DRIVER={ODBC Driver 18 for SQL Server};SERVER=DESKTOP-JS80C9O,1433;Trusted_Connection=yes;TrustServerCertificate=yes