Re: Capture SQL Server Connections

Thanks for the feedback Russell and Linchi, I was able to accomplish my task.

For future reference, LOGON TRIGGERS are only support in SQL Server 2005+.

It also helps to install SQL Server 2005 Service Pack 2 to have it recognize
the LOGON TRIGGER keywords.

"Russell Fields" wrote:

Daniel,

As long as the LOGON TRIGGER is running the connection is not yet made
available to the user. So you can insert your code, or a call to your code,
in the trigger.

CREATE TRIGGER Setup_The_Login
ON ALL SERVER ...
FOR LOGON
AS
BEGIN
-- Call your code here
-- Do any error handling
-- IF an Error ROLLBACK
END;

If your C# code is implemented as a SQL Server CLR stored procedure, you can
simply call it. If it is an external object, you may be able to use the
SP_OA* procedures or xp_cmdshell to execute it in process with the trigger.

A caution is that the trigger will, of course, slow down the login process.
And you may have a lot more connections being made to the server than you
expect, depending on the client software. You may want to use event
notification for a while just to get a feel for the login frequency before
you implement a complex trigger.

RLF

"Daniel M. Dority" <DanielMDority@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:DE6CD447-CD8E-4CA2-B501-661298E2A222@xxxxxxxxxxxxxxxx
Ah excellent!

I am looking to postpone the logon with some custom code like C# until my
code has been executed. It seems that if I dump it into a login table,
the
login will succeed and the event will exit which I don't want...yet.

How would I go about this?

"Russell Fields" wrote:

With a LOGON trigger, you can have code to insert data to a logging table
or
even prevent the logon from happening. (Sample code is at the link.)
The
logon trigger is synchronous to the logon event and you have all the SQL
Server resources available to you, including the CLR, at the moment of
the
logon.

Event notification is asynchronous, so it is best for logging to a table
as
Linchi described.

RLF


"Daniel M. Dority" <DanielMDority@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:4E875148-8DC4-47B6-8165-F11B9099B34F@xxxxxxxxxxxxxxxx
Can I capture this event and inject some custom code either through
managed
or unmanaged code?

"Russell Fields" wrote:

If you are on SQL Server 2005/2008, you can create a LOGON trigger to
capture the logon events.
http://msdn.microsoft.com/en-us/library/bb326598.aspx

Would that give you what you need?

RLF

"Daniel M. Dority" <DanielMDority@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:FD1DEFC2-1947-46C2-875F-3D26A899A70A@xxxxxxxxxxxxxxxx
Is there an API/Interface available that would allow me to see who
is
connected to the database? Or an event that I can subscribe too as
they
are
connecting so I can provide a custom authenication mechanism?

This has to work from either connecting via ConnectionString or
logging
into
the SQL Server itself.

I know I can view their SPID but by that time, they are already
connected.






.

Relevant Pages

  • Re: Capture SQL Server Connections
    ... As long as the LOGON TRIGGER is running the connection is not yet made available to the user. ... If your C# code is implemented as a SQL Server CLR stored procedure, ... >>> connecting so I can provide a custom authenication mechanism? ...
    (microsoft.public.sqlserver.server)
  • Re: Records not inserting...
    ... > I am totally new to SQL server. ... However, in a trigger, would I be ... test the effects of certain data modifications and to set conditions for ... UPDATE statements. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Help for an Oracle Newbie
    ... application I wrote using SQL Server ported over to Oracle. ... Obviously there are several built in data types in SQL Server that do ... Of course I had to use a different name for the trigger under each ...
    (comp.databases.oracle.server)
  • Re: No one could logon to productio database for a while
    ... While this logon trigger is being executed, ... will Oracle wait for logon trigger to be completed before they ... can connect or will Oracle execute another "instance" of logon ...
    (comp.databases.oracle.server)
  • Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !
    ... >into SQL server, the record exist anyway! ... >If you refresh the data you will see the record... ... it gets refreshed after trigger excecution automatically. ... I don't know your monitoring system, but I guess that removing the result ...
    (microsoft.public.sqlserver.programming)
Loading