Q: How can I tell if connections are using net password encryption?

Q: Are connections using net password encryption?

Turning on the sp_configure setting “net password encryption required” setting can greatly improve security.  Client applications that have not been programmed to use password encryption send their passwords over then netword in plaintext, where the password can be sniffed.  Turning the “net password encryption required” option on prevents such clients from connecting to ASE (though they will still be sending readable passwords over then network while trying to connect).  Presumably the users will contact the SA asking why they can’t connect, allowing the applications to be identified and rewritten to use password encryption.  However, the approach of just turning this option on could cause unacceptable service interruptions.  Is there a way to identify such connections from within ASE before turning on the feature?

A: Yes, assuming the applications have current connections to the server.  It isn’t terribly convenient, but you can run a DBCC PSS(uid,spid) command against a connection. There is a bit set in the field named “p6stat” if net password encryption was not used.

❗ Note: DBCC PSS is not a formally documented command; it’s output may change between versions without warning. This example output is from Adaptive Server Enterprise/15.7.0/EBF 20369 SMP ESD#02 /P/Sun_svr4/OS 5.10/ase157esd2/3109/64-bit/FBO/Sat Jul  7 10:07:17 2012

Here I log in without the -X parameter used to turn on net password encryption in ISQL

bret-sun2% isql -Usa -P********
1> select @@spid
2> go

——
     17

(1 row affected)
1> dbcc traceon(3604)
2> go
00:0000:00000:00017:2013/05/03 12:49:27.44 server  DBCC TRACEON 3604, SPID 17
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1> dbcc pss(1,17)
2> go
{

PSS (any state) for suid 1 – spid 17:

PSS at 0x10006da1a88

PSS Status fields :
pstat=0x10000 (0x00010000 (P_USERPROC))
p2stat=0x1010 (0x00001000 (P2_XLATE), 0x00000010 (P2_DEBUG))
p3stat=0x800 (0x00000800 (P3_PSS_ACTIVE))
p4stat=0x0 (0x00000000)
p5stat=0x8 (0x00000008 (P5_RUSRCONN_USED))
p6stat=0x10 (0x00000010 (P6_NETPWD_NO_ENCRYPT)) 
p7stat=0x0 (0x00000000)
p8stat=0x0 (0x00000000)
pextstat=0x0 (0x00000000)

In contrast, when net password encryption is used, that bit isn’t set.
In the following example, you can see another bit has been set in p8stat showing which encryption method was used
p8stat=0x2 (0x00000002 (P8_NETPWD_RSA_ENCRYPT3)).
The exact bit set when encryption is used may differ depending on the client and server versions.  For instance, in 15.0.3, the bit set is p6stat=0x40 (0x00000040 (P6_NETPWD_RSA_ENCRYPT)).

bret-sun2% isql -Usa -P******** -X
1> select @@spid
2> go

——
     18

(1 row affected)
1> dbcc traceon(3604)
2> go
00:0000:00000:00018:2013/05/03 12:52:47.79 server  DBCC TRACEON 3604, SPID 18
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1> dbcc pss(1,18)
2> go
{

PSS (any state) for suid 1 – spid 18:

PSS at 0x10006dba390

PSS Status fields :
pstat=0x10000 (0x00010000 (P_USERPROC))
p2stat=0x1010 (0x00001000 (P2_XLATE), 0x00000010 (P2_DEBUG))
p3stat=0x800 (0x00000800 (P3_PSS_ACTIVE))
p4stat=0x0 (0x00000000)
p5stat=0x8 (0x00000008 (P5_RUSRCONN_USED))
p6stat=0x0 (0x00000000)
p7stat=0x0 (0x00000000)
p8stat=0x2 (0x00000002 (P8_NETPWD_RSA_ENCRYPT3))
pextstat=0x0 (0x00000000)

If you are using ISQL version 15.0 ESD 12 or higher, the new pipe feature can get you the results for every active spid at once.
(my thanks to Dan Thrall for pointing out this improvement to the method).

In this example, the first 14 spids are system processes so don’t have these bits set.
Spid 43 isn’t using network encryption while spid 44 is using it.

1> dbcc pss(0,0)
2> go | egrep “NETPWD|pspid”

pkspid=13434983   pspid=2   pclient_kpid=13434983   parent_spid=2
pkspid=13566056   pspid=3   pclient_kpid=13566056   parent_spid=3
pkspid=13697129   pspid=4   pclient_kpid=13697129   parent_spid=4
pkspid=13828202   pspid=5   pclient_kpid=13828202   parent_spid=5
pkspid=13959275   pspid=6   pclient_kpid=13959275   parent_spid=6
pkspid=14090348   pspid=7   pclient_kpid=14090348   parent_spid=7
pkspid=14221421   pspid=8   pclient_kpid=14221421   parent_spid=8
pkspid=14352494   pspid=9   pclient_kpid=14352494   parent_spid=9
pkspid=14483567   pspid=10   pclient_kpid=14483567   parent_spid=10
pkspid=14614640   pspid=11   pclient_kpid=14614640   parent_spid=11
pkspid=14745713   pspid=12   pclient_kpid=14745713   parent_spid=12
pkspid=14876786   pspid=13   pclient_kpid=14876786   parent_spid=13
pkspid=16711808   pspid=15   pclient_kpid=16711808   parent_spid=15
pkspid=16056443   pspid=20   pclient_kpid=16056443   parent_spid=20
p6stat=0x10 (0x00000010 (P6_NETPWD_NO_ENCRYPT))
pkspid=19071122   pspid=43   pclient_kpid=19071122   parent_spid=43
p8stat=0x2 (0x00000002 (P8_NETPWD_RSA_ENCRYPT3))
pkspid=19202195   pspid=44   pclient_kpid=19202195   parent_spid=44

Capture the contents of master..sysprocesses at the same time so you can correlate the spid with application names, user logins, and ip addresses.

There is an open feature request, CR 700602, to have the pssinfo() function enhanced to be able to output the pstat fields.

Bret Halford

Support Architect, SAP Active Global Support

Sybase, Inc., an SAP Company

385 Interlocken Crescent Suite 300, Broomfield CO 80021, USA