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.thrallfor 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
10 | |
10 | |
9 | |
9 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 |