Upsizing Process

[C. SHaner]

I have an existing MS Access database that has grown beyond reasonable Access
limits. We decided to move the data to a MS SQL Server, keeping the Access
frontend.

I “upsized†the Access database using the Upsizing Wizard. This worked great
for moving the data to a MS SQL Server.

On the SQL Server I created a user account and gave it dbo role for the
database I moved to SQL server.

On the workstation I created an ODBC connection to the database using SQL
authentication.

I linked the tables from the SQL server. So far all is well with the world.

I wanted to add additional security. So I proceded with what I know how to
do, that is I created an Access Workgroup file (mdw). I then created a copy
of the database so that I could do some testing. On one I used the security
file and the other is without.

When I open the “secured†database I get the standard Access login box and
the “Startup†form runs. The other just opens with my “Startup†form. When I
open the main data entry form, with the SQL Server datasource, in the
“unsecured†database it opens normally. When I do the same in the “Securedâ€
database I get a SQL Server login box. The question is why the different
behavior? My intention was to use the Access Workgroup file to assign
permissions to objects in the database.

Since this is my first attempt at this process I question if I have gone
down a wrong path??? Any suggestions?

 

[JLCan]

I am doing the same thing! We are using different routes but I have
encountered almost the same problems - even worst: different behaviour on
different PC. My understanding of this problem, from many sources (even
MicroSoft), is that Access 2007 does not 'realy' use the workgroups but ...
it does. I plan to rest uniquely on SQL for security and possibly (it will
certainly work differently from what I expect) a little help via submenus
restrictions.

 

[C. SHaner]

I am using ACC2003. There must be some issues with using "Workgroup" security
versus the native SQL server security. I would really like to control access
to database objects at the "frontend". It would be my observation that
implementing database object security on the server side could be very
complex, but I may be very wrong as I am a novice to using SQL server as my
database.

 

[Norman Yuan]

You do not/can not use Access user level security (workgroup file) for SQL
Server back end. You need to use SQL Server built-in security (Login, User,
Role, permissions on server objects, such as table, view, SP, UDF...).

 

[C. SHaner]

Thanks, that is what I feared. Do you have a "favorite" author or book that
explains the proper way to provide security in a split Access/SQL environment?

 

[Rick Brandt]

Thanks, that is what I feared. Do you have a "favorite" author or book that
explains the proper way to provide security in a split Access/SQL environment?

Basically you set up Access security to control access to objects in the front
end and separately you set up security on the SQL Server objects without any
consideration of the Access objects in the front end that use them. These are
two completely separate processes.

The major issue with security on the server is that you can make zero
assumptions that those objects will only be accessed by your Access front end.
You have to protect them from any and all possible tools that can connect to a
SQL Server. Your App being just one of those possible tools.

This is actually true of ANY split app even when the back end is another MDB
file. Back end security is back end security and front end security is an
entirely different thing. It's just that with an All-Access scenario you are
using the same security tools.

 

[C. SHaner]

Rick, thanks for the reply. I am somewhat confused.

An earlier reply said I can’t use Access Security with SQL server backend.
As you stated I could use Access Security to control the objects in the
“frontendâ€, which is exactly what I wanted to do. My original question
remains: Why when Access Security is implemented, and I am using a ODBC
connection defined on the workstation, does a SQL Server Login Box pop up
when I display a form that has data from the SQL server? Without using an
“mdw†file the forms displays and no “login†box is displayed, but I don’t
want an ‘unsecured†application.

 

[Norman Yuan]

What do you actually want to secure? I assume it is the data in SQL Server
database. That is, you want certain users access certain data (read only, or
read/write). That is most database application needs on the regard of
security. I do not see why you need to secure the front-end, which is just a
program (be it Access front end or front end written in other language, such
as VB).

Whether a login dialog pops up or not, depends on your design of the front
app. With SQL Server, if you use Windows Authentication, then there is no
need to ask log in. You can control users' accessiblilty based on user's
Windows user account or Windows domain security group.

 

[C. SHaner]

I am not using Windows Authentication. In my original post I explained that I
have one account on the server side that has "dbo" rights. I am using an ODBC
connection, defined on the machine and "pointing" to the afore mentioned
account. This ODBC connection is using SQL authentication. I also use this
ODBC connection in Access to "link" the tables from the SQL server to my
Access application.

I have looked at several major application we use here and even our ERP
system uses the client application to provide security and user permissions.
These applications connect to a "dbo" account. In the past I have used an
Access Security file to assign permissions to the database objects and I
expected the same results whether the data is an Access database, SQL Server
or for that matter an Oracle database

I have discovered several items that seems to indicate that when you use an
Access Security file that Access tries to pass the user name and password
entered in the default Access login box to SQL Server. I found some
"work-aounds" mentioned abour placing entries into the registry for
TryJetAuth key. This did not fix it

 

[Rick Brandt]

I am not using Windows Authentication. In my original post I
explained that I have one account on the server side that has "dbo"
rights. I am using an ODBC connection, defined on the machine and
"pointing" to the afore mentioned account.

REALLY bad idea.

This ODBC connection is
using SQL authentication. I also use this ODBC connection in Access
to "link" the tables from the SQL server to my Access application.

I have looked at several major application we use here and even our
ERP system uses the client application to provide security and user
permissions. These applications connect to a "dbo" account.

Doesn't matter how many apps you have that do this. It is a REALLY bad
idea.

In the
past I have used an Access Security file to assign permissions to the
database objects and I expected the same results whether the data is
an Access database, SQL Server or for that matter an Oracle database

Access security can be hacked for a few dollars. By using a DBO account you
have effectively reduced your SQL Server security so that it is no harder to
crack than Access' is. Did I mention that this is a REALLY bad idea?

I have discovered several items that seems to indicate that when you
use an Access Security file that Access tries to pass the user name
and password entered in the default Access login box to SQL Server.

Incorrect. A person might create an Access app with code that tries to do
that, but Access out-of-the-box would never do that.

I
found some "work-aounds" mentioned abour placing entries into the
registry for TryJetAuth key. This did not fix it

Why not just use trusted connections and set up the security on the server?
Then you get all the security you need and no one has to log into your
Access app.

 

[Norman Yuan]

Even not using Windows authentication in SQL Server, you can still take
advantage of SQL Server built-in security. You just need to create different
logins and map them to diferent users/role in the targeting database, then a
assign permissions accordingly.

If you create a single login and make it dbo of targeting database, then you
give up the security provided by SQL Server, and re-invent the wheel on your
own. That is fine if your application requires that and you can afford to
the investment of "re-inventing". Unless you have the time and the team (do
not even think of doing that if you are the only developer) to build all
user access control into your front-end, it is best to use SQL Server
built-in security, whether you use Windows authenticatio or not.

Since the SQL Server is on back end, no matter how secure your home-grown
security on your front end, if the user knows the almighty dbo user
namne/password, he have so many ways to connect himslef to the backend
database without using your front end and do any thing he wants (he owns the
database!). In this case, you re-invented a wheel, but it does not roll into
the direction as you want it to, your database is on the loose.

So, if you use SQL Server (or any other database server, for this matter) as
back end, use its built-in security ti secure it. Do not get its door wide
open and rely on front end to secure it, unless the front end is the only
door to it (even so, if the db server has security built-in, why not using
it?).