Access 2003 Security Pass through Queries
-
We are using Access 2003 against SQL Server 2000.
Access 2003 enforces security so we are thinking of using signed code to minimise any user messages about macros.
Because we have a number of multi table views we need to use an mdb to get them editable. Because we are using an mdb we can't link reports to recordsets. If we use pass through queries it works. BUT whenever we edit the pass through queries to change parameters (in code) the database goes corrupt.
Technically what is happening is that the user is modifying a signed database and doesn't have the correct certificate so the certificate is discarded. This is what happens when you do it manually. When you do it in code, the database gets paranoid and the next time you open it, it notices that the "code" has been changed and gives an error message suggesting viruses an other nasties.
OK we can get round that by using unsigned code so the user gets a macro warning but we don't like that.
Is there any way to get round this (apart from the obvious one of downgrading to e.g. Access 2000) and to have signed code that allows you to change parameters. Or am I approaching this from the wrong end?
- John McCabe
- CMIS Manager
- East Norfolk Sixth Form College
- (England)
-
You can avoid the macro warning by lowering the security of the individual Access 2003 projects.
Go to the menu bar:
Tools>Macro>Security>Low
May not be the answer you are looking for, but it works.
-
Quite agree. Trouble is the users. They have enough trouble clicking on a program icon.
Seriously, we are looking a situation in a College where we need a reasonably managed network. We install Office 2003 from the network with a GPO and have set all the bits about security there. We could use another policy template to manage security for users but prefer to have security reasonably tight.
What I feel is that this is a classic Micros**t gotcha. You want security. You want transparency for the users. But it fouls up in the middle because it can't (apparently) cope with setting parameters in pass through queries.
-
Part of the problem here is trying to have it both ways: you want a signed application, but you want to preserve the ability alter the SQL of a pass-through query. Doing this, you open up your data to any sort of attack that a clever enough hacker could manage to mount by doing the same thing himself. If that's possible, what's the point of the signature except to provide a false sense of security?
If you want to have a signed application, and still be able to alter pass-through SQL, maybe you could move the pass-through queries to an external mdb and process them outside the signed mdb. I post the details below. I'd love to hear your result.
I don't know for a fact that DBEngine will ignore changing the SQL in the external query. It's just the only way I can think of to attempt what you're after. If it works, let us know.
Create an external query repository.
Move the PTQs over there.
From your application, alter the PTQ SQL in the repository.
Use the PTQ in the repository as the report recordsource.
In more detail, using c:\MyDirectory, MyApplication.MDB, and MyExternalQueries.MDB:
In c:\MyDirectory create MyExternalQueries.MDB
Move the query (e.g. MyPassThroughQuery) over there.
MyExternalQueries.MDB cannot be a signed application for the now-obvious reason that if you sign it and then alter the SQL you lose the signature.
In your application (e.g. c:\MyDirectory\MyApplication.mdb) use this SQL for your report recordsource:
Select * From [c:\MyDirectory\MyExternalQueries.mdb].MyPassThroughQuery
If you get the SQL for your report recordsource correct, you will be able to work with the report in design view as you would normally.
If MyPassThroughQuery has a valid connect string and returns records, you can run the report as it stands and you will get whatever records come back through the external PTQ located in MyExternalQueries.MDB.
For the situation you describe, which is the only reason to create an external query repository such as this, you need to alter the SQL of that external query, which is permitted -- over there, in the unsigned MyExternalQueries.MDB.
I wrote the function below to do that for the simple case in which MyPassThroughQuery requires only one parameter. Adding more is trivial. Cleanup and error-checking are omitted for simplicity.
Public Function writeqdsql(UserName As String) As Boolean
Dim db As DAO.Database
Dim qd As DAO.QueryDef
writeqdsql=false
Set db = DBEngine.OpenDatabase("c:\MyDirectory\MyExternalQueries.MDB")
Set qd = db.QueryDefs("MyPassThroughQuery")
qd.SQL = "Select * From tblUser Where UserName ='" & UserName & "';"
writeqdsql=true
End Function
Now you're set to call the function, which sets the sql of the external query, and then run the report. A typical way of doing this would be to have some form offer one or more controls that collect the criteria values you need, and use the _Click event of cmdRunMyReport to call the function to write the sql.
You can of course just put the code from the function in-line in your _Click event if you won't call it from anywhere else.
HTH
CRW
Houston
-
Thanks immensely!!
Worked a treat. No major hiccups at all.
As to the question of security, I'm not (directly) worried about security for this application. It's more a question of user interface. What I wanted was a transparent move up from Access 2000 where you don't get messages about code in applications through to 2003 where you do.
Knowing our users, I didn't want to either a) lower security levels to low - too many risks there or b) encourage them to blindly click through the macro warning - too much risk of them avoiding a valid warning. All I wanted was a way of saying this is an internally trusted piece of software, please turn off any warnings to the user and let it run.
Using digital certificates to do this should be safe and I'm not too worried about SQL injection and other tricks. Most of our users have enough trouble turning their computer on
Seriously, this is just a question of saying this code is trusted by us, please let it run without any warnings.
Thanks again for all the help.
-
cant you just not use macros?
use code instead?
martin
-
The security features are accessed through Tools .. Macros.. Security. It is normal therefore to refer to this as the Macro warning even though it refers to code.
I have one or two macros but the vast majority of the progarm is done in code including class modules. After all, without any code, I couldn't digitally sign my code
-
good man
i think macros should be banned.
*shivers in horror at some of the access systems hes had to debug someone else has written with spagetti macro's only!*
martin
-
Thanks for letting me know the outcome.
I have a question for you, on your success with digital signatures.
We have a certificate. We signed our application. We put it on the network. Uh-oh. Users don't have that certificate installed on their workstations, and they are not (by corporate policy) administrators, and don't appear to be able to install the certificates. I thought IT should be able to push the cert out through group policy, but for some reason that hasn't been successful.
I wound up (get ready to laugh) having to write a vbscript that created an Acces object and set the security setting low and then proceeded to open the application.
Have you been successful with your signed Access application? If so, what are we missing?
Rick W
-
Yes we have been successful but it took quite a bit of work and forward planning. We knew that we would need to move up to Access 2003 for our work so....
We created a local certficate server, issued our own certificate and then signed the code. What we did was to roll out Office 2003 using group policy with the certificate in the transform. We slos had a separate group policy to trust the certificate server. Total cludge but it worked.
Talking to our IT people, it appears that Microsoft, as ever, have got it a bit pear shaped.
The "classic" way to do it is to have a local "root" (or master) certificate server which appears in Active directory. You can then manage the beast via Active Directory. This has all sorts of management implications to keep it working properly (and securely).
If you don't do it that way, then you need to first of all push out the certificate server via a GPO and then somehow add your certificate to the list of trusted publishers. Most of this is in fact managed by Internet Explorer (don't ask!) which under the content part of the Internet tab has a Certificates section. I gather from our IT people that the way to manage this is to use the Internet Explorer Administration kit and create a custom installation and manage certificates that way.
This is one of those areas where Microsoft have got it badly wrong. They have enforced security settings in Office but not provided the tools in Active Directory to manage it. Not only that, they have created a range of user interface problems as a result.
-
jfmccabe,
That is a very interseting and insightful discussion of signed code and management in the Microsoft environment.
Isn't the problem that you are using the wrong tool for the job. Access is a great adhoc reporter, reporting engine and robust platform for certain types of tools; but Access as a secure front end for a many usered application is not the right way to go. Now or in the past. Again, I'm not bashing you. I've written a lot of Access and your and the other posters responces are technically right on.
-
Very possibly. I agree about Access's faults but I haven't the skills to go fully over to .net framework (yet)
What may be obscuring things is the question of signed code and security.
We like Access. I've been using it since 1993 (Access 2.0) I've taught computing courses in Access all the way up to Access 2000. I've now moved over into College Admin. out original MIS system - still in use
- is written in Access 2.0 We now need something a bit bigger hence the need to move to SQL Server.What we really like, as you point out, is the reporting engine. We know it and can drive it (to distraction).
We don't need (much) security. However, to get the best out of SQL Server, we've found it best to use Access 2003. Access 2003 enforces the security model. Fine. What we want is transparency. We don't like all the macro warnings and want to turn them off without risking too much. We don't want a secured front end, we want a front end like we've always had. In fact, I'm taking a lot of forms straight up from Access 2.0 to Access 2003 so what the users get is what they've always had only it works better.
The other thing is that our MIS was always a consortium arrangement - several colleges collaborated to do things. So in some ways we are limited by the skills set of the weaker players. Again, no problem. We've all been running Access systems for several years and have a good understanding of what we can do in Access.
-
Again, tip top answer. Consider Reporting Services and it's web reporting. If you have the skill to script Access the way you seem to, then creating Reports and displaying them on the web internally won't be a difficult transition. I like the book Professional SQL Server Reporting Services, Wrox press. Combine this with DTS and you have a great architexture
-
you could also keep using access, but start moving over functionality and application logic directly onto SQL server itself.
That way you can lock the data down at source, rather than locking the data down at the external application layer. You only give your Access access
to what you want it to have.martin
-
This is what my shop does.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply