July 14, 2006 at 5:35 am
I want to connect MS Access to our underlying MS SQL database for reporting in our SAP R/3 4.7 enviroment.
I am concerned that if I am not careful, someone may be able to accidentally make changes to the SQL database. This connection should be for reporting only, with users not able to make any changes.
Can anyone help with this?
Thanks,
Nino
July 14, 2006 at 6:42 am
Your focus needs to be on security on the SQL server. Any users that connect to the database should only have the rights necessary for them to do their job. So for example someone who only needs reporting would only have datareader access.
SQL usually uses the NT Security to authenticate users, but you can set MS-Access to use SQL logins. This way you could write your MS-Access reporting database with a specific user that can only read data for reports. To do this when you are connecting to the database using an .ADP or linking ODBC tables in an .MDB specify that you want to use a SQL login rather than using integrated security.
July 17, 2006 at 4:47 pm
Agree with Steven. Use a static ADO connection and that will prevent someone from writing to your SQL database during that session. You can further use the security in SQL server and just grant select permissions on the appropriate fields. Let me know if you need any help opening an ADO connection.
Marvin
Marvin Dillard
Senior Consultant
Claraview Inc
July 17, 2006 at 5:43 pm
Thanks everyone. I set up a security group in AD and added it in SQL as a datareader. It seems to be working.
After more testing, I'll let you know if I have a problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply