3

I have a "who-did-what-to-which-table-and-when" requirement from one of my clients. Brief description of the system is as follows:

  • No source code of the software
  • No tracking of events by the software
  • All users use the application under the "sa" login
  • There is a login table in the database, that verifies the user credentials during application startup
  • SQL Authentication, not Windows Authentication. Creating separate users/roles in SQL is not possible as the connection string from the front-end to the back-end is hard-coded and cannot be edited

My issue is as follows:

  • I can get HostName, IP,Suser_Name(),MAC address etc. But the client isn't happy with any of them
  • I can map IP addresses to the user login table. Client isn't happy again.

My Question is:

Is there anyway I can link the username from the Login table to the DDL trigger? The Login table has 2 columns, Username and Password. Is there some join/ any other operation that I'm possibly overlooking here?

System works on SQL Server 2008

Rizzy
  • 35
  • 5
  • 2
    I'm pretty sure the answer, with all the limitations caused by the setup you have, is no. If the login routine updated the user login table (say with a last login date value) you could possibly do something with CONTEXT_INFO and a trigger on that table, but as your login table only has two columns, which the app obviously only reads to verify the user's identity, I think you're out of luck. What you've tried with mapping IP to user logins, or getting the HostName/IP/SUser_Name/MAC Address details, is likely to be the best the client can get. – MartW May 01 '16 at 19:32
  • 2
    during your application login keep the SPID - Username information in another table. At trigger, based on the SPID and retrieve the Username – Squirrel May 02 '16 at 01:05
  • @MartW, yes it looks like I'm out of luck. Thanks for your comment. – Rizzy May 02 '16 at 06:29
  • @Squirrel, I shall give this a try. I presume I must create a trigger for this, as I cannot edit any commands coming from the front-end to the back-end. – Rizzy May 02 '16 at 06:33
  • is the login table linked with AD? – Yuval Perelman May 28 '18 at 14:48
  • Your setup is problematic to say the least. **First**, you should never let your user use the "sa" login, under any circumstances. (or any login with sysadmin privileges for that matter). **Second**, You should never hard code your connection string, nor save it in the front-end (assuming the application is divided into front-end and back-end parts). **Third**, SQL Server 2008 is out of extended support as of last July. That being said, why do you need DDL triggers? Are your users allowed to make structure changes to the database? that's also a strong indicator for a problematic design.... – Zohar Peled Feb 02 '20 at 06:34
  • @AaronBertrand I was a late adopter of the system created by someone else. Had no control over most things as mentioned – Rizzy Feb 14 '20 at 15:40

1 Answers1

1

You might be able to trace the select statement of the user/password table and then join on spid and time frame.

https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/sql-trace

  • Create a trace by using sp_trace_create.
  • Add item events with sp_trace_setevent. Batch Complete should work in this case.
  • Set a filter with sp_trace_setfilter. Filter it down to your user/password table.
  • Start the trace with sp_trace_setstatus.
  • Stop the trace with sp_trace_setstatus.
  • Close the trace with sp_trace_setstatus.

https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/create-and-run-traces-using-transact-sql-stored-procedures

double-beep
  • 5,031
  • 17
  • 33
  • 41
e-Fungus
  • 321
  • 3
  • 17
  • I'd love to try this approach, but I do not use the aforementioned program anymore. This question was asked in 2016 and I cant find a way to 'close' it. Nevertheless, this seems to be the right way; had no clue we could play around with the server profiler using these events. – Rizzy Feb 14 '20 at 15:54