Security Hole in ADO Connection from ADP to SQL Server

[flouw]

I have an Access 2000 ADP linked to a SQL 2005 database. I have secured the
ADP so that a user is not able to access the database window, not able to
break into code, not able to automate the ADP etc.

The ADP connects with SSPI context to the SQL Server. Each domain user has
access to only ONE stored procedure in the database. It is an encrypted
procedure that returns a user name and an encrypted password. On startup, my
ADP executes the procedure which returns the user name and encrypted password
to the client.

On the client machine, I decrypt the password using super-secured custom
encryption logic. I then disconnect the ADP from the SQL Server and
reconnect using the user name and decrypted password. The user is a SQL user
with dbo rights on the database. In my connection string I am very careful
to set "Persist Security Info=False".

Once I have reconnected my ADP, I execute another procedure to read my
user's application security settings, and I build a custom toolbar for each
user with only the options (forms, reports) they are allowed to access.

So, all of this is quite tight. There is no way to break into code, no way
to interrupt the startup logic, no way to automate the ADP, no way to get the
encyption logic or encryption keys. At the same time, security setup on my
SQL Server is very simple. All I need to do is give each user access to ONE
stored procedure, which I do by adding the domain user to a group. Neat.

Just to be extra sure, I compile my ADP as an ADE before I distribute it to
my clients.

PROBLEM:
(1) Start the ADE, allow startup procedures to complete
(2) Start WinHex or similar memory analysis program to capture the RAM used
by the MSACCESS process
(3) Search memory for Unicode string "Password="
(4) Keep looking until you find a non-null value after the seach string
(5) Wonder why you spent all that time securing the password!

My first question is: why if I set the "Persist Security Info=False" option
do I still need to worry about what Access is doing in RAM with my ADO
connection string!

My second question is: does anyone have any suggestions on how to remove
this from the client machine's memory?

Thanks,
Francois

 

[Sylvain Lafontaine]

You cannot really protect your system using a SQL-Server Login; because the
password is transmitted in clear over the network (don't remember if this
can be circumvented by using the SQL-Server encrypted protocol) and is
stored in clear in the ADP memory address. ADP need to keep the password
because it opens three connection with the SQL-Server and sometime a fourth.
In some cases, the ADO protocol used by ADP can also open other connections.

Instead of a SQL-Server Login, you should use a Windows login if you want a
truly secured environment.

Also, you should never give your users an account with dbo rights. As to
your affirmation that « There is no way to break into code, no way to
interrupt the startup logic, no way to automate the ADP, no way to get the
encyption logic or encryption keys. », I'm not sure about how your ADP is
really secured: what happens if the user try to open the Database window
with the F11 key or the VBA window with the Alt+F11 keys or resets the
AllowBypassKey by executing the command « CurrentProject.Properties.Add
"AllowBypassKey", True » in the Immediate Window?

 

[flouw]

Sylvain,

I'm not worried about transmitting the password over the network in the
clear, as this is a closed LAN on a corporate network and the possibility of
a malicious user installing a sniffer tool undetected is extremely remote.

Believe me, F11 is disabled, ALTF11 is diabled, the user cannot get to code,
cannot access the immediate window, the toolbars are locked down. The ADP is
in a custom "Locked" state where the only way to unlock it is enter a
specified key combination (AUTOKEYS macro controls this) and know the
administrator password (validated against an encrypted procedure on the
server). The ADP closes and is "unlocked" when opened again.

We were using SQL app role security (user connects with SSPI, rights to only
one stored proc, gets the password, we switch the security context of the
connections we can get to, and don't worry about the DB window since the user
has no access there) but the problem with this approach is the mysterious
extra connection that appears and disappears particularly with data bound
subform controls when the server is slow in responding. This connection does
not inherit the app role context. So we were forced to abandon app role. and
use a SQL user.

I really don't like that ADP stores the password in clear in the memory on
the client machine. I found 4 places it is stored per ADP. My next option
is to write code to overwrite the password in memory, but I am worried about
the disappearing-reappearing 4th connection again!

Thanks,
Francois

 

[flouw]

Sylvain,

Is there any way for me to get to the fourth connection in the ADP to set
the app role security?

That would allow me to go back to app role, giving me my SSPI connection to
the SQL Server but still allowing me to control security from the front-end
of my app?

