Audit Trail – Access 2010 w/SQL Server backend

I don’t do these posts much anymore, but something I just got working this morning definitely warrants one.

I maintain a database here that has an Access 2010 front-end and SQL Server back-end. Since I had to rebuild the database from scratch and actually know how to maintain it I took a few database classes and got a certificate where I learned some SQL and basic database knowledge. Little did I know that I also should’ve learned visual basic. I’ve played around with Access in the past and used it for little personal databases, but I’ve never actually built and maintained one like this. I tried desperately to avoid VB since I’ve never even looked at it before and I’ve always hated programming. But there were times where I couldn’t avoid it, some things you just can’t do without it.

The major thing I’ve been working on this week is creating an Audit Trail so I have record of who, what, and when a person edits or deletes a record from the database. The first link I found was this one http://www.fontstuff.com/access/acctut21.htm. I created the table just as he described it. The first problem I had was, I’m guessing, because it isn’t an Access database – it has a SQL back-end. Again, I knew basically nothing about VB when I started this a few days ago. Running the code it wasn’t even finding the table so I had to use my google-fu to figure what wtf I needed to do. That led me to learning about connection strings.

So this is what the section of my code looks like before it actually gets to the auditing part:

Sub AuditChanges(IDField As String, UserAction As String, Optional DeletedID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = New ADODB.Connection
    cnn.Provider = “sqloledb”
    cnn.ConnectionString = “Driver={SQL Server Native Client 10.0};Server=[server IP Address];Database=[DB Name];Trusted_Connection=yes;”
    cnn.Open
Set rst = New ADODB.Recordset
rst.Open “SELECT * FROM tblAuditTrail”, cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = fGetUserName()

The sections in bold are what I changed or added to the original code. I already have a function in Access, that I found somewhere else online, that automatically enters the username of the person logged into the computer. That’s part of the code I use to track new entries. All the reading I did the last few days said something like that is better than using enviorn(“username”) and since I already had the function I just used it.

So getting that far and using the rest of his code exactly the same at least got it partially working. I was able to track edits with all the information I wanted. But deletes and additions were not being recorded properly. It would record when a new record was added and also deleted, but it wasn’t capturing the primary key value for some reason, which is the entire point. I really don’t understand why it wasn’t working, I downloaded his example DB and looked at it, still couldn’t figure it out.

More research led me to another forum where the person used Martin Green’s code, but had the same problems I was having. It’s the 5th post down the page, by johngachui. He changed some other things in the code that weren’t what I was interested in, so I just focused on the part about the deleted records. He added the DeletedID As String part that I added up above. I didn’t really care about recording additions either since I already do that another way so once I got the code functioning, I removed the parts about recording new records.

Here’s the functioning code as I need it, without recording additions:

Sub AuditChanges(IDField As String, UserAction As String, Optional DeletedID As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = New ADODB.Connection
cnn.Provider = “sqloledb”
cnn.ConnectionString = “Driver={SQL Server Native Client 10.0};Server=10.55.12.4;Database=Deconfliction;Trusted_Connection=yes;”
cnn.Open
Set rst = New ADODB.Recordset
rst.Open “SELECT * FROM tblAuditTrail”, cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = fGetUserName()
Select Case UserAction
Case “EDIT”
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = “Audit” Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = DeletedID
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, “ERROR!”
Resume AuditChanges_Exit
End Sub

After making the changes in this code, The person in the 2nd forum also said to do things differently on the forms that call the auditchanges function and this is what I used:

Dim DelID As String

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
Call AuditChanges(“cevid”, “EDIT”)
End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
DelID = [cevid]
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then
Call AuditChanges(“cevid”, “DELETE”, DelID)
End Sub

Works great now, on the main form at least. I came across a ton of other forums where people reported issues with this not working on subforms because of the Screen.ActiveForm.Controls(IDField).Value part. I haven’t gotten that far yet. That is my next obstacle.

Sources:

, ,

Leave a comment

Access 2010 set focus on subform control

Just a quick post because I know I’ll need this in the future. In our little database we have here in the office I was wanting a specific control in a subform to become active when someone clicks in any of the blank space on the main form. I couldn’t get the stupid thing to work until I found a forum saying you have to first reference the subform and then reference the control after it.

Not sure it’s your issue, but to set the focus to a subform control from a main form, you need both:

Me.subformcontrol.SetFocus
Me.subformcontrol.Form.somecontrol.SetFocus

I just kept trying to use the one statement on the bottom. Can’t believe I haven’t run into this until now, but there you go. It worked like a charm.

Source:
Access 2010 – Navigation Page, cannot set focus to “inside” form

Leave a comment

ASA 5500 series dropping TLS packets

I’ve had this nagging problem with our email for quite awhile now, maybe a year or so. I’m more of a desktop/server/software troubleshooter, not so much into the networking stuff. My boss is the network admin. I’ve been getting much more involved in our firewalls and routers as of late. I noticed that TLS hasn’t been working for some reason. I didn’t figure out why until today. It hasn’t been a big issue because we’ve been getting emails anyway, but someone from outside our organization has now sent me two emails in the last month and a half with bounces he’s received.

Diagnostic-Code: smtp; 510 Did not receive the expected protocol response.

It’s sporadic though, he didn’t always have this problem. I was determined to figure it out today. There are people out there who don’t really care if something is working well, as long as it’s working – I’m not one of those. If I’m responsible for it, it’s going to function properly.

We have a firewall in front of our email gateway, an ASA to be exact. I have spent all day working on this, bouncing between our email gateway and the firewall trying to determine where the problem is. I noticed that email from our email server to the gateway was encrypted and from the gateway to the email server was as well. But anything coming to the gateway from the outside and anything going out from the gateway was not. So, ding ding ding, it has to be firewall.

I’m getting more comfortable working with the firewall, but it always makes me anxious because I’m afraid to break something. Essentially what was happening was that the ESMTP inspection was dropping the TLS packets since it couldn’t actually inspect them. Which would be why their server wasn’t receiving the expected response. It’s annoying though, that I didn’t see any indication of packets being dropped in the logs. Maybe I wasn’t looking in the right place. It makes me wonder who else has been having this problem and just not reaching out to us to let us know. I used the GUI for this, but there are commands to run it as well that you can see here at Experts Exchange.

For the GUI: In ASDM go to configuration -> firewall -> service policy rules -> under Global; global_policy, right click on inspection_default and edit -> go to the Rule Actions tab -> uncheck ESMTP.

As soon as I did that and hit apply I went back to my gateway and saw everything being sent/received in TLS now. Mission accomplished.

Source:

,

Leave a comment

Boot to CD on new Toshiba Satellite

I bought a new laptop yesterday. I ended up choosing the Toshiba Satellite P55-A5312. It seemed to be the best deal for the price range I was looking at. The only thing I’m not so happy about is no removable battery, hopefully that won’t be a problem in the future. I tend to be a control freak when it comes to computers so not being able to replace the battery if I need to is a bit odd for me.

Before I remove all the garbage on the laptop that I don’t really want on it and put the stuff I do use on it, I wanted to make an image of it as a backup. I haven’t bought a new computer in over 4 years now, besides at work. So I suppose I’m a little behind the times. But I found it really odd that the laptop would not boot to the ghost CD that I had in it. I went into the BIOS and changed the boot order. I hit F12 when it was booting up to select the DVD drive, but it went in to Windows anyway. What. The. Hell.

After looking around online since last night I was finally able to find a forum where someone gave directions that worked. I had to disable two options in the BIOS in order for this thing to boot to the CD. It has to do with secure boot, which is a new thing with Windows 8, Windows 8.1, Server 2012 and Server 2012 R2. So maybe I’m not too far behind the times. According to technet it is “a security standard developed by members of the PC industry to help make sure that your PC boots using only firmware that is trusted by the PC manufacturer.” It’s to help prevent rootkits and other forms of malware from booting with the system. Why does that then stop me from booting from a CD when I specifically tell it to do so? I find that slightly annoying. My control freak tendencies are popping up again. I don’t need computer manufacturers or Microsoft to protect me. If I break something it’s my own damn fault.

Whoever you are alexandru1 on the Tom’s Hardware forum, I thank you.

To switch UEFI native mode to legacy BIOS mode (and disable secure boot):

Restart the computer. In Windows 8, from the Start or Desktop screen move your pointer over the upper or lower right corner of the screen. When the Windows Charms appear, click the Settings ( ) charm, click Power, and then the Restart option.
Press and hold the F2 key when the screen goes black, and wait for the BIOS setup utility to launch. If your system cannot boot into Windows 8, shut down the computer completely, then press F2 while powering back on.
Select Security -> Secure Boot, and then Disabled.
Select Advanced -> System Configuration, and then Boot Mode.
Change UEFI Boot to CSM Boot.
To save the change and exit the BIOS setup, press F10.

I ghosted my laptop successfully. I had to re-enable UEFI boot to get it to boot back in to Windows when it was finished.

Source: Tom’s Hardware forum – New Toshiba Satellite – can’t boot from CD or USB

1 Comment

Adding drivers to Ghost WinPE

GhostEventDetails

This post is using the Symantec Solution Suite 2.5

I’m setting it up so that I have the ghost client installed on the majority of our workstations. The only ones I’m not installing it on our encrypted laptops. The first problem I came across was it getting stuck at “Polling for bound server.” I found that means the NIC drivers for my computer aren’t in the WinPE PreOS. So I found the drivers for my card and popped open the ghost boot wizard. Even after adding the NIC driver I couldn’t get the damn thing to work and could not find where it would tell me what the problem was. It was my own fault. I discovered the error at the top by double clicking on the failed job in the ghost console, which brings up the event log and it always failed on “To Virtual Partition.” I didn’t realize I could then click on that and the specific error would pop up. That error up above means the SATA drivers for my board weren’t in WinPE either. After going to the motherboard manufacturer’s website to get them and adding them through the steps below, it is now creating my image of my test computer.

Any missing drivers will need to be the Vista 32-bit version. It doesn’t matter what OS your computer is running.

Open the ghost boot wizard to edit the Windows PE PreOS. Then on the second screen click on WinPE-512 and click copy. The 512 image is for machines that have over 512mb of memory. Name it whatever you want. You don’t want to go editing the original copy of it in case it gets messed up somehow.

BootWizardBootWizardCopy

Now go ahead and click edit on the image you named. Depending on which drivers you are updating, select the proper tab and add new driver.

WinPEDrivers

After finding the path for the driver and giving it a descriptive name make sure you check the box for Vista as the OS.

AddNewDriver

Sometimes it doesn’t actually add the drivers to the image the first time you hit OK on the top two images. So click edit one more time and scroll down the list for the drivers you added to make sure their boxes are selected. It takes a few minutes for it to update the image.

Any computers that you have already installed the ghost client on need to have the virtual partition changed to the version you just created. In the ghost console right-click on them, go to the client tab and select the correct one from the drop down list. After changing that on all of your machines you’ll then need to run a task that refreshes the configuration and inventory on all the computers. THEN the backup regime or image create task will work. If you want all the new computers you install the ghost client on to use the same version of WinPE then go to the tools menu in the ghost console, down to options and change it on the client tab there.

Oh, in the midst of troubleshooting these problems I tried the PC-DOS virtual partition, but ended up getting stuck in a boot loop that kept sending me back to it and not letting me go back to Windows. The second article below describes how to hide the ghost partition to fix that.

Sources:

,

Leave a comment

Group policy on server 2012

We finally got our new server up and running as a new domain controller. Now we have a server 2008 and server 2012 as our DCs – our main DC was running server 2003, ouch. Trying to manage windows 7 machines with server 2003 was not fun so this was very exciting for me. I was working last week on making sure things were syncing properly between the two of them so I could start cleaning up group policy and look in to changing the way I have our printers set up. Things are a little different in group policy management editor, but not much.

One thing I did notice is you can now easily check the status of active directory and sysvol replication for the domain. When you open group policy management click on your domain and to the right the first tab will be the status. You can hit the detect now button on the bottom right to pull it and see what it says.

GPStatus

When I did this for the first time last week, instead of it saying 1 domain controller with replication in sync, it had 1 for domain controller with replication in progress. I can’t remember exactly what it said when you clicked the arrow next to it to show the details. But if you clicked on the domain controller this is the window that opened:

ReplicationInProgress

I tried figuring this out for at least 3 hours before I finally found a site that told me how to fix it. Being interrupted and having to work on other things probably didn’t help. It’s been a week now and I don’t remember what the site was. Clearly the permissions are messed up, but where to fix the permissions was the problem. All you need to do is open group policy management editor for whatever policy is on the list above. Right-click on the policy name and go to properties.

Properties

Go to the security tab, click advanced near the bottom and in the permissions tab click restore defaults. That was it, it was that simple. But it took forever for me to find the solution. Now one thing I noticed the next day was that resetting the permissions also resets who the policy applies to. Obviously I have the intern policy in order to lock down where they can go and what they can do. After resetting the permissions I logged in the next day and thought things looked weird because my control panel was missing. I logged in to the domain controller and noticed under security filtering in group policy management it was set for authenticated users instead of just the intern group. I didn’t realize it reset who it applies to until it applied to me, whoops. Luckily only 2 other people noticed before I fixed it.

,

Leave a comment

Keeping track of who deleted and created files

I received a call on Monday at home from someone at work in a panic because an entire folder on our network drive was missing. I had to recover it from our backups of the previous evening. I thought we had already set it up so we could find out who deletes files and folders, but we didn’t. I finished setting it up yesterday and had to go to four different pages to really get all the info I needed.

From all the reading I did online for how to track this information there are a couple of ways to do it. You can do it in group policy or you can use the auditpol tool if running server 2008. Starting with Server 2008 they added audit categories which allow you to fine tune what you want to see in the event log. With Server 2003 you can only enable an entire class, which could cause a huge amount of useless information to be included. That would defeat the purpose of me wanting to set file auditing to begin with because I wouldn’t want to search through a massive amount of logs for a couple of events.

First, if you are running a server 2003 domain still (I cry a little inside about this being true for us) you’ll need to go into group policy and enable force audit policy subcategory settings (Windows Vista or later) to override audit policy category settings. You can find that setting under computer configuration > policies > windows settings > security settings > local policies > security options.

I made the following changes on the file server directly since that’s the only one we care about. To audit when files and folders are deleted or created you want to enable success audits for the file system subcategory under the object access class. Here’s a list of all the classes and subcategories that I found helpful.  If you want to view all of the subcategories and their settings you can type auditpol /get /category:* into a command prompt. To enable this category use this in the command prompt auditpol /set /subcategory:”File System” /success:enable /failure:enable.

The last step is just like setting permissions on folders. Go to the folders you want to monitor, right-click on them and go to properties. Go to the security tab and then click the advanced button. Instead of using the permissions tab we’re using the auditing tab.  Click edit under this tab and add whatever group of users you want to monitor, in my case it was just the domain’s built-in users group since I want to what everybody does on our network share when they create and delete things. Then you can check the boxes for what actions you want to keep track of. In my case it was just the boxes for creating files, creating folders, delete subfolders and files, and delete.

Then you’re done. When you want to go back and check the event log on the file server you’ll see events under the file system category for the boxes you checked.

Next time someone deletes something they aren’t supposed to I can chase them down.

Sources:

  1. Technet forum – Where is my : Audit: Force audit policy subcategory settings (Windows Vista or later) to override audit policy category settings
  2. Technet forum – auditing file share on windows 2008 R2
  3. Technet – Fine Tune Your Security Audit Policies
  4. Technet forum – how can track who deleted file/folder from Windows Server 2008
  5. Technet – Advanced Audit Policy Configuration descriptions

Leave a comment