How To Audit Trail In Web Application Using Sql Server
Jul 13, 2010
We are developing a web application using asp.net and sql server. We are required to do an Audit trail for the application. As I understand this, an audit trail would basically be for all the Inserts, Updates and Deletes on the data base right? Now the way to approach this is that I have an Audit Trail Table in the DB that populates after every insert,update or delete is fired (Manually writing the script within the DAL). However any DB changes directly fired from SQL Management studio will NOT be recorded (for obvious reasons :P).
To cater for that I could create a trigger and that takes care of everything. I also did some googling and found out that SQL server has the ability to do audit trail. However the problem with using triggers is that I will NOT get the user information that logged in the website. I will get the sql user but I dont give two hoots about that, I am concerned about the website user. A solution that I figured out was either
a) Have an audit trail from my web application and also have trigger set up. On the audit report, I simply show an audit log from web application and and audit log from sql server.Obvious problems with this approach: over head. Writing to two different sets of tables on EVERY DB CHANGE.
b) I have a column called UserId ON EVERY DB TABLE. Then I create a trigger to capture all the DB changes. I pass this userId on every table I change (insert,update,delete) and the get this id from the trigger.
Obvious setbacks: unneccesary userid column in every table I do appologize for the long post. Basically I need an audit log that does log all the db changes (including direct hack to db) but at the same times gives me user login information for those db changes that were made FROM the web application.
I have a question regarding the record audit trail with EF. Recently we have started working on a application that has to have an record audit trail.Just few tables have Guid as PK, the rest of them have Int32 standard Identity setup.
The quick solution we have found is to wire up an event handler on SavingChanges and get all Added, Deleted and Modified records. But the problem is that the Inserted records for the tables that have Int32 as PK are audited with PK value of 0.
I want to implement audit trail in my project. This implies keeping track of changes made in DB and later generating a report for specific data.
I'm looking for solutions other than triggers as in to have a generic solution for DB Audit trail in my project.
We'll probably be using Entity Framework 4.0. I want to explore the audit trail capabilities for the same any pointers in this directions would be helpful. also any ideas for DB Audit trail if we use Enterprise Library Data Application Block. Note:SQL Server 2008 has auditing features, but we'll not be using the Enterprise version.
I am in the process of creating an audit trail system. Simple enough. Certain fields require audit trail. What is the best design concept to allow this to work in multiple applications without having to change much? I would of course leave it to the admin of the site to add what fields should be audited, but the logic is the problem. Would implementing the interface IComparable be a place to start? My initial thinking is to compare 2 arrays against eachother and do an insert of what fields are in question. Array1 are the fields in the form, array2 are the fields from the sql table that require auditing.
My ASP.NET intranet web application uses Windows Authentication, and I would like to record the following details:
1) Windows ID 2) Session Start Time 3) Session Stop Time 4) URL being browsed to (optional)
I've got some basic code setup in "Session_Start" method of the Global.ASAX to log session start times (seen below), but that's it so far. I have the feeling this is a primitive approach and there are "better" ways of doing this. So I really have two questions:
1) Is this the right way to go about doing this? If not what are some other options? 2) If this is the right way, do I just need to drop some code in the "Session_End" method to record the time they exit, and thats a complete solution? Does this method always get called when they close the browser tab they have the site open in, or do they have to close the entire browser (I don't have logout functionality)? Any way users can skip over this session end method (or start for that case)?
Dim connsql As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionstring").ConnectionString) Dim cmdsql As System.Data.SqlClient.SqlCommand = connsql.CreateCommand cmdsql.CommandText = "BeginUserSession" cmdsql.CommandType = Data.CommandType.StoredProcedure Try cmdsql.Parameters.Add("@windowsid", System.Data.SqlDbType.VarChar, 30, "windowsid") cmdsql.Parameters("@windowsid").Value = Session("UserInfo").identity.name If connsql.State <> System.Data.ConnectionState.Open Then connsql.Open() cmdsql.ExecuteNonQuery() connsql.Close() Catch ex As Exception Finally If connsql.State <> Data.ConnectionState.Closed Then connsql.Close() End Try
I have an old 1.1 application. I want to add an audit log page which records every page user visits. who are they, which page they visit, and what time and so on. However, I do not want to touch the old code but just add another page to the website. Is it possible?
We have an (asp.net) application that connects to a SQL Server 2008 R2 database using login credentials specifically setup for this application.
Our application access/modifies/deletes records via stored procedures, to which we pass the username of the user performing the action as a parameter.
We need to be able to keep an audit log of all updates and deletes on certain tables. The solution also needs to be as minimal as possible and not require any developer intervention.
The easiest way I can find, is to put a trigger on the table (A) that copies the 'old' data into a history table (A_History) The problem is that we need to be able to tell who performed the action, for an update this is easy we just look at username of the newly inserted record. But for a delete we do not have access to the username parameter sent to the stored procedure.
Is there any way to implement this without using things like CONTEXT_INFO(), because this would need to be added to every stored procedure and every call, which is bound to be forgotten.
I am using SQL server triggers to do the database auditing for my website. My problem is that I cannot pass the username who manipulate data into the Audit table. The username saved into Audit table is always the SQL server user name which is part of my connection string. Is there any better way to handle this?
I'm currently using an SqlDataSource in ASP.NET/C# to let users insert, delete and update entries in a table/gridview. Every event needs to be written to an audit table.
I have easily implemented inserting and deleting - when inserting, the main info audited is just the parameter values of the insert query (e.Command.Parameters[0].Value.ToString() etc), and deleting is pretty much the same (just getting the ID in the delete query).
But with updating, I need to log which fields were changed and also their old values. How would I do this? As an example, here is the code for the inserting:
I have QuoteNumber(ddquotes.text),partnumber(txtpart),Lastmodifieddate(txtLMD) which is a time stamp on my webform.I am sending all these information to my vendaor as an email with attachment .
When i click on email button a message should be recorded in a Textbox saying so and so quotenumber,so and so partnumber has been sent on ,so and so datetime to so and so vendor.how can i acheive this with single button click.
Suppose you have the following... An ASP.NET web application that calls a stored procedure to delete a record. The table has a trigger on it that will insert an audit entry each time a record is deleted. I want to be able to record in the audit entry the username of who deleted the record. What would be the best way to go about achieving this? I know I could remove the trigger and have the delete stored procedure insert the audit entry prior to deleting but are there any other recommeded alternative?
If a username was passed as a parameter to the delete stored procedure, is there anyway to get this value in the trigger that's excuted when the record is deleted? I'm just throwing this out there...
I'm experiencing a to me mysterious error when sending e-mails through a SMTP-server from an ASP.NET web application. I get the famous error "unable to relay for xxx@yyy.zzz". What's mysterious to me is that when I cut and paste the exact same code that sends the e-mail into an usual .NET Windows Forms application, send the e-mail with this application, it all works just fine. This made me think that perhaps the problem is that the ASP.NET application runs as NETWORK SERVICE while the Windows Forms application runs on a domain user account, but it turns out that I have another ASP.NET application sending e-mail through the same SMTP-server running under NETWORK SERVER at the same IIS, and this application does not experience this problem.
I've further tried to send e-mails through the SMTP-server manually by telnet the smtp-server on port 25 and running the SMTP-protocol manually, and it all works fine. The SMTP-server is not configured with any kind of authentication or SSL.
Another mysterious fact is that the ASP.NET application can send e-mails from an adress within the same domain to an e-mail adress within the same domain, but not to any adress outside of the domain. But the Windows Forms application, that uses the exact same code, can send e-mails from any adress to any adress both within AND outside of the domain.
So to summarize:
The ASP.NET application can send e-mails from addresses within the domain to adresses within the domain, but not to addresses outside of the domain. A Windows Forms application running the same code on the same computer can send e-mails from ANY address to ANY address. Another ASP.NET application on the same IIS running under the same account (NETWORK SERVICE) can send e-mails using the same SMTP-server from ANY adress to ANY adress. There is no authentication configured on the SMTP-Server. Both the ASP.NET application and the Windows Forms application utilizes the System.Net.Mail.SmtpClient class to send a System.Net.Mail.MailMessage.
The code that sends the e-mail massage is:
private void button1_Click(object sender, EventArgs e) { MailMessage mesasge = new MailMessage(txtFrom.Text, txtTo.Text, "Test mail", txtBody.Text); SmtpClient client = new SmtpClient(); if (!(string.IsNullOrEmpty(txtUserName.Text))) //Is false since txtUserName.Text is empty client.Credentials = new System.Net.NetworkCredential(txtUserName.Text, txtPassword.Text); client.EnableSsl = false; client.Host = txtServer.Text; client.Port = 25; try { client.Send(mesasge); } catch (Exception ex) { txtResponse.Text = ex.Message; } }
As far as I can understand, this should be a matter of configuration rather than coding issues.
I tried to install Visual Studio Prof 2010 trail version in my system, but i got an error saying 'Please remove/uninstall Visual Studio 2010 load test controller' to proceed installation. i uninstalled my previous VS2008 software from system and i could not find anything like 'Remove/Uninstall Visual Studio 2010 load test controller' software in my Add/Remove Programs.
Question: I created an ASP.NET web application. Now it originally was meant for deployment on a webserver. That is working, so, so far so good. My question now is: Is it possible to deploy it as a desktop-application, too? That is to say the installer installs some kind of server, plus the web application, configures the server to run this application on a localhost URL, and then creates a link in the start menu/desktop, where it opens that url (website, the application) on localhost in a web browser?
I think it should be possible, though the server wouldn't be IIS, because this is a windows component which can't be separately installed. Is there anything like this already out there? The nonplusultra would be that it only starts the server when you click on the shortcut.
i have classes of my web application only the default one work if i for example click a link to go to another class i get this error for each class this happens . But only on the web server WHy ?
Server Error in '/' Application.
Parser Error
Description:An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.
Parser Error Message: Could not load type 'WebApplication2.Login2'.
Source Error:[Code]....
Line 1: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Login2.aspx.cs" Inherits="WebApplication2.Login2" %>Line 2: Line 3: Source File: /Login2.aspx Line: 1
I have written a program that interprets bar charts on the web and converts them into textual summaries. It is blind and visually impaired get more information during their web surfing. Anyways, right now all of the processing is handled on the user's machine and it is a very resource intensive program. So, I wanted to make it so the main computation was done by a server so that the user could simply click on a graph and get the summary instead of waiting while the computation was being done. Does anyone have a clue how to tackle this problem? If you need more information let me know.
I have an application that runs perfectly fine locally using the VS 2010 application server, however, when I deploy to our web app machine startup just spins and spins and never loads. We have other apps on this same machine that load just fine (this is a debug deployment of same app in product). I have been spinning my wheels on this for days and I am at a loss as the problem could be. Here is what I did
1) Create a new directory (same level as other apps) 2) Copied over our existing test ([URL]) and it works fine 3) Build and publish new debug app ([URL]) and it just spins trying to load first form.
I know the diretory is "working" as the 'test' application I put there works fine.
Server Error in '/staff' Application. Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'. Source Error:
[Code]....
Stack Trace:
[Code]....
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1
I have to write a application which connects to remote server(windows 2003 server, open SQL Management studio in that server and enter a specific SQL Server address and access a stored procedure, change some values and execute the stored procedure.)
I have to write this application using C#.Net , Could anybody point me in right direction, Where do I start?
We have a web application that makes a call to a remotely hosted ASMX service that worked in Server 2003 under IIS6. We are now migrating it to a Server 2008/IIS 7 setup and it doesn't appear that the application makes the call to the remote web service anymore. We ran Wireshark and don't see any traffic to the service URL. It seems to throw a 404 error without trying to connect.
Is there some security policy or restriction in Server 2008 or IIS 7 we need to set to allow an ASP.NET application make a call to a remote web service? We are using Network Service as our application pool user, but also tried using Local System as a troubleshooting step. In addition, the same code works if run from a console application on the server instead of the web application.
Is there some setting under IIS7 or server 2008 that would be causing this?
I would like to develop a server push supported application. Can anybody suggest which technology i should concentrate it reach this. I have already written a WCF Service using WS Binding. do i have to use JQuery? Commet? I have tried silverlight but it does support only polling. I am not looking for polling mechanism.
If i wanted to communucate from two application server to one SqlServer,what are the drawbacks if i implemented a senario like this, at the same time wat are the advantages.
My application is in .NEt Framework 2. 0 and which i have deployed in window server 2000 . when i run this application it gives me error like . server application unavailable .
I'am developing a web application with VS2010 Entity Framework and SQL Server Express. This application will be deploiyed in a server with SQL Server 2008 (not express) It is possible? What changes (if needed) i need to do?