Any comments would be welcome.

Thanks,
Francois

 

[Sylvain Lafontaine]

You're not the first one trying to use Application role with ADP. As far as
I know, all other have fail to achieve a truly secure environment with this
combination without beeing hit by the problem of ghost connections.

However, I still don't understand why you don't want to use a standard
Windows login to access your SQL-Server. By Microsoft's own advice, this is
the best way to achieve a truly secured connection with SQL-Server when
working on a LAN or a VPN.

If you really want to use a SQL-Server login and have full control on all of
your connections, the only possibilities that I see would be to use unbound
forms (and even then, make sure that ADO never opens an hidden second
connection) or to switch to .NET. (And personally, even with one of these
two methods, I would still use a Windows login whenever it's possible.).

By trying to create your own little sql-server security setup, I would think
that you are going the wrong way.

 

[Sylvain Lafontaine]

As far as I know, everyone in the past who has tried to use Application role
with ADP has failed; see:

http://groups.google.com/group/micr...ver&q=application+role&qt_g=Search+this+group

Using Application roles and/or managing yourself the Transactions with bound
forms; two things that would be very useful to do inside an ADP project but
like the old proverb says: same war, same failure. If you want to use one
of these two things with ADP, it's usually easy to have something working
after a few hours of work but if you want to use this in a real (and
complexe) situation, you're usually hit by an insolvable problem at some
point.

I might be wrong here but in my opinion, if you want to use SQL-Server
logins, Application roles or manage the transactions with bound forms; the
only solution that I would see be to forget about Access, ADP and ADO and
switch to .NET.

 

[flouw]

Well, it's not so little... we have 10,000+ database objects. Thousands of
users and thousands of client side objects in 28 linked ADPs. So, without
writing a full management system into my ADPs to handle granting and revoking
user rights based on application security setup (or moving to n-tier and
..NET... yeesh! see you in 3-5 years) I would really like to handle the
connection like this.

We have been running this app on LANs in our branch offices in 120+
countries for 7 years. In that time we have moved from SQL security to app
role and recently back to SQL security (some issues with app role and SQL
2005 anyway). ADP has proved to be a remarkably flexible RAD tool allowing a
very small development team to produce a lot of work in a very short time.

I would just really like to "plug" this hole.

Thanks,
Francois

 

[flouw]

Well, we limped along with app role and ADO for 4 years, so I understand the
pain of anyone who has tried this. Eventually it became such a nuisance we
took the opportunity when moving to SQL 2005 to ditch it.

I am able from VBA to poke around in the RAM of the client machine, find the
password and XXXX it out (really nasty). Any thoughts?

 

[Sylvain Lafontaine]

Well, then, make sure that even if someone find the password, it will
remains useless outside of any valid connection. One possibility would be
to add a counter so that the password cannot be reused for more than 4
connections or checking the connections themselves (you might have to
deactivate the connection pooling here; i'm not sure) or to give each
user/application another (encrypted) password that must be passed as a
parameter with each call. SQL-Server 2005 offers a lot of support for
encryption and certificates, maybe you will find what you are looking for
there.

You could also take a look at IpSec (see
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec03.mspx
) but I don't know if you will like it or even if you can use it with ADP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Well, we limped along with app role and ADO for 4 years, so I understand
the
pain of anyone who has tried this. Eventually it became such a nuisance
we
took the opportunity when moving to SQL 2005 to ditch it.

I am able from VBA to poke around in the RAM of the client machine, find
the
password and XXXX it out (really nasty). Any thoughts?

 

[flouw]

Well, the SQL user is really a "proxy" user so all users connect with the
same connection (same SQL user, same password). We store a record in a
security table on the SQL Server to give us server-side user context (pid,
host name and NT user name in the table). The security record is added each
time a user connects and is based on the pid not the spid to handle the
phantom connection. A counter in this context doesn't make sense.
Additionally we have 28 ADPs chained together as code projects, so we have
the potential (unlikely since each code project is divided by function e.g.
purchasing vs accounts payable) of up to 28x4 simultaneous connections per
user.

We are exploring an SSID connection solution which requires the insertion of
a small bit of security code into each of our 7800+ stored procedures and
looks promising.

We are also exploring adjusting the RAM which I think is dangerous (and
maybe illegal!)

Thanks,
Francois

Well, then, make sure that even if someone find the password, it will
remains useless outside of any valid connection. One possibility would be
to add a counter so that the password cannot be reused for more than 4
connections or checking the connections themselves (you might have to
deactivate the connection pooling here; i'm not sure) or to give each
user/application another (encrypted) password that must be passed as a
parameter with each call. SQL-Server 2005 offers a lot of support for
encryption and certificates, maybe you will find what you are looking for
there.

You could also take a look at IpSec (see
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec03.mspx
) but I don't know if you will like it or even if you can use it with ADP.

 

[Vadim Rapp]

f> PROBLEM:
f> (1) Start the ADE, allow startup procedures to complete
f> (2) Start WinHex or similar memory analysis program to capture the RAM
f> used by the MSACCESS process

Since you mentioned this is corporate environment, I think the right path
would be in using group policy to restrict the users from running these
kinds of applications. Plus some administrative steps. There are things that
are better addressed by IT, but also there are things that have to be
addressed by HR. Search workstations for winhex, and if found, treat as
sabotage against the company. Otherwise, it's arms race where you will never
win, while spending increasing resources all the time. It seems to me that
what you have described as already implemented, is already a clear sign of
that race that went beyond the reasonable limits long ago. If your
environment needs to be so secure (presumably not only for this adp
application), the answer should be in the network policies + discipline.

Vadim Rapp

 

[Sylvain Lafontaine]

Another possibility could be to use thin clients (Terminal Server) or thin
machines (diskless workstations). If the users don't even have the
possibility of installing WinHex and similar softwares in the environment
running the ADP application, this will seriously limit their capacity of
breaking through the security.

 

[flouw]

We have explored Terminal Server in the past. It's a good suggestion. Thanks.

 

[flouw]

I think, Vadim, that you have expertly summed up my conclusions on the
matter. I agree that we are wasting resources on this. We are talking about
managed workstations, with policies enforcing approved software, so I suspect
that once the calamity howlers have quietened down a bit we will all be able
to continue with our lives (and the app will live on).

I still feel it would be better if ADP did not store password information in
the clear in memory. That is not a nice design.

We do have a technical solution if the HR solution is not adopted. It
involves including a security exit at the top of every stored procedure (all
our data access from the ADP is via stored procedures) that if the procedure
is not executed from a recognized client process (i.e. inside our client with
proper security protocol initiated) the procedure exits without returning any
records. It is not pretty, but it allows us to connect SSPI and grant every
user EXE rights on (almost) all stored procedures. The problem is contention
for the central security table in a high transaction volume environment,
which will degrade performance.

Thanks for your input.

 

[Vadim Rapp]

f> I suspect that once the calamity howlers have quietened down a bit we
f> will all be able to continue with our lives (and the app will live on).

yeah, good luck... I know a whole country, and a really big one, where I
happen to live, where there's no sign of quietening down, and "safety and
protection" is steadily becoming more important than anything else,
including liberty and freedom, - even though there hasn't ever been a single
real "terrorist" on the horizon.

Vadim Rapp

 

[Tom van Stiphout]

On Thu, 7 Feb 2008 11:05:00 -0800, flouw

Wow, I thought our company had written some good-size ADP projects,
but you beat me by at least an order of magnitude. I hope you have at
least that much more cloud with MSFT (though 10 * 0 = 0 ) to keep
ADP alive. I am very concerned about the lack of support in A2007
Runtime which I posted about.

On the topic of your idea of writing some code to overwrite memory
locations: who are you trying to keep out? The NSA? Even if it is
technically possible (it might require a kernel mode device driver -
not your average VBA code), there must be a small window of
opportunity where Access actually uses those connection strings and
needs the pw to be normal. My snooper could be waiting for that.

Also, you don't think a software-based sniffer is a possibility, yet
you think WinHex is? How about I install a keyboard logger on the
computer in the conference room, and wait for a manager to login?
As others have said Group Policies seems the obvious way to keep
people from installing applications they shouldn't. We have once
successfully used GP to lock down a Windows machine for use in a
Kiosk. And you know how people abuse kiosk applications.

One tantalizing idea is to use rootkit-like code: you know how some
applications can intercept what you're trying to do and do something
different? Well, there are only so many APIs that memory sniffers use
to do their thing. You can sit in-between (perhaps using Hooks) and
throw them some curveballs.

If you are thinking of rewriting for .NET, be aware there is an
Interop Toolkit available at microsoft.com which will allow you to
migrate your applications one form at a time. We are building up some
experience with that, and so far I like it quite a bit.

Regards,

-Tom.

Well, it's not so little... we have 10,000+ database objects. Thousands of
users and thousands of client side objects in 28 linked ADPs. So, without
writing a full management system into my ADPs to handle granting and revoking
user rights based on application security setup (or moving to n-tier and
.NET... yeesh! see you in 3-5 years) I would really like to handle the
connection like this.

We have been running this app on LANs in our branch offices in 120+
countries for 7 years. In that time we have moved from SQL security to app
role and recently back to SQL security (some issues with app role and SQL
2005 anyway). ADP has proved to be a remarkably flexible RAD tool allowing a
very small development team to produce a lot of work in a very short time.

I would just really like to "plug" this hole.

Thanks,
Francois

<clip>

 

[raibeart]

I would like to know more about the interop toolkit you are talking
about.

Robert

 

[Tom van Stiphout]

On Thu, 14 Feb 2008 06:10:55 -0800 (PST), raibeart

Check this out:
http://groups.google.com/group/comp...read/thread/78d8c3a80cca3cc1/c4c77f430c7402db

-Tom.

 

[flouw]

I hope you have at

least that much more cloud with MSFT (though 10 * 0 = 0 ) to keep
ADP alive. I am very concerned about the lack of support in A2007
Runtime which I posted about.

Yes, I have seen your posts about ADP and Acc2007. We have tried several
different options with Acc2007. For now, we are waiting to see if ADE
support improves with the runtime (no good if reports don't work). We also
had a major performance problem with Acc2007 attempting to interpret the
entire database structure each time you open a new form, which it seems we
are able to address.

As others have said Group Policies seems the obvious way to keep
people from installing applications they shouldn't.

I agree. Most users are not local admins, so no problem. The theoretical
concern is mostly regarding a rogue developer.

If you are thinking of rewriting for .NET, be aware there is an
Interop Toolkit available at microsoft.com which will allow you to
migrate your applications one form at a time. We are building up some
experience with that, and so far I like it quite a bit.

I've seen posts about the Interop Toolkit and it looks promising. So far I
am not committing wholesale to .NET. ADP is such a great technology I just
can't believe that MSFT will let it die. Although sometimes it seems
marketing takes precedence...

Thanks for your thoughts.

 

[wangtianwu]

I have an Access 2000 ADP linked to a SQL 2005 database. I have secured
the
ADP so that a user is not able to access the database window, not able to
break into code, not able to automate the ADP etc.

The ADP connects with SSPI context to the SQL Server. Each domain user
has
access to only ONE stored procedure in the database. It is an encrypted
procedure that returns a user name and an encrypted password. On startup,
my
ADP executes the procedure which returns the user name and encrypted
password
to the client.

On the client machine, I decrypt the password using super-secured custom
encryption logic. I then disconnect the ADP from the SQL Server and
reconnect using the user name and decrypted password. The user is a SQL
user
with dbo rights on the database. In my connection string I am very
careful
to set "Persist Security Info=False".

Once I have reconnected my ADP, I execute another procedure to read my
user's application security settings, and I build a custom toolbar for
each
user with only the options (forms, reports) they are allowed to access.

So, all of this is quite tight. There is no way to break into code, no
way
to interrupt the startup logic, no way to automate the ADP, no way to get
the
encyption logic or encryption keys. At the same time, security setup on
my
SQL Server is very simple. All I need to do is give each user access to
ONE
stored procedure, which I do by adding the domain user to a group. Neat.

Just to be extra sure, I compile my ADP as an ADE before I distribute it
to
my clients.

PROBLEM:
(1) Start the ADE, allow startup procedures to complete
(2) Start WinHex or similar memory analysis program to capture the RAM
used
by the MSACCESS process
(3) Search memory for Unicode string "Password="
(4) Keep looking until you find a non-null value after the seach string
(5) Wonder why you spent all that time securing the password!

My first question is: why if I set the "Persist Security Info=False"
option
do I still need to worry about what Access is doing in RAM with my ADO
connection string!

My second question is: does anyone have any suggestions on how to remove
this from the client machine's memory?

Thanks,
Francois