J
John W. Vinson
A misphrasing on my part: it's just that the procedures needed to do security
correctly on SQL (any version) are nontrivial and require a good bit of study.
A misphrasing on my part: it's just that the procedures needed to do security
correctly on SQL (any version) are nontrivial and require a good bit of study.
A
Armen Stein
It wouldn't necessarily. It would be best installed on a server of
course, if one is available.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
A
Armen Stein
Sorry, I didn't phrase that very well.
How about this?
Any version of SQL Server, including the free Express version, can be
made much more secure than Access if configured properly.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
A
Armen Stein
Sorry, that was unclear wording on my part. I meant that any version
of SQL Server (including Express) can be made more secure than Access.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
A
Armen Stein
That might have been true of MSDE in previous versions - I didn't have
much familiarity with that. But now SQL Server Express is a
separately-installed (free downloadable) product.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
L
.Len B
<snip>
| Phew, thanks guys. Are you trying to scare me? It's working.
|
| It looks like I've got a bit of reading to do.
|
| The domain runs on SBS2003 and I think I've seen a SQL server
| there; not sure of version but I think it keeps some monitoring
| data for SBS itself.
|
| Nevertheless, I'm still inclined to mdb because
| * the budget for man hours and
| * the fact that, as John said "Locks keep out honest people"
| and I think that it is 'honest' people I'm dealing with.
|
| Authorised users wouldn't really want to hack in for curiosity
| sake but if the door was open they might peek. OTOH, a hacker
| would first have to logon to the domain as a user with access to
| the FE folder and also know a password for the db or at least
| that the db is there and is juicy enough to be a target.
| Am I being naive here?
|
| The jewels being protected are medical records of profoundly
| disabled children - not an attractive target I think, at least
| not like credit card or financial info. Users are medical or
| admin staff. The organization is a 'not for profit' and the
| budget is always tight.
|
| Thanks for the advice and the links. I'll take a deep breath and
| follow them.
|
| Thanks once again Armen, John and Pete.
| --
| Len
| ______________________________________________________
| remove nothing for valid email address.
|
Well, I've downloaded the FAQ and printed it. I went to Armen's site
to get the items he suggested and ended up with some extras as well.
So I've begun my reading and so far I've discovered that I'll need
to do more reading.
(BTW, Armen, loved the concept of running the Selection Form from
the Report rather than the other way around. Brilliant out-of-the-
square thinking.)
Here in Australia HIPAA doesn't apply but we do have other laws
mandating similar matters and I think you're right that I check
the legal requirements. More reading!
Also, back when this WAN was established, there wasn't non-profit
pricing but there was a gift of 10 XP Pro from MS. The version of
SQL server is 2005.
In the FAQ so far I got the impression that the Workgroup Information
File which gets created 'attaches' to Access rather than to the database
file. To me this implies that all databases on the system will 'use' it.
That doesn't seem right so I guess I'll find out more as I read. All I
can figure is that there must be a UseSecurity(y/n) property set in the
databases themselves.
Many many thanks to everyone for their input.
| Phew, thanks guys. Are you trying to scare me? It's working.
|
| It looks like I've got a bit of reading to do.
|
| The domain runs on SBS2003 and I think I've seen a SQL server
| there; not sure of version but I think it keeps some monitoring
| data for SBS itself.
|
| Nevertheless, I'm still inclined to mdb because
| * the budget for man hours and
| * the fact that, as John said "Locks keep out honest people"
| and I think that it is 'honest' people I'm dealing with.
|
| Authorised users wouldn't really want to hack in for curiosity
| sake but if the door was open they might peek. OTOH, a hacker
| would first have to logon to the domain as a user with access to
| the FE folder and also know a password for the db or at least
| that the db is there and is juicy enough to be a target.
| Am I being naive here?
|
| The jewels being protected are medical records of profoundly
| disabled children - not an attractive target I think, at least
| not like credit card or financial info. Users are medical or
| admin staff. The organization is a 'not for profit' and the
| budget is always tight.
|
| Thanks for the advice and the links. I'll take a deep breath and
| follow them.
|
| Thanks once again Armen, John and Pete.
| --
| Len
| ______________________________________________________
| remove nothing for valid email address.
|
Well, I've downloaded the FAQ and printed it. I went to Armen's site
to get the items he suggested and ended up with some extras as well.
So I've begun my reading and so far I've discovered that I'll need
to do more reading.
(BTW, Armen, loved the concept of running the Selection Form from
the Report rather than the other way around. Brilliant out-of-the-
square thinking.)
Here in Australia HIPAA doesn't apply but we do have other laws
mandating similar matters and I think you're right that I check
the legal requirements. More reading!
Also, back when this WAN was established, there wasn't non-profit
pricing but there was a gift of 10 XP Pro from MS. The version of
SQL server is 2005.
In the FAQ so far I got the impression that the Workgroup Information
File which gets created 'attaches' to Access rather than to the database
file. To me this implies that all databases on the system will 'use' it.
That doesn't seem right so I guess I'll find out more as I read. All I
can figure is that there must be a UseSecurity(y/n) property set in the
databases themselves.
Many many thanks to everyone for their input.
D
Douglas J. Steele
Armen Stein said:
True, but don't you typically install it on a workstation, not a server?
P
(PeteCresswell)
Per .Len B:
It's settable via the command line when you fire up MS Access. I
forget the syntax, but it is there and you can have a different
security file every time you start MS Access.
It's settable via the command line when you fire up MS Access. I
forget the syntax, but it is there and you can have a different
security file every time you start MS Access.
A
Armen Stein
Hi Doug,
It doesn't matter. It can be installed on either one. If you have a
Windows server, but not SQL Server, then SQL Server Express is a great
way to get up and running with no licensing cost.
I wonder if you're thinking about Compact Edition? That's a one-user
version that would be installed on a workstation.
Version comparison:
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
D
David W. Fenton
The apps I've upsized to SQL Server required almost no sprocs.
Maybe you're going overboard in converting things to sprocs that
don't really need to be?
D
David W. Fenton
Quite generous of you to take credit for a misphrasing posted by
Armen!
D
David W. Fenton
Are you thinking in terms of Access 2000-era MSDN? We've been well
past that stage for almost half a decade!
D
David W. Fenton
Why would you do that? You install it on a server and use it like
you would full SQL Server. You could do that with MSDE, too, but it
had more limitations, if I'm not mistaken, and a trickier
installation.
D
David W. Fenton
SQL Server CE is not really SQL Server at all, but a completely
different db engine designed for compatibility with SQL Server
programming. But it lacks tons of features that real SQL Server has.
It even lacks a lot of things that Jet/ACE has had forever!
I think the relationship between SQL Server and SQL Server CE is a
lot like the relationship between Outlook and Outlook Express -- not
much under the hood, some superificial resemblances, and that name
in common.
Doesn't mean it's not a good db engine, just that you shouldn't
expect it to be like SQL Server in any significant way.
D
David W. Fenton
Er, what? It's the standard approach for anyone who wants their
filtering forms to be usable in multiple locations -- the filtering
dialog itself knows nothing about the context in which it's called.
This is good design, and lots of us have been using it for a very,
very long time (lessons learned from trying to maintain filtering
dialogs that had to know too much about the reports/forms they were
filtering).
[]
The workgroup file applies to a Jet workspace. By default if not
told otherwise, Access will use the workgroup file set as the
default with the workgroup administrator. But you can open your
instance of Access with the appropriate commandline arguments and
designate any accessible workgroup file for that session. You can't
change workgroup files within a session, but you can open other
workspaces that use different workgroups.
P
(PeteCresswell)
Per David W. Fenton:
I guess "require" is a judgment call. More-or-less on faith
(after reading a few write ups on the subject) I decided to move
all of the processing I could back to the server.
I'm pretty sure I went further than most would - including
Adds/Changes/Deletes using #Temp tables, and single streams
combining multiple tables when a screen used more than one table.
"Overboard" is a stronger word than I would use, but definitely
"further"... OTOH, part of my approach was informed by a
couple of conversions I did where straight table
conversion/one-for-one ODBC connections and no coding at all on
the server side as the mandate. In those two cases, performance
went down the tubes in a beeeeeg way.
OTOOH, I have no data to discriminate between the effects of the
ODBC/One-for-one/No SP approach and the possibility that the
real problem was I had no control over the computer being used as
the server.
To wit, I've had several experiences to date where something that
was running a-ok response-wise went down the tubes when IT moved
it to a virtual machine in some humongous mother machine.
My impression is that even the experts in IT are not fully in
control of a given app's performance/resources once it's in such
an environment. The control may *be* here, but I've seen some
pretty powerful people lean on those guys and not get any
improvements. This is as opposed to having the back end
deployed on a physical machine that we can throw money at until
it's fast enough to suit the users.
One such application was a straight-up .MDB back end where
performance increased significantly just after moving to a VM
Ware-based Citrix server, but gradually degraded over time
(compact/repairs did not help) with the same number of concurrent
users to where it was significantly worse that the original
file-server-based deployment.
Another was a .NET rewrite of a bond trading system (rewrite done
by somebody else). Running against the test server (Express on
somebody's laptop in the same building), it was super-fast,
blowing my MS Access version away performance-wise. But after
deployment to production, people on the trading desk were longing
for my app, saying that the new one was such a dog as to be
almost unusable.
My (totally uninformed) guess is that as a VM Ware mother box
gets loaded up with more and more applications and virtual
machines, our share of MIPS and/or memory decreases.
I guess "require" is a judgment call. More-or-less on faith
(after reading a few write ups on the subject) I decided to move
all of the processing I could back to the server.
I'm pretty sure I went further than most would - including
Adds/Changes/Deletes using #Temp tables, and single streams
combining multiple tables when a screen used more than one table.
"Overboard" is a stronger word than I would use, but definitely
"further"... OTOH, part of my approach was informed by a
couple of conversions I did where straight table
conversion/one-for-one ODBC connections and no coding at all on
the server side as the mandate. In those two cases, performance
went down the tubes in a beeeeeg way.
OTOOH, I have no data to discriminate between the effects of the
ODBC/One-for-one/No SP approach and the possibility that the
real problem was I had no control over the computer being used as
the server.
To wit, I've had several experiences to date where something that
was running a-ok response-wise went down the tubes when IT moved
it to a virtual machine in some humongous mother machine.
My impression is that even the experts in IT are not fully in
control of a given app's performance/resources once it's in such
an environment. The control may *be* here, but I've seen some
pretty powerful people lean on those guys and not get any
improvements. This is as opposed to having the back end
deployed on a physical machine that we can throw money at until
it's fast enough to suit the users.
One such application was a straight-up .MDB back end where
performance increased significantly just after moving to a VM
Ware-based Citrix server, but gradually degraded over time
(compact/repairs did not help) with the same number of concurrent
users to where it was significantly worse that the original
file-server-based deployment.
Another was a .NET rewrite of a bond trading system (rewrite done
by somebody else). Running against the test server (Express on
somebody's laptop in the same building), it was super-fast,
blowing my MS Access version away performance-wise. But after
deployment to production, people on the trading desk were longing
for my app, saying that the new one was such a dog as to be
almost unusable.
My (totally uninformed) guess is that as a VM Ware mother box
gets loaded up with more and more applications and virtual
machines, our share of MIPS and/or memory decreases.
A
Armen Stein
Quite generous of you to take credit for a misphrasing posted by
Armen!
That's right, if anyone is going to take credit for my misphrasing,
it's going to be ME!
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
A
Armen Stein
Lots of us, perhaps, but the approach isn't as universal as it should
be. I still see a lot of systems where criteria forms call reports.
And when I mention it in presentations, I still get a few "aha"
moments (or sometimes even pushback) from attendees. So I'm going to
keep recommending it!
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
D
David W. Fenton
[interesting experiences with virtual servers deleted]
In cases where the server is being underprovisioned (by design?), as
you describe, yes, you get benefit from wringing all the performance
out of the server as you can.
On the other hand, my experience with non-virtualized SQL Servers
(none of them dedicated boxes, BTW) is that a well-designed Access
app upsizes quite well, and there are only a handful of bottlenecks
that need to be addressed by moving something serverside. I have
noticed the most bang for the buck in moving outer joins to
server-side views, because Jet is apparently not efficient in
handing that off to the server. I had one routine that I thought
would need an sproc, but it worked just fine with plain old DAO code
retrieving summary data (with the Access app, it had been so slow
that I was caching a recordset with the calculations done; the SQL
Server made it fast enough to just request the calculations
on-the-fly).
But since about 1998, I've been designing all my Access apps with
the idea that they might one day need to be upsized to SQL Server.
With that in mind, I do operations in a way that is going to be
efficient with a server, but the great result is that it also makes
the Access app more efficient.
So, obviously, YMMV.
But the difference between the two of us may simply be that I
haven't been in the situation where I was forced by the
infrastructure to wring all the performance out of the SQL Server as
possible.
And I'm not even sure I'm smart enough to do it. If you follow SQL
Profiler with plain vanilla ODBC, you'll see that with a few
exceptions (the most obvious being bulk inserts/updates), Jet is
very smart with what it sends to SQL Server. Almost everything gets
executed by a generic sproc with parameters, so it gets properly
compiled even though it's not server-side.
I was stunned by all of this the first time I upsized, since I
expected my junky old Access app to be completely inefficient. It
wasn't, and SQL Profiler told me what needed to be moved
server-side. It was substantially less than what I expected, and
also ended up not being the things I *assumed* would need to be
moved. Indeed, the biggest problem I had was with tasks that I
assumed were not going to be an issue at all, while the stuff I
thought was going to be a dog ran just fine.
Go figure.
In cases where the server is being underprovisioned (by design?), as
you describe, yes, you get benefit from wringing all the performance
out of the server as you can.
On the other hand, my experience with non-virtualized SQL Servers
(none of them dedicated boxes, BTW) is that a well-designed Access
app upsizes quite well, and there are only a handful of bottlenecks
that need to be addressed by moving something serverside. I have
noticed the most bang for the buck in moving outer joins to
server-side views, because Jet is apparently not efficient in
handing that off to the server. I had one routine that I thought
would need an sproc, but it worked just fine with plain old DAO code
retrieving summary data (with the Access app, it had been so slow
that I was caching a recordset with the calculations done; the SQL
Server made it fast enough to just request the calculations
on-the-fly).
But since about 1998, I've been designing all my Access apps with
the idea that they might one day need to be upsized to SQL Server.
With that in mind, I do operations in a way that is going to be
efficient with a server, but the great result is that it also makes
the Access app more efficient.
So, obviously, YMMV.
But the difference between the two of us may simply be that I
haven't been in the situation where I was forced by the
infrastructure to wring all the performance out of the SQL Server as
possible.
And I'm not even sure I'm smart enough to do it. If you follow SQL
Profiler with plain vanilla ODBC, you'll see that with a few
exceptions (the most obvious being bulk inserts/updates), Jet is
very smart with what it sends to SQL Server. Almost everything gets
executed by a generic sproc with parameters, so it gets properly
compiled even though it's not server-side.
I was stunned by all of this the first time I upsized, since I
expected my junky old Access app to be completely inefficient. It
wasn't, and SQL Profiler told me what needed to be moved
server-side. It was substantially less than what I expected, and
also ended up not being the things I *assumed* would need to be
moved. Indeed, the biggest problem I had was with tasks that I
assumed were not going to be an issue at all, while the stuff I
thought was going to be a dog ran just fine.
Go figure.
D
David W. Fenton
I don't understand why it wouldn't be obvious. My early apps had the
forms hardwired to the reports, but then I needed to re-use them in
multiple locations, so I ended up needing to pass OpenArgs and had a
lot of branching code. Eventually it became obvious that the filter
form should be as dumb as possible and all the specific could should
be in the report/form it was specific to. I would think this would
be obvious to anyone who is building an app that gets complicated
enough that you find yourself tempted to build two forms that do the
same thing but need to be used in different contexts. To me, that's
obvious that I need to generalize the forms such that it can be used
in both contexts since that means easier maintenance.
I didn't read it in a book or hear it in a newsgroup -- the apps
themselves taught it to me!