Building an auditing system; MS Access frontend on SQL Server backend.

Question

So basically I'm building an app for my company and it NEEDS to be built using MS Access and it needs to be built on SQL Server.

I've drawn up most of the plans but am having a hard time figuring out a way to handle the auditing system.

Since it is being used internally only and you won't even be able to touch the db from outside the building we are not using a login system as the program will only be used once a user has already logged in to our internal network via Active Directory. Knowing this, we're using a system to detect automatically the name of the Active Directory user and with their permissions in one of the DB tables, deciding what they can or cannot do.

So the actual audit table will have 3 columns (this design may change but for this question it doesn't matter); who (Active Directory User), when (time of addition/deletion/edit), what (what was changed)

My question is how should I be handling this. Ideally I know I should be using a trigger so that it is impossible for the database to be updated without an audit being logged, however I don't know how I could grab the Active Directory User that way. An alternate would be to code it directly into the Access source so that whenever something changes I run an INSERT statement. Obviously that is flawed because if something happens to Access or the database is touched by something else then it will not log the audit.

Any advice, examples or articles that may help me would be greatly appreciated!

Answer 1

How many users of the app will there be? Is there possibility of using windows integrated authentication for SQL authentication?

Updated: If you can give each user a SQL login (windows integrated) then you can pickup the logged on user using the SYSTEM_USER function.

Answer 2

@KevinKenny I would say something like 10-30 with potential for more in the future. I've never really heard or used of Windows integrated authentication for SQL server. Is it easy/secure? Things like that may be a tougher sell to the boss. :/

Answer 3

Does this work for you?

select user_name(),suser_sname()

---

Doh! I forgot to escape my code.

Answer 4

@JayMooney No, apparently my SQL server hasn't heard of either of those commands...

Answer 5

It should be

select user name(),suser sname()

replace spaces with underscores

Answer 6

LOL how embarassing, that also answers my unwritten question of what is up with the italics?

I'm not sure if those will work or just return the username I am using to connect the Access app to the database which is the same for everyone - is there some way to somehow use Active Directory as part of the SQL server authentication process?

Anyways I'm gonna run a few tests to ensure this is actually the case.

Answer 7

If you specify SSPI in your connection string to Sql, I think your Windows credentials are provided.

Answer 8

Ya those 2 commands did what I suspected and just returned the same thing for everyone.

I'm not using a connection string; as I said earlier I'm using Access and am just using Linked Tables to connect to my database.

Answer 9

you need to connect with integrated security aka trusted connection see (http://www.connectionstrings.com/?carrier=sqlserver)

Answer 10

@SQLMenace - is that the only way? Will I have to change the entire model of linked tables?

Honestly I don't even know how to integrate a connection string into Access...

Answer 11

I tried playing with Access a bit to see if I could find a way for you. I think you can specify a new datasource to your SQL table, and select Windows NT Authentication as your connection type.

Answer 12

Jay, can you go into a bit of detail on HOW you did that... hah just to get be started I don't exactly follow ya.

Will that still work though, I still need to authenticate with SQL server in order to run queries.

Answer 13

Sure :)

There should be a section in Access called "External Data" (I'm running a new version of Access, so the menu choice might be different).

Form this there should be an option to specify an ODBC connection.

I get an option to Link to the datasource by creating a linked table.

I then created a Machine datasource. I selected SqlServer from the drop down list. Then when I click Next, I'm prompted for how I want to authenticate.

Answer 14

Ugh not working, still adding the same things via the trigger.

Also I should mention I'm using ODBC to connect...

Answer 15

Can you post the trigger?

Answer 16

CREATE TRIGGER testtrigger1
ON testdatatable
AFTER update
AS 
BEGIN
    INSERT INTO testtable (datecol,usercol1,usercol2) VALUES (getdate(),user_name(),suser_sname());
END
GO

Answer 17

Ok, it's working here. I'm seeing my windows credentials when I update my tables. So, I bet we missed a step. Let me put together a 1,2,3 sequence of what I did and maybe we can track down where this is breaking for you.

---

1. Create a new MSAccess database (empty)
2. Click on the tables section
3. Select external data
4. Pick ODBC database
5. Pick Link to the datasource by creating a linked table
6. Select Machine datasource
7. Pick New...
8. System Datasource
9. Pick SQL Server from the list and click Next, Finish.
10. Give the new datasource a name and description, and select (local) for the server. Click Next.
11. Pick "With Windows NT authentication using the network login ID". Click Next.
12. Check Change the default database to, and pick the DB. Click Next. Click Finish.
13. Test the datasource.
14. Pick the table that the Trigger is associated with and click OK.
15. Open the table in Access and modify one of the entries (the trigger doesn't fire on Insert, just Update)
16. Select * from your audit table

Answer 18

Well apparently I'm an idiot because it seems to be working. I wanted to try it on a different account but of course no one with Access is installed is still here and I can't create a new account.

Ugh, anyways should be working - with final results to be posted on Monday!

Thanks for all the help.

Answer 19

We also have a database system that is used exclusively within the organisation and use Window NT logins. This function returns the current users login name:

CREATE FUNCTION dbo.UserName() RETURNS varchar(50)
AS
    BEGIN
    RETURN  (SELECT nt_username FROM master.dbo.sysprocesses WHERE spid = @@SPID)
    END

You can use this function in your triggers.

Answer 20

My solution would be not to let Access modify the data with linked tables.

I would only create the UI in Access and create an ADO connection to the server using windows authenticated in the connection string. Compile you Access application as dbe to protect the VB code.

I would not issue SQL statement, but I would call stored procedures to perform the changes in the database, and create the audit log entry in an atomic transaction.

The UI (Access) does not need to know the inner works on the server. All it needs to do is request and update/insert/delete using the stored procedures you would create for this purpose. The server should handle the work.

Retrieve a record set with ADO using a view with the hint NOLOCK implemented in the server and cache this data in Access for local display. Or retrieve a single record and lock only that row for editing.

Using linked tables your users will be locking each other.

With ADO connections you will not have the trouble to set ODBCs on every single client.

Create a table to set the server status. You application will check it before any action. you can use it to close the server to the application in case that you need to perform changes or maintenance.

Access is a great tool. But it should only handle its local data and not be allowed to mess with the precious server.

Answer 21

sorry guys its not an answer but a question for you. i have a company that uses access as front-end and back-end for their database application. they want it to be Ms Access front-end and SQL back-end. how do i do that. this is live data guys i dont wanna mess up coz i will get killed' can yu give me the steps