Pass-thru SQL queries - Advice

[MMcCarthy]

Hi everyone,

I have to advise a new client regarding backending some access databases for security reasons. As the issue is one of security I'm assuming they won't want to just link the tables into the front end. I know I can use ADO to make a lot of the required connections but I am considering using Pass-thru SQL queries for some issues.

However, I know there are problems with their use and I'm just wondering what they are. Any other advise regarding backending an access database to improve security are welcome.

Mary

[dima69]

Hi everyone,

I have to advise a new client regarding backending some access databases for security reasons. As the issue is one of security I'm assuming they won't want to just link the tables into the front end. I know I can use ADO to make a lot of the required connections but I am considering using Pass-thru SQL queries for some issues.

However, I know there are problems with their use and I'm just wondering what they are. Any other advise regarding backending an access database to improve security are welcome.

Mary

Mary,
I am not an expert in security issue, but I want to be :)
Could you explain why linking the tables from the BE is less secure than the other things ?

[MMcCarthy]

Mary,
I am not an expert in security issue, but I want to be :)
Could you explain why linking the tables from the BE is less secure than the other things ?

If you link the tables in the backend they can still be imported to another database or at the very least linked to.

[dima69]

If you link the tables in the backend they can still be imported to another database or at the very least linked to.

Thanks for the clarification. It would be interesting to see the evolution of this thread.

[FishVal]

Hi, Mary.

Do you mean MSSQL or some other non-Jet BE?

P.S. Just subscribing to the thread.

[MMcCarthy]

Hi, Mary.

Do you mean MSSQL or some other non-Jet BE?

P.S. Just subscribing to the thread.

For the moment until I hear otherwise I'm operating as if its MS SQL

[puppydogbuddy]

Hi, Mary.

Do you mean MSSQL or some other non-Jet BE?

P.S. Just subscribing to the thread.

Hi Mary,
Thought you would be interested in these comments by www.aadconsulting.com about issues to consider if you are running Access on a network. Note that pass-thrus are one of the issues, but there others you should consider as well.

Access Databases on a Network

Generally, Access can handle up to 50 concurrent users over a network without any trouble, but there are other issues to consider:

Does the server and do the client PCs have enough grunt?
Is LAN broadband sufficient?
How big is the database?
How is it split?
Keep administrative functions in the back-end
Locate all lookup tables and those with static data in the front-end
How is the back-end queried? Are you using ODBC, JET, DAO, or ADO etc...?
Don't open tables across the network. Locate back-end queries in the back-end
Open queries with the minimum recordset required. Use Snapshots where possible
What RecordLocking regime is in place ...optimistic, pessimistic... ?
Your level of experience with and knowledge of Client/Server databases. The Chapter Building Client/Server Applications in the Access documentation, Building Applications with Microsoft Access, is a good starting point.

[MMcCarthy]

Thanks pdog,

I'll have a look at that tonight. I have to allot some time to read all the code solutions and white papers on these sites. I used to know them well but have gotten out of the habit.

Mary

[puppydogbuddy]

Hi again Mary,

Here is another must have link I had in my files: it's an excellent tutorial about how to implement pass-thru queries. And It mentions the only reservation that I have heard about correctly implemented pass-thrus.....it is not really a reservation, but just something you need to be consciously aware of up front:

Removing interaction with one layer (the Jet Engine) between the client and server should improve performance. However, the syntax for the queries will be SQL specific and does not support the Jet syntax anymore.

http://www.aspfree.com/c/a/Microsoft...-in-MS-Access/

[Jim Doherty]

Hi again Mary,

Here is another must have link I had in my files: it's an excellent tutorial about how to implement pass-thru queries. And It mentions the only reservation that I have heard about correctly implemented pass-thrus.....it is not really a reservation, but just something you need to be consciously aware of up front:

Removing interaction with one layer (the Jet Engine) between the client and server should improve performance. However, the syntax for the queries will be SQL specific and does not support the Jet syntax anymore.

http://www.aspfree.com/c/a/Microsoft...-in-MS-Access/

