Creation of an audit system; MS Access Interface on SQL Server

Question

So, basically I create an application for my company, and it MUST be built using MS Access and must be built on SQL Server.

I made most of the plans, but it’s hard for me to determine how to handle the audit system.

Since it is used only internally, and you cannot even touch db from outside the building, we do not use the login system, since the program will be used only after the user has already logged into our internal network through Active Directory. Knowing this, we use the system to automatically determine the Active Directory username and with their permissions in one of the database tables, determining what they can or cannot do.

Thus, the actual audit table will have 3 columns (this design may change, but it does not matter for this issue); who (Active Directory user), when (add / remove / edit time), what (what was changed)

My question is how should I handle this. Ideally, I know that I should use a trigger so that it is impossible to update the database without registering an audit, however I do not know how I could capture an Active Directory user this way. An alternative would be to encode it directly in the access source so that whenever something changes, I run the INSERT statement. Obviously, this is not true, because if something happens to Access or the database is touched by something else, then it will not register the audit.

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

Answer 1

Does this work for you?

select user_name(),suser_sname() 

---

Doh! I forgot to avoid the code.

Answer 2

Ok, he works here. When I update my tables, I see my credentials in Windows. So I'm sure we missed the step. Let me put together a sequence of 1,2,3 of what I did, and maybe we can keep track of where it breaks for you.

---

• Create a new MSAccess database (empty)
• Click on the table section
• Select external data
• Select an ODBC Database
• Select a data source link by creating a linked table.
• Data source selection
• Choose a new ...
• System data source
• Select SQL Server from the list and click Next, Finish.
• Give the new data source a name and description and select (locally) for the server. Click "Next."
• Select "Using Windows NT Authentication Using Network Logon ID." Click "Next."
• Check Change the default database and select the database. Click Next. Click Finish.
• Check the data source.
• Select the table that the trigger is associated with and click OK.
• Open the table in Access and change one of the entries (the trigger does not start in Insert, just Update).
• Select * from your audit table.

Answer 3

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

Answer 4

I tried playing with Access a bit to find out if I can find a way for you. I think you can specify a new data source for your SQL table and choose Windows NT Authentication as the connection type.

Answer 5

Sure:)

Access should have a section called “External data” (I am launching a new version of Access, so the menu selection may be different).

Formulate this, there should be an option to specify an ODBC connection.

I get the ability to reference a data source by creating a linked table.

Then I created a machine data source. I selected SqlServer from the drop down list. Then, when I click "Next", I am asked to indicate how I want to authenticate.

Answer 6

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

Answer 7

We also have a database system that is used exclusively within the organization and is used to log into Windows NT. This function returns the username for the current user:

 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 8

How many application users will there be? Is it possible to use integrated Windows authentication for SQL authentication?

Updated . If you can give each user an SQL login (integrated windows), you can select a registered user using the SYSTEM_USER function.

Answer 9

It should be

 select user name(),suser sname() 

replace spaces with underscores

Answer 10

you need to connect with built-in security, as well as a trusted connection ( http://www.connectionstrings.com/?carrier=sqlserver )

Answer 11

My solution should not allow Access to modify data with related tables.

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

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

The user interface (Access) does not have to know the internal workings on the server. All he needs to do is request and update / insert / delete using the stored procedures that you will create for this purpose. The server must handle the work.

Retrieve the recordset using ADO using the NOLOCK prompt view implemented on the server and cache this data in Access for local display. Or get one entry and lock only this line for editing.

Using linked tables, your users will block each other.

Using ADO connections, you will not have problems installing ODBC on each individual client.

Create a table to set the server status. The application will check it before any action. you can use it to close the server for the application if you need to make changes or maintenance.

Access is a great tool. But it should only process local data and not be allowed into a messy server.