vb123.com.au
How
To Avoid An Access 2003 Sandbox Storm
by Garry Robinson - Editor of
vb123.com
Earlier in the year when I was working on my book, an
opportunity came up to spend three weeks working for a potentially huge copper
mine that is located in the Gobi desert of Mongolia. Just before the trip, I had
gone against all my better judgment and installed Access 2003 beta 2 on the
laptop that I was to take on my trip. Of course my mind was continually focused
on book deadlines and the PC seemed to be working well when I took off. At the
site I was asked to demonstrate a metallurgical database that I had written for
another mine. The night before the presentation and its time to have a look at
the Access 2000 database and you guessed it. Nothing works. Frantically I tried
a number of things and my doubting mind blamed the last software installation on
my computer, the beta version of Access 2003. I uninstall the software and guess
what, the problem goes away. I wouldn’t be too surprised except for the fact
that this was a beta two version and not a lot should go wrong with this
version. When I returned to Australia after three weeks in the wild, I had a
quick look through the Access beta newsgroup and formed an opinion that the
problem was not Access itself but something to do with the new Digital
Signatures in Access 2003. In this article, I explore this problem and explain
that it a Jet Engine feature called the Sandbox that was causing all the grief.
Once I have explained some of the issues that relate to the Sandbox, I will show
you how you can prepare for the sandbox issues that could be heading your way
soon. This issues are important as any person who purchases Access 2003 could be
opening your Access 2000 or 2002 format database and encountering sandbox errors
any day now.
Here is the page on Access 2007 Sandbox mode from Microsoft
Recommendations
Normally I would put my recommendation or conclusion at the
end. In this case, the recommendations are more important so I will place then
at the front of the article. They are
·
All Access 2000+ developers should upgrade their version of Jet 4 to Service
Pack 8 sooner rather than later. Whilst developing, you should work with a
special registration key called the Sandbox mode changed to the higher
protection level of 3 so that a Jet Engine protection mechanism called sandbox
mode is used at all times.
· All
key databases users (database testers) should upgrade to Service Pack 8 soon
after the developers are happy that the obvious sandbox issues have been
resolved.
· All
users should upgrade to Service Pack 8 of Jet before upgrading to Office 2003
Professional or Access 2003.
· All
new computers should include upgrades to this service pack.
Action: Upgrade all your users to the latest version of the Jet Engine
here. Type in Jet
Engine into the Search Box and choose the latest version for your operating
system. Do this for everyone running your database.
This link may be quicker
Garry’s Problem - My Critical Date Functions Aren’t
Working
The metallurgical system that failed has an unusual design
because all the key tables are related to each other by time and not by more
conventional data relationships. In the database there is shift data, daily
data, weekly data, monthly data and even yearly target figures. To combine this
different information, the software uses consolidation queries that are joined
by special functions that produce time identifiers for each record. To see an
example of this, have a look at the form that allows users to select a month as
a filter before using a data entry form systems (shown in Figure 1).
<<< Click on the small picture to expand it
Figure 1 - The Data Entry Form That Failed As A Result Of The Sandbox
Unfortunately when I opened this form using Access 2002 (on
a computer with Office 2003 beta installed), the data in the list box came up
with errors for the month ID. I traced this error to the query that was used as
the recordsource for the list box
SELECT
calYYYYMM_FX([MyDateField]) AS MonthID, lockDataField AS Locked FROM
MonthlyDataStatus;
After looking at the query, the error could be traced to the
function CalYYYYMM_FX. This is function that translates a date into a text
string. This special date text string sorts well and has proved a very stable
approach when joining queries with dates. The function itself is pretty
innocuous as shown below
Function
CalYYYYMM_FX(date_req As Variant) As String
'--> CalYYYYMM_FX -
Returns 7 char calendar year-month text for any date
'
' date_req "variant" Date/time that you want to convert
' returns "string"
'
' This function returns seven character year text
' between 1933-01 and 2032-12 for date data
'
' eg aString = CalYYYYMM_FX(#12-May-97#)
' will return "1997-05" to text variable aString
CalYYYYMM_FX =
Format$(date_req, "yyyy-mm")
End Function
So in a nutshell, this application was rendered unworkable
because queries like this one were used extensively to bring all the different
date related data together and the culprit seemed to be Access 2003. There was
also another big problem that was happening both with Access 2002 and Access
2003, none of the wizards would work.
The Problems Will Happen If You Only Have Access 2000 or
2002
At this stage I will skip a few of the Access 2003 issues
that I encountered along the way and explain how you can simulate this problem
using only Access 2002 or 2000. When the Jet 4 DLL was upgraded to service pack
3 in late 1999, one of the enhancements included a special verification process
called Sandbox mode. As this change was designed to thwart the many vb script
viruses like Mellissa and ILoveYou that were around at the time of the virus
and because its default mode didn’t effect or enhance how Access used the jet
engine, its arrival was only heralded by a couple of pages posted to MSDN. In
other words, it was as if nothing had happened. Now with the release of Access
2003, it seems like the sandbox mode didn’t work very well and the Access team
had to rush out a last minute patch that requires users to download a new
version of Jet from the WindowsUpdate site.
Let’s first have a look at this sandbox thing. If you search
the Registry (open Regedit.exe in the Windows directory) for the word
SandboxMode, you should find an entry in the Jet 4.0 tree (as shown in Figure
2). In this case I have set the data entry to the safest sandbox protection
level of 3, but you will probably find that its value will be initially be set
to 2.
Figure 2 - The sandbox entry in the registry.
If you find the key, then you are using Service Pack 3 or
higher. To check which version of the service pack that you are using, search
for the file MsJet40.dll which you will probably find in the c:\windows
directory. Right click on the file, choose properties and select the version
tab. If you look at the following table, you will find information on the date
and version numbers for all the Jet service packs since version 3.
MsJet40.dll Service
Pack Date
4.0.8015.0 8 October 15, 2003
4.0.7328.0 7 June 11, 2003
4.0.6218.0 6 April 15, 2002
4.0.4431.1 5 October 26, 2000
4.0.3714.
4 April 26, 2000
4.00.2927.4 3 October 11, 1999
Changing The Sandbox Mode
The sandbox is allowed to have four different registry
settings as shown in the following table. As it is highly likely that you have
used setting 2 for a number of years now, you should refrain from using either
the 0 or 1 setting unless you are debugging a VB or other external application.
Setting
Description
0
Sandbox mode is disabled at all times.
1
Sandbox mode is used for Access
applications, but not for non-Access Applications.
2
Sandbox mode is used for non-Access
applications, but not for Access Applications. (This is the default value.)
3
Sandbox mode is used at all times.
If you were to change the Sandbox registry setting from 2 to
3 (sandbox used at all times), you will probably find that have now introduced a
number of problems into your Access 2000+ databases functions. This entry
applies to everyone that uses your computer. Once you make this change to the
registry, the most obvious errors that you should encounter is that all wizards
will bring up the error illustrated in Figure 3.
Figure 3 - Error caused by running any wizard in sandboxmode with old
versions of Jet 4
Another way of describing this registry setting is by
showing you the more formal definition of the registry setting in the format
that VB.Net and VB6 programmers will be familiar with.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SandboxMode = 3
Whilst the obvious solution to this problem is to reset the
sandbox registry setting back to 2 and carry on life as normal, I recommend that
you upgrade your Jet engine to Jet 4 service pack 8 to fix this problem. I will
explain how to do this.
Downloads … What To Do If You Do Not Want To Manually
Edit The Registry
I know, we should not hack around in the registry too much
and we definitely shouldn’t ask our users to do likewise. In fact this article
is probably the only time that I have ever contemplated telling anyone else to
change the registry. To make life easier for you, the download files for this
article includes two .REG files. These little files will change the registry for
you if you double click on them. They are called sandbox3.reg which changes the
SandboxMode setting to 3 and sandbox2.reg which sets it back to the less safe
value.
Click here for the
download file if you own "The Toolshed" Else click
here
Upgrading To Jet Service Pack 8
To upgrade to the latest service pack of jet, the best way
is to follow these instructions.
1.
Point your browser to
www.windowsupdate.com
2.
Click on the Scan for Updates link
3.
Pick Jet 4, service pack 8 from the Windows list as shown in Figure 4.
4.
Find Update for Jet 4.0 Service Pack 8 (KB829558) and click Add
5.
Click the Review and Install updates link
6.
Click Install Now
Figure 4 - Downloading Jet 4.0 Service Pack 8
When you have finished, the file
c:\windows\system32\msjet40.dll should have a version property value of
4.0.8015.0 or later.
This upgrade will change the version of Jet 4 that you are
using for Access 2000, 2002, 2003. Once you have completed a trial period with
this upgrade, you should start informing your customers that they too should
upgrade. If your clients are using Access as a client to Oracle, the notes on
the service pack indicate that it is important that you upgrade to this service
pack as it fixes a number of critical bugs.
What does the Sandbox Try To Protect?
Sandbox mode is designed to protect your computer and
databases against database objects that are designed with a malicious intent. It
does this by stopping unsafe expressions being executed from one of the
following
·
From a query
·
The default property of a control
·
The on event line of a control embedded in a form or report
Any of these actions can be fired in a database without a
VBA Project and because of this, macro detection and macro signatures will have
no effect as they only kick in when a database has VBA code. For example, a
query that that could do some damage is illustrated by the following SQL. In
this sample, I show you how you can launch the calculator program from a query.
If you try this with Access 97 or with the SandboxMode registry setting set to 2
or 0, this will launch the program quite happily. It doesn’t leave much to the
imagination to think what other things you could do with this particular power.
SELECT
Shell("c:\windows\System32\calc.exe") FROM Customers;
As you may expect, there are a number of other common VBA
functions that are banned in this mode because they two could be used to ill
effect and they include:
Command, CurDir, DeleteSetting,
FileCopy, Kill, SendKeys, SetAttr, Shell, Spc, UserForms, Width
One of the other places that the jet sandbox testing comes
into effect is when you have a special function encoded as an expression in the
Event property boxes for any form, report or control on a form or report. If you
store what Jet thinks is an illegal expression in this property, the expression
will not work. Figure 5 shows two event properties that have illegal
expressions.
Figure 5 - Illegal expressions that will be caught by the sandbox mode.
If you look at the On Enter event in figure 5, you will find
that I have used an Access specific expression rather than a VBA expression.
There are approximately 60 of these that are not allowed by sandbox testing
including
AddToFavorites AutoCorrect
CompactRepair CreateForm CreateReport DBEngine DDEExecute
DoCmd Echo ExportXML FileDialog
FileSearch FollowHyperlink ImportXML InsertText
LanguageSettings LoadFromText
LoadPicture Modules NewCurrentDatabase OpenCurrentDatabase RefreshDatabaseWindow
RefreshTitleBar RunCommand SetDefaultWorkgroupFile SetHiddenAttribute SetOption
SetUndoRecording SysCmd
Obviously if you undertake this practice for executing
snippets of VBA code, you may be in for a rude shock. The solution in this case
is simply to convert the code to VBA and there are many reasons why that is a
good idea anyway.
By now you will be wondering what happens when the jet
Sandbox testing encounters what could be an illegal expression? You will receive
an error that says “The expression that you entered has a function name that
Access cannot find”. The code then stops. If you have a unsafe expression in a
query, you will find an error text string in your results. This sort of error
reporting should concern you because many users will not report this type of
error because the solution for them in most cases it is simply a matter of
clicking OK to return to the normal interface.
Why should this concern you if you and your users are
happily chugging along with Access 2000 or 2002? Simply because when Access 2003
is installed on a computer, it is highly likely that the sandbox registry item
will be modified to the safer level of 3. At that stage, you will want to have
removed the majority of these sandbox related issues from your database because
your database may not work too well, just like my important database. Remember
too that the number of errors is far greater if you are running an earlier
version of Jet version 4. Now that I have found this out, my programmers and I
now work with the sandbox registry item set to 3 at all times so that we will
reduce the Access 2003 surprises at a later stage.
Where Does Access 2003 Fit Into The Picture
Whilst nothing in life can be guaranteed, at some stage soon
you or one of your users/customers will be trying out one of your databases with
Access 2003. When they do, you will need to be prepared with a solution for the
issues that may lie ahead. Therefore as it is likely that this is going to
happen to you, I thought I would first share with you what the Microsoft Access
2003 help tells you about the new version.
“If you are currently using Access 2002, migrating to Access
2003 is quick and easy. All you need to do is install and run Access 2003, and
simply open your Access 2002 files! There is no need to back up and convert your
files, because the file format did not change in Access 2003”. And for databases
that have code, “You don’t have to do anything special to open a file that
contains code, because the new version of Access is fully compatible with files
created using Access 2002”. If only life was this easy!
When you install Access 2003 on a computer, it is very
likely that you are going to encounter the form shown in Figure 6.
Figure 6 - The screen you encounter in Access 2003 when you don’t have the
latest Jet 4 Service Pack
This form is the very first thing that you or your users are
likely to see when you open an Access 2003 database and its purpose is not
obvious at first glance. I have looked at this form a number of time and I still
have to concentrate hard to work out what I was meant to do. If you click No,
you get the Royal boot and Access closes down. If you click Yes, you really must
wonder if you are about to encounter some evil goblin. In fact, you already have
encountered the evil goblin because your computer hasn’t been upgraded to Jet
Service Pack 8. More confusion follows as the next form Access displays after
this form is likely to be one of those macro protection forms (see figure 7)
that you have probably seen when you open an Excel spreadsheet that has a VBA
project. In this article, I am not going to rant and rave about the usefulness
of these as a protection mechanism except to warn you that if you haven’t
installed Jet Service Pack 8 on the computer, every time that you or your users
change the macro security level in Access, the form shown before in Figure 6
will be returning to haunt you. Now you will understand why the Jet Service pack
upgrade is important.
Figure 7 - Macro Security Warning When Opening a Database
Turning Off The Macro Security Warnings
Whilst I could now beat my chest and tell you that
developers should all rush out and purchase security certificates from Verisign
or Thwate for a few hundred dollars a year, I am going to take a more realistic
approach that assumes that most developers and users are going to adopt the
lower security setting because it is just easier and less costly. With Peters
permission, another article will follow soon that delves into the mysterious
world of security certificates and macro protection strategies for Access 2003
users. Anyway as the medium level security dialog is going to drive you nuts,
you will no doubt find that you can turn off the macro security as follows
Go to the menu Tools ~ Macros ~ Security and the dialog box
displayed in Figure 4 will be displayed. Select the Low Security Level on the
macro security dialog and and all the prompts will disappear the next time that
you open a database. Possibly though a macro virus will be run from your
database.
What Happens When You Select Low Security
When you select the Low security setting, you would think
that the complications for you (and your users) would be over. Unfortunately
not, there is more. Immediately following that dialog is another tricky question
about unsafe expressions (as shown in Figure 8). What this prompt is saying is
that if you Click Yes, Sandbox mode will be turned off and if you click No it
will be turned on. If I were a humble user, I would imagine that I would have no
idea what this meant. The conclusion that you may draw from this is that you
probably have no way of determining which sandbox mode setting your users are
going to select so that you can prepare for both.
Figure 8 - Macro Security Levels
What Happens to the sandbox mode for the higher macro
security settings?
If your users continue to use the medium or high macro
security setting, your database will always be forced to use the SandboxMode
setting of 3. As the medium setting is the default mode, you must prepare for
this situation.
Useful Further Reading and Resources
Rick Dobson wrote a great article
on macro security for Access 2003 in for Smart
Access in April 2004
Information about Service Pack for Jet
http://support.microsoft.com/default.aspx?kbid=239114
Listings and discussions of unsafe VBA and Control Event Function
http://support.microsoft.com/default.aspx?kbid=294698
How to obtain the latest Jet Service pack
http://support.microsoft.com/default.aspx?kbid=239114
Information about the latest service pack
http://support.microsoft.com/?kbid=829558
Read about Microsoft Jet Expression Service sandbox mode
http://office.microsoft.com/assistance/preview.aspx?AssetID=HP010446591033
A page at my site covering things to be aware of when installing Access 2003
http://www.vb123.com/toolshed/03_access/install.htm
Find out how to extract the version of Jet your users are
running (and log to a file)
Go to this page
Conclusion
When Access 2003 rolls along onto your client or companies
computers, you are going to look like a bit of a turkey if the best thing that
you can recommend to these users is that they turn off all macro protection so
that your software works. It is much better to make sure that your database
works well by testing for sandbox mode at all times on your pc. You should also
endeavor to ensure that all your key testers upgrade to the latest version of
Jet 4 and use the sandbox protection as well to improve the coverage. Finally,
you can reduce the complication of the Access 2003 rollout by ensuring that as
many people as possible have upgraded their version of Jet 4 before they upgrade
Access.
A different slant on these issues is a marketing one. If you
are an independent Access developer, the simplest approach to Macro security in
Access 2003 would be to tell your clients to adopt the lowest level of
protection. In these days of constant computer security issues, being weak on
security is not a good long term strategy. Wouldn’t it be a great thing instead
to say to your customers that you are recommending that they adopt Sandbox
protection for all versions of Access 2000 upwards as their first stage of
Access macro protection? To do this, you need to ensure that they are using Jet
Service Pack and have the sandbox registry item set so that unsafe expressions
are protected on their computer. That is the approach that my company has taken
both for development and end users for the time being.
Author Bio.
Garry Robinson runs GR-FX Pty Limited, a company based in Sydney,
Australia. If you want to keep up to date with the his latest postings on Access
Issues, visit his companies popular web site at http://www.vb123.com/ or sign
up for his Access email newsletter by sending a blank email to tips@vb123.com
The web site features many Access resource sand software that are used by more
than 10,000 readers a month. To find out about Garry’s book which is called
“Real World Microsoft Access Database Protection and Security”, point your
browser to http://www.vb123.com/map/. You can find Garry’s contact details at …
www.gr-fx.com
Other Pages On This Site You Might Like To Read
Cleaning Up An Access
Database
Microsoft Access Security
And Passwords
Restrict The People Who Can
Use Your Database Folder
An Access Global
Error Handler
Click on the following button
to jump to the next page in the document loop.
External Links On Topic
http://www.databasedev.co.uk/jet-sandbox-mode.html
Notes: Since article was published>
Sandbox and Access 2007
Macro security information in the registry.
The security level can be changed for either
individual users or created for all users who log onto the computer. If you need
to set the security for an individual user, then you need to change this key:
\\HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Access\Security\Level
Change the value of this key to (1 for low security; 2 = medium security; 3 =
high security)Or for all users on the computer, you need to create this key:
If you need to set the same security level for all users who log onto the
computer instead, then you need to create this new Registry key:
\\HKEY_LOCAL_MACHINE\Software\Microsoft\Office\11.0\Access\Security\Level
Create the key of type DWORD and set the value to (1 for low security; 2 =
medium security; 3 = high security)
Our Tools and Resources
vb123 Professionals
Get Good Help Here
If you need help with a database or
Office programming,
our Professionals could be the answer because we have worked on many
similar solutions
Frontpage Conversions
We have converted vb123.com to Expression Web,
contact us if we can help you move to the latest Microsoft web tool.
About The Editor ~ Contact Us
Garry Robinson writes for a number
of popular computer magazines, is now a book author and has worked on
100+ Access databases. He is based in Sydney, Australia
Access 2003 Security
How
To Avoid An Access 2003 Sandbox Storm
by Garry Robinson - Editor of vb123.com
Earlier in the year when I was working on my book, an opportunity came up to spend three weeks working for a potentially huge copper mine that is located in the Gobi desert of Mongolia. Just before the trip, I had gone against all my better judgment and installed Access 2003 beta 2 on the laptop that I was to take on my trip. Of course my mind was continually focused on book deadlines and the PC seemed to be working well when I took off. At the site I was asked to demonstrate a metallurgical database that I had written for another mine. The night before the presentation and its time to have a look at the Access 2000 database and you guessed it. Nothing works. Frantically I tried a number of things and my doubting mind blamed the last software installation on my computer, the beta version of Access 2003. I uninstall the software and guess what, the problem goes away. I wouldn’t be too surprised except for the fact that this was a beta two version and not a lot should go wrong with this version. When I returned to Australia after three weeks in the wild, I had a quick look through the Access beta newsgroup and formed an opinion that the problem was not Access itself but something to do with the new Digital Signatures in Access 2003. In this article, I explore this problem and explain that it a Jet Engine feature called the Sandbox that was causing all the grief. Once I have explained some of the issues that relate to the Sandbox, I will show you how you can prepare for the sandbox issues that could be heading your way soon. This issues are important as any person who purchases Access 2003 could be opening your Access 2000 or 2002 format database and encountering sandbox errors any day now.
Here is the page on Access 2007 Sandbox mode from Microsoft
Recommendations
Normally I would put my recommendation or conclusion at the end. In this case, the recommendations are more important so I will place then at the front of the article. They are
· All Access 2000+ developers should upgrade their version of Jet 4 to Service Pack 8 sooner rather than later. Whilst developing, you should work with a special registration key called the Sandbox mode changed to the higher protection level of 3 so that a Jet Engine protection mechanism called sandbox mode is used at all times.
· All key databases users (database testers) should upgrade to Service Pack 8 soon after the developers are happy that the obvious sandbox issues have been resolved.
· All users should upgrade to Service Pack 8 of Jet before upgrading to Office 2003 Professional or Access 2003.
· All
new computers should include upgrades to this service pack.
|
Action: Upgrade all your users to the latest version of the Jet Engine here. Type in Jet Engine into the Search Box and choose the latest version for your operating system. Do this for everyone running your database. This link may be quicker |
Garry’s Problem - My Critical Date Functions Aren’t Working
The metallurgical system that failed has an unusual design because all the key tables are related to each other by time and not by more conventional data relationships. In the database there is shift data, daily data, weekly data, monthly data and even yearly target figures. To combine this different information, the software uses consolidation queries that are joined by special functions that produce time identifiers for each record. To see an example of this, have a look at the form that allows users to select a month as a filter before using a data entry form systems (shown in Figure 1).
<<< Click on the small picture to expand it
Figure 1 - The Data Entry Form That Failed As A Result Of The Sandbox
Unfortunately when I opened this form using Access 2002 (on a computer with Office 2003 beta installed), the data in the list box came up with errors for the month ID. I traced this error to the query that was used as the recordsource for the list box
SELECT calYYYYMM_FX([MyDateField]) AS MonthID, lockDataField AS Locked FROM MonthlyDataStatus;
After looking at the query, the error could be traced to the function CalYYYYMM_FX. This is function that translates a date into a text string. This special date text string sorts well and has proved a very stable approach when joining queries with dates. The function itself is pretty innocuous as shown below
Function CalYYYYMM_FX(date_req As Variant) As String
'--> CalYYYYMM_FX -
Returns 7 char calendar year-month text for any date
'
' date_req "variant" Date/time that you want to convert
' returns "string"
'
' This function returns seven character year text
' between 1933-01 and 2032-12 for date data
'
' eg aString = CalYYYYMM_FX(#12-May-97#)
' will return "1997-05" to text variable aString
CalYYYYMM_FX = Format$(date_req, "yyyy-mm")
End Function
So in a nutshell, this application was rendered unworkable because queries like this one were used extensively to bring all the different date related data together and the culprit seemed to be Access 2003. There was also another big problem that was happening both with Access 2002 and Access 2003, none of the wizards would work.
The Problems Will Happen If You Only Have Access 2000 or 2002
At this stage I will skip a few of the Access 2003 issues that I encountered along the way and explain how you can simulate this problem using only Access 2002 or 2000. When the Jet 4 DLL was upgraded to service pack 3 in late 1999, one of the enhancements included a special verification process called Sandbox mode. As this change was designed to thwart the many vb script viruses like Mellissa and ILoveYou that were around at the time of the virus and because its default mode didn’t effect or enhance how Access used the jet engine, its arrival was only heralded by a couple of pages posted to MSDN. In other words, it was as if nothing had happened. Now with the release of Access 2003, it seems like the sandbox mode didn’t work very well and the Access team had to rush out a last minute patch that requires users to download a new version of Jet from the WindowsUpdate site.
Let’s first have a look at this sandbox thing. If you search the Registry (open Regedit.exe in the Windows directory) for the word SandboxMode, you should find an entry in the Jet 4.0 tree (as shown in Figure 2). In this case I have set the data entry to the safest sandbox protection level of 3, but you will probably find that its value will be initially be set to 2.
Figure 2 - The sandbox entry in the registry.
If you find the key, then you are using Service Pack 3 or higher. To check which version of the service pack that you are using, search for the file MsJet40.dll which you will probably find in the c:\windows directory. Right click on the file, choose properties and select the version tab. If you look at the following table, you will find information on the date and version numbers for all the Jet service packs since version 3.
MsJet40.dll Service
Pack Date
4.0.8015.0 8 October 15, 2003
4.0.7328.0 7 June 11, 2003
4.0.6218.0 6 April 15, 2002
4.0.4431.1 5 October 26, 2000
4.0.3714.
4 April 26, 2000
4.00.2927.4 3 October 11, 1999
Changing The Sandbox Mode
The sandbox is allowed to have four different registry settings as shown in the following table. As it is highly likely that you have used setting 2 for a number of years now, you should refrain from using either the 0 or 1 setting unless you are debugging a VB or other external application.
|
|
|
| 0 |
|
| 1 |
|
| 2 |
|
| 3 |
|
If you were to change the Sandbox registry setting from 2 to 3 (sandbox used at all times), you will probably find that have now introduced a number of problems into your Access 2000+ databases functions. This entry applies to everyone that uses your computer. Once you make this change to the registry, the most obvious errors that you should encounter is that all wizards will bring up the error illustrated in Figure 3.
Figure 3 - Error caused by running any wizard in sandboxmode with old
versions of Jet 4
Another way of describing this registry setting is by showing you the more formal definition of the registry setting in the format that VB.Net and VB6 programmers will be familiar with.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SandboxMode = 3
Whilst the obvious solution to this problem is to reset the sandbox registry setting back to 2 and carry on life as normal, I recommend that you upgrade your Jet engine to Jet 4 service pack 8 to fix this problem. I will explain how to do this.
Downloads … What To Do If You Do Not Want To Manually Edit The Registry
I know, we should not hack around in the registry too much and we definitely shouldn’t ask our users to do likewise. In fact this article is probably the only time that I have ever contemplated telling anyone else to change the registry. To make life easier for you, the download files for this article includes two .REG files. These little files will change the registry for you if you double click on them. They are called sandbox3.reg which changes the SandboxMode setting to 3 and sandbox2.reg which sets it back to the less safe value.
Click here for the
download file if you own "The Toolshed" Else click
here
Upgrading To Jet Service Pack 8
To upgrade to the latest service pack of jet, the best way is to follow these instructions.
1. Point your browser to www.windowsupdate.com
2. Click on the Scan for Updates link
3. Pick Jet 4, service pack 8 from the Windows list as shown in Figure 4.
4. Find Update for Jet 4.0 Service Pack 8 (KB829558) and click Add
5. Click the Review and Install updates link
6. Click Install Now
Figure 4 - Downloading Jet 4.0 Service Pack 8
When you have finished, the file c:\windows\system32\msjet40.dll should have a version property value of 4.0.8015.0 or later.
This upgrade will change the version of Jet 4 that you are using for Access 2000, 2002, 2003. Once you have completed a trial period with this upgrade, you should start informing your customers that they too should upgrade. If your clients are using Access as a client to Oracle, the notes on the service pack indicate that it is important that you upgrade to this service pack as it fixes a number of critical bugs.
What does the Sandbox Try To Protect?
Sandbox mode is designed to protect your computer and databases against database objects that are designed with a malicious intent. It does this by stopping unsafe expressions being executed from one of the following
· From a query
· The default property of a control
· The on event line of a control embedded in a form or report
Any of these actions can be fired in a database without a VBA Project and because of this, macro detection and macro signatures will have no effect as they only kick in when a database has VBA code. For example, a query that that could do some damage is illustrated by the following SQL. In this sample, I show you how you can launch the calculator program from a query. If you try this with Access 97 or with the SandboxMode registry setting set to 2 or 0, this will launch the program quite happily. It doesn’t leave much to the imagination to think what other things you could do with this particular power.
SELECT Shell("c:\windows\System32\calc.exe") FROM Customers;
As you may expect, there are a number of other common VBA functions that are banned in this mode because they two could be used to ill effect and they include:
Command, CurDir, DeleteSetting, FileCopy, Kill, SendKeys, SetAttr, Shell, Spc, UserForms, Width
One of the other places that the jet sandbox testing comes into effect is when you have a special function encoded as an expression in the Event property boxes for any form, report or control on a form or report. If you store what Jet thinks is an illegal expression in this property, the expression will not work. Figure 5 shows two event properties that have illegal expressions.
Figure 5 - Illegal expressions that will be caught by the sandbox mode.
If you look at the On Enter event in figure 5, you will find that I have used an Access specific expression rather than a VBA expression. There are approximately 60 of these that are not allowed by sandbox testing including
AddToFavorites AutoCorrect CompactRepair CreateForm CreateReport DBEngine DDEExecute
DoCmd Echo ExportXML FileDialog FileSearch FollowHyperlink ImportXML InsertText
LanguageSettings LoadFromText LoadPicture Modules NewCurrentDatabase OpenCurrentDatabase RefreshDatabaseWindow RefreshTitleBar RunCommand SetDefaultWorkgroupFile SetHiddenAttribute SetOption SetUndoRecording SysCmd
Obviously if you undertake this practice for executing snippets of VBA code, you may be in for a rude shock. The solution in this case is simply to convert the code to VBA and there are many reasons why that is a good idea anyway.
By now you will be wondering what happens when the jet Sandbox testing encounters what could be an illegal expression? You will receive an error that says “The expression that you entered has a function name that Access cannot find”. The code then stops. If you have a unsafe expression in a query, you will find an error text string in your results. This sort of error reporting should concern you because many users will not report this type of error because the solution for them in most cases it is simply a matter of clicking OK to return to the normal interface.
Why should this concern you if you and your users are happily chugging along with Access 2000 or 2002? Simply because when Access 2003 is installed on a computer, it is highly likely that the sandbox registry item will be modified to the safer level of 3. At that stage, you will want to have removed the majority of these sandbox related issues from your database because your database may not work too well, just like my important database. Remember too that the number of errors is far greater if you are running an earlier version of Jet version 4. Now that I have found this out, my programmers and I now work with the sandbox registry item set to 3 at all times so that we will reduce the Access 2003 surprises at a later stage.
Where Does Access 2003 Fit Into The Picture
Whilst nothing in life can be guaranteed, at some stage soon you or one of your users/customers will be trying out one of your databases with Access 2003. When they do, you will need to be prepared with a solution for the issues that may lie ahead. Therefore as it is likely that this is going to happen to you, I thought I would first share with you what the Microsoft Access 2003 help tells you about the new version.
“If you are currently using Access 2002, migrating to Access 2003 is quick and easy. All you need to do is install and run Access 2003, and simply open your Access 2002 files! There is no need to back up and convert your files, because the file format did not change in Access 2003”. And for databases that have code, “You don’t have to do anything special to open a file that contains code, because the new version of Access is fully compatible with files created using Access 2002”. If only life was this easy!
When you install Access 2003 on a computer, it is very likely that you are going to encounter the form shown in Figure 6.
Figure 6 - The screen you encounter in Access 2003 when you don’t have the
latest Jet 4 Service Pack
This form is the very first thing that you or your users are likely to see when you open an Access 2003 database and its purpose is not obvious at first glance. I have looked at this form a number of time and I still have to concentrate hard to work out what I was meant to do. If you click No, you get the Royal boot and Access closes down. If you click Yes, you really must wonder if you are about to encounter some evil goblin. In fact, you already have encountered the evil goblin because your computer hasn’t been upgraded to Jet Service Pack 8. More confusion follows as the next form Access displays after this form is likely to be one of those macro protection forms (see figure 7) that you have probably seen when you open an Excel spreadsheet that has a VBA project. In this article, I am not going to rant and rave about the usefulness of these as a protection mechanism except to warn you that if you haven’t installed Jet Service Pack 8 on the computer, every time that you or your users change the macro security level in Access, the form shown before in Figure 6 will be returning to haunt you. Now you will understand why the Jet Service pack upgrade is important.
Figure 7 - Macro Security Warning When Opening a Database
Turning Off The Macro Security Warnings
Whilst I could now beat my chest and tell you that developers should all rush out and purchase security certificates from Verisign or Thwate for a few hundred dollars a year, I am going to take a more realistic approach that assumes that most developers and users are going to adopt the lower security setting because it is just easier and less costly. With Peters permission, another article will follow soon that delves into the mysterious world of security certificates and macro protection strategies for Access 2003 users. Anyway as the medium level security dialog is going to drive you nuts, you will no doubt find that you can turn off the macro security as follows
Go to the menu Tools ~ Macros ~ Security and the dialog box displayed in Figure 4 will be displayed. Select the Low Security Level on the macro security dialog and and all the prompts will disappear the next time that you open a database. Possibly though a macro virus will be run from your database.
What Happens When You Select Low Security
When you select the Low security setting, you would think that the complications for you (and your users) would be over. Unfortunately not, there is more. Immediately following that dialog is another tricky question about unsafe expressions (as shown in Figure 8). What this prompt is saying is that if you Click Yes, Sandbox mode will be turned off and if you click No it will be turned on. If I were a humble user, I would imagine that I would have no idea what this meant. The conclusion that you may draw from this is that you probably have no way of determining which sandbox mode setting your users are going to select so that you can prepare for both.
Figure 8 - Macro Security Levels
What Happens to the sandbox mode for the higher macro security settings?
If your users continue to use the medium or high macro security setting, your database will always be forced to use the SandboxMode setting of 3. As the medium setting is the default mode, you must prepare for this situation.
Useful Further Reading and Resources
Rick Dobson wrote a great article on macro security for Access 2003 in for Smart Access in April 2004
Information about Service Pack for Jet
http://support.microsoft.com/default.aspx?kbid=239114
Listings and discussions of unsafe VBA and Control Event Function
http://support.microsoft.com/default.aspx?kbid=294698
How to obtain the latest Jet Service pack
http://support.microsoft.com/default.aspx?kbid=239114
Information about the latest service pack
http://support.microsoft.com/?kbid=829558
Read about Microsoft Jet Expression Service sandbox mode
http://office.microsoft.com/assistance/preview.aspx?AssetID=HP010446591033
A page at my site covering things to be aware of when installing Access 2003
http://www.vb123.com/toolshed/03_access/install.htm
Find out how to extract the version of Jet your users are
running (and log to a file)
Go to this page
Conclusion
When Access 2003 rolls along onto your client or companies computers, you are going to look like a bit of a turkey if the best thing that you can recommend to these users is that they turn off all macro protection so that your software works. It is much better to make sure that your database works well by testing for sandbox mode at all times on your pc. You should also endeavor to ensure that all your key testers upgrade to the latest version of Jet 4 and use the sandbox protection as well to improve the coverage. Finally, you can reduce the complication of the Access 2003 rollout by ensuring that as many people as possible have upgraded their version of Jet 4 before they upgrade Access.
A different slant on these issues is a marketing one. If you are an independent Access developer, the simplest approach to Macro security in Access 2003 would be to tell your clients to adopt the lowest level of protection. In these days of constant computer security issues, being weak on security is not a good long term strategy. Wouldn’t it be a great thing instead to say to your customers that you are recommending that they adopt Sandbox protection for all versions of Access 2000 upwards as their first stage of Access macro protection? To do this, you need to ensure that they are using Jet Service Pack and have the sandbox registry item set so that unsafe expressions are protected on their computer. That is the approach that my company has taken both for development and end users for the time being.
Author Bio.
Garry Robinson runs GR-FX Pty Limited, a company based in Sydney,
Australia. If you want to keep up to date with the his latest postings on Access
Issues, visit his companies popular web site at http://www.vb123.com/ or sign
up for his Access email newsletter by sending a blank email to tips@vb123.com
The web site features many Access resource sand software that are used by more
than 10,000 readers a month. To find out about Garry’s book which is called
“Real World Microsoft Access Database Protection and Security”, point your
browser to http://www.vb123.com/map/. You can find Garry’s contact details at …
www.gr-fx.com
Other Pages On This Site You Might Like To Read
Cleaning Up An Access
Database
Microsoft Access Security
And Passwords
Restrict The People Who Can
Use Your Database Folder
An Access Global
Error Handler
Click on the following button
to jump to the next page in the document loop.
External Links On Topic
http://www.databasedev.co.uk/jet-sandbox-mode.html
Notes: Since article was published>Sandbox and Access 2007
Macro security information in the registry.
The security level can be changed for either
individual users or created for all users who log onto the computer. If you need
to set the security for an individual user, then you need to change this key:
\\HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Access\Security\Level
Change the value of this key to (1 for low security; 2 = medium security; 3 =
high security)Or for all users on the computer, you need to create this key:
If you need to set the same security level for all users who log onto the
computer instead, then you need to create this new Registry key:
\\HKEY_LOCAL_MACHINE\Software\Microsoft\Office\11.0\Access\Security\Level
Create the key of type DWORD and set the value to (1 for low security; 2 =
medium security; 3 = high security)
Our Tools and Resources
vb123 Professionals
Get Good Help Here
If you need help with a database or
Office programming,
our Professionals could be the answer because we have worked on many
similar solutions
Frontpage Conversions
We have converted vb123.com to Expression Web,
contact us if we can help you move to the latest Microsoft web tool.
About The Editor ~ Contact Us
Garry Robinson writes for a number
of popular computer magazines, is now a book author and has worked on
100+ Access databases. He is based in Sydney, Australia
Access 2003 Security