Just a quick FWIW from me Mary.... depending on the clients intentions have had considerable success using ADP format (thin client approach) using either MS SQL servers own applcation roles or Windows integrated security authentication, with users assigned to specific roles, preset permissions and so on. JET on the client is bypassed mostly but you can always call on that locally for whatever reason if so needed (personally I have never needed to in this scenario but you have the best of both worlds.

Security on the server is of course as good as you wish to make it. (had MD5 encryption applied in various instances to usernames and strings ON the server so that only the security cleared few can understand visually what is underlying, together with encrypted stored procedures etc etc

From a security perspective its as robust as your weakest link of course ie: the careless who might leave the server exposed to SQL injection attacks etc or even worse leaving the default login account as SA with a blank password. Sounds silly but how many servers are out there with that setup being pinged left right and centre.

Whilst I mention using ADP format it does not exclude MDB obviously you can implement that via the pass through route and MDB is currently being perceived as the ipso facto if you like for an Access 2007 interface with SQL Server. (whether that means a dumping of the ADP in due course like Data Access Pages who knows? except Microsoft, and unless anyone else has, I have seen nothing that warrants any fear that they may be so inclined to do so)

Regards

Jim

[MMcCarthy]

Hi again Mary,

Here is another must have link I had in my files: it's an excellent tutorial about how to implement pass-thru queries. And It mentions the only reservation that I have heard about correctly implemented pass-thrus.....it is not really a reservation, but just something you need to be consciously aware of up front:

Removing interaction with one layer (the Jet Engine) between the client and server should improve performance. However, the syntax for the queries will be SQL specific and does not support the Jet syntax anymore.

http://www.aspfree.com/c/a/Microsoft...-in-MS-Access/

If thats the only issue it shouldn't be a problem.

[MMcCarthy]

Just a quick FWIW from me Mary.... depending on the clients intentions have had considerable success using ADP format (thin client approach) using either MS SQL servers own applcation roles or Windows integrated security authentication, with users assigned to specific roles, preset permissions and so on. JET on the client is bypassed mostly but you can always call on that locally for whatever reason if so needed (personally I have never needed to in this scenario but you have the best of both worlds.

Security on the server is of course as good as you wish to make it. (had MD5 encryption applied in various instances to usernames and strings ON the server so that only the security cleared few can understand visually what is underlying, together with encrypted stored procedures etc etc

From a security perspective its as robust as your weakest link of course ie: the careless who might leave the server exposed to SQL injection attacks etc or even worse leaving the default login account as SA with a blank password. Sounds silly but how many servers are out there with that setup being pinged left right and centre.

Whilst I mention using ADP format it does not exclude MDB obviously you can implement that via the pass through route and MDB is currently being perceived as the ipso facto if you like for an Access 2007 interface with SQL Server. (whether that means a dumping of the ADP in due course like Data Access Pages who knows? except Microsoft, and unless anyone else has, I have seen nothing that warrants any fear that they may be so inclined to do so)

Regards

Jim

Thanks Jim

I won't know the clients full intentions until tomorrow. I am only going on third hand information at the moment regarding their requirements. You know how reliable that can be (LOL).

All I know for sure is they have a number of Access databases that they would ideally like to backend to SQL to improve security. They are going to start with one and based on how efficiently that can be achieved I'll pick up the contract for all the others. So nice little earner :)

I'll stick with mdb's as thats my comfort zone. My experience of ADP is very limited.

Mary

[Jim Doherty]

Thanks Jim

I won't know the clients full intentions until tomorrow. I am only going on third hand information at the moment regarding their requirements. You know how reliable that can be (LOL).

All I know for sure is they have a number of Access databases that they would ideally like to backend to SQL to improve security. They are going to start with one and based on how efficiently that can be achieved I'll pick up the contract for all the others. So nice little earner :)

I'll stick with mdb's as thats my comfort zone. My experience of ADP is very limited.

Mary

okey cokey Like I say its not so much the frontend shop window so to speak rather the backend server side that needs the coverage if security is the main issue with port lock downs etc if its internet accessible etc etc

Jim

[NeoPa]

I'm pretty well with Jim on this one Mary.
SQL Server, set up to use Windows Authentication (only - to protect against sa with no password), will give you as much security as the client could require. It still needs to be set up on the SQL Server, and with permissions being set for all the elements (Stored Procedures & User Defined Functions as well as Views) individually, you may need to be careful of blocking something unintentionally, but you have a fairly high level of control including flexibility and precision.

[NeoPa]

Very Strong Recommendations :

1. Use Roles to control the various levels of permissions that you want to assign.
2. Set up Groups in your Users. This enables you simply to add a user (account) to an existing Group to update access quickly and simply.

[NeoPa]

If using PassThru queries to MS SQL Server, don't forget to use ANSI-92 SQL (See ANSI Standards in String Comparisons).