Re: Capture SQL Server Connections

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
    ... LOGON TRIGGERS are only support in SQL Server 2005+. ... As long as the LOGON TRIGGER is running the connection is not yet made ... connecting so I can provide a custom authenication mechanism? ...
    (microsoft.public.sqlserver.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: No one could logon to productio database for a while
    ... While this logon trigger is being executed, ... can connect or will Oracle execute another "instance" of logon ... We have no of process set to 1200 and there were only 383 oracle shadow ...
    (comp.databases.oracle.server)
  • Re: No one could logon to productio database for a while
    ... While this logon trigger is being executed, ... can connect or will Oracle execute another "instance" of logon ... We have no of process set to 1200 and there were only 383 oracle shadow ...
    (comp.databases.oracle.server)
  • Re: Cached logon for multiple domain
    ... We have x no. of clients, say 10 and y no. of domain, say 2. ... clients need to logon. ... starts their VPN before connecting to their ... This seems to have problems with the DNS. ...
    (microsoft.public.windows.server.general)