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
Good to know Bret.
I didn't know you could monitor this on the ASE side in this way.
If anyone wants to check this from the client side you can check with Ribo or Wireshark (TDS tracing tools).
These tools can also help if you want to check if other security features are working, such as ssl or kerberos.
Ribo Example:
Open a cmd prompt.
cd %SYBASE%/jutils-3_0/ribo
set environment variables-
JAVA_HOME=C:\Sybase\jre64 (can set this to jre, jre32 as well if they are in the root %SYBASE% directory)
RIBO_HOME=C:\Sybase\jutils-3_0\ribo
start Ribo-
Ribo -s asehost -p aseport -l listeningport
(Ribo -s ldkim-sun -p 7172 -l 5005)
Open another cmd prompt to test isql as the client.
isql -Uaseuser -Pasepassword -Sribohost:riboport
(isql -Usa -Psybase -Slocalhost:5005)
This should generate a tds file in %SYBASE%/jutils-3_0/ribo
You can translate this file to txt by:
Ribo cap#.tds cap#.txt
(Ribo cap0.tds cap0.txt)
without password encryption-
Secure Login Flags [1]: UNUSED (0x00)
with password encryption-
Secure Login Flags [1]: SEC_LOG_ENCRYPT + SEC_LOG_ENCRYPT2 + SEC_LOG_ENCRYPT3 (0xA1)
Wireshark Example:
Have to download Wireshark from online.
Run Wireshark
Select Capture > Options > double click on network card
Enter capture filter:
host asehost && port aseport
(host ldkim-sun && port 7172)
This should turn green when it is able to trace the host and port.
Hit Ok and then Start
Now you can run the client to the host and it will be traced.
isql -Usa -Psybase -Sldkim-sun:7172
No password encryption you can see the password in plain text right after the username sa
With password encryption. The password after sa is gone.
Regards,
Ryan