Prev: unable to schedule file copy within sql server sched job
Next: MSSQL implicit conversion problem
From: Bob Barrows on 14 Jul 2010 20:24
jbdhl wrote:
>> With a SQL Server backend, the story is a little more satisfying.
>> There are tools on the market (Idera, Trillium, Guardium, etc.) that
>> provide the type of compliance monitoring you require.
>
> Thanks for your elaborate answer! You're right about the pl/sql thing,
> but luckily Access is not relevant here.

Oops - I saw the word "access" in your subject line, and combined with
"Can SQL Server help with this?" I jumped to the mistaken conclusion
that you were asking about MS Access.

> We will need to perform a large amount of post processing of the data,
> and possibly even modify what gets captured, so we would like to
> perform the capturing ourselves, instead of using a third-party
> product. There are also a number of other reasons why we would like to
> do it ourselves but that's out of the scope of this thread. :-)

If you have SQL 2008 Enterprise, the auditing you are talking about
comes right out of the box. Just do a quick google for "SQL 2008 audit"
to se what I mean. Earlier versions of SQL and less costly editions
(Standard, Express, etc.) of SQL 2008 will not have this.

> So, does anyone know how to perform such a capturing? How do the three
> products mentioned in the above citation do it?

They use server-side traces - google and BOL will provide lots of
information about creating and running server-side traces.

Re. Banana's suggestion to use SQL Profiler, he is correct that using
Profiler like this will adversely affect performance. If you want to
roll your own, use server-side traces instead - properly configured and
filtered, they will likely have very little affect on performance. But
of course, you still have the problem of accumulating terabytes of trace
data that needs to be analyzed ... along with the risk of shutting your
server down if you run out of space (I would assume you would not want
to simply shut off the traces if you run out of space to store the
results - that defeats the purpose of compliance monitoring, does it
not?)


--
HTH,
Bob Barrows


From: Erland Sommarskog on 15 Jul 2010 04:18
Bob Barrows (reb01501(a)NOyahoo.SPAMcom) writes:
>> So, does anyone know how to perform such a capturing? How do the three
>> products mentioned in the above citation do it?
>
> They use server-side traces - google and BOL will provide lots of
> information about creating and running server-side traces.

In case in one the products mention, I can add a testimony: I was
contacted by a person from that company, and he wanted to know how to
get access to the TDS specification. This was when the specification
still was under NDA. Whether they actually were able to get access to
the specification, or whether they opted to use server-side traces
I don't know.

> Re. Banana's suggestion to use SQL Profiler, he is correct that using
> Profiler like this will adversely affect performance. If you want to
> roll your own, use server-side traces instead - properly configured and
> filtered, they will likely have very little affect on performance.

That depends on what you trace. I know. I have caused performance issues
in production environments by running server-side traces. Filtering does
not always help. I inadvertently left a trace running which included the
Showplan event. The trace was filtered for a certain spid, but the entire
server was affected.

I don't think Showplan events are of interest in this case, but I still
wanted to add the caveat that you can cause damage with server-side
traces as well.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: jbdhl on 15 Jul 2010 06:32
Thanks for all your meticulous and insightful replies!

The thing we are strongly considering to implement (and thus the thing
that causes my questions in here) is a combined analyzer and "sandbox"
for database layouts. As mentioned, it should first of all capture all
statements send to the database for analysis and various post
processing. But secondly, we also consider making this thing a sandbox-
gateway/proxy for developers. That is, a gateway that can rewrite
queries/statements on-the-fly in order to make DBAs experiment and
play with query/schema modifications and their impact without altering
the application code (which, for various reasons, sometimes isn't
available). Therefore, this capturing/modification has to be made
before the queries actually hit SQL Server. It seems that a TDS proxy
is the way to handle this challenge.

The word "access" in subj. was just a verb, and has nothing to do with
the product "Access". :-) Also, my mentioning of pl/sql was a
momentarily absence of brain while I wrote the list of requirements
(I'm normally an Oracle guy).

Once again, thanks for your help!
From: Erland Sommarskog on 15 Jul 2010 17:10
jbdhl (jbirksdahl(a)gmail.com) writes:
> It seems that a TDS proxy is the way to handle this challenge.

Which by no means is a small order. You may be interested to check
out the FreeTDS project, at http://www.freetds.org. They reverse-
engineered TDS before the specification was public. Since this is
open source, their code might give you a head start. But note that
they only have a client; I don't think they have a TDS server.

I have not tried writing a TDS client, even less a server. But having read
the specification, my impression is that the login is the most tricky
part, particularly if you want to support the Kerebors stuff. Sending
queries is not that difficult. But there certainly is a risk that you
confuse the client API if you send your responses in the wrong way!

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2
Prev: unable to schedule file copy within sql server sched job
Next: MSSQL implicit conversion problem