SEO

#1 (**permalink**) 02-29-2008, 08:28 AM

Hi, All
I use SQL Server 2000 and Win 2003 Server. I try to create a SQL Server
Login audit log using Profiler, but cannot find the tools. I looked at the
Windows Event Viewer, it only logs Windows authentications users for SQL
Server not SQL Server users (Mixed mode users).

Please help on creating SQL Server Login log (username,password and time).

Thanks

Kai

#2 (**permalink**) 02-29-2008, 08:28 AM

Are you trying to log all SUCESSFULL logons and FAILED logons?
You can right-click the server in ENTERPRISE MANAGER and check the options
for each.

"kai" <kailiang@earthlink.net> wrote in message
news:crgDe.1579$6f.1305@newsread3.news.atl.earthli nk.net...
> Hi, All
> I use SQL Server 2000 and Win 2003 Server. I try to create a SQL Server
> Login audit log using Profiler, but cannot find the tools. I looked at the
> Windows Event Viewer, it only logs Windows authentications users for SQL
> Server not SQL Server users (Mixed mode users).
>
> Please help on creating SQL Server Login log (username,password and time).
>
> Thanks
>
> Kai
>
>

#3 (**permalink**) 02-29-2008, 08:28 AM

Oscar,
Thanks for responding. I selected all, then I relogin, in the event log
viewer, I cannot find new login. Where should I read the log?

Thanks

kai
"Oscar Santiesteban Jr." <oscarsantiesteban@worldnet.att.net> wrote in
message news:JWgDe.14190$5N3.7915@bgtnsc05-news.ops.worldnet.att.net...
> Are you trying to log all SUCESSFULL logons and FAILED logons?
> You can right-click the server in ENTERPRISE MANAGER and check the options
> for each.
>
>
> "kai" <kailiang@earthlink.net> wrote in message
> news:crgDe.1579$6f.1305@newsread3.news.atl.earthli nk.net...
>> Hi, All
>> I use SQL Server 2000 and Win 2003 Server. I try to create a SQL Server
>> Login audit log using Profiler, but cannot find the tools. I looked at
>> the
>> Windows Event Viewer, it only logs Windows authentications users for SQL
>> Server not SQL Server users (Mixed mode users).
>>
>> Please help on creating SQL Server Login log (username,password and
>> time).
>>
>> Thanks
>>
>> Kai
>>
>>
>
>

#4 (**permalink**) 02-29-2008, 08:28 AM

Kai,

In event viewer, there are 3 logs SYSTEM, Error (I think) and Application
Log. It should be in Application Log. OTherwise, in Enterprise Manager
expand MANAGMENT and look down to find "Current Logs" or "Logs" (you should
have the last 6 logs) top file should be the most current log. Look in that
one. This file is also in c:\program files\microsoft sql server\logs\
directory.

Oscar

"kai" <kailiang@earthlink.net> wrote in message
news:R4hDe.14071$aY6.12092@newsread1.news.atl.eart hlink.net...
> Oscar,
> Thanks for responding. I selected all, then I relogin, in the event log
> viewer, I cannot find new login. Where should I read the log?
>
> Thanks
>
> kai
> "Oscar Santiesteban Jr." <oscarsantiesteban@worldnet.att.net> wrote in
> message news:JWgDe.14190$5N3.7915@bgtnsc05-news.ops.worldnet.att.net...
> > Are you trying to log all SUCESSFULL logons and FAILED logons?
> > You can right-click the server in ENTERPRISE MANAGER and check the
options
> > for each.
> >
> >
> > "kai" <kailiang@earthlink.net> wrote in message
> > news:crgDe.1579$6f.1305@newsread3.news.atl.earthli nk.net...
> >> Hi, All
> >> I use SQL Server 2000 and Win 2003 Server. I try to create a SQL
Server
> >> Login audit log using Profiler, but cannot find the tools. I looked at
> >> the
> >> Windows Event Viewer, it only logs Windows authentications users for
SQL
> >> Server not SQL Server users (Mixed mode users).
> >>
> >> Please help on creating SQL Server Login log (username,password and
> >> time).
> >>
> >> Thanks
> >>
> >> Kai
> >>
> >>
> >
> >
>
>

#5 (**permalink**) 02-29-2008, 08:29 AM

Oscar,
Thanks, it is in the "Current Log", it contains the user. In Application
log, user shows "N/A"

Kai
"Oscar Santiesteban Jr." <oscarsantiesteban@worldnet.att.net> wrote in
message news:sqiDe.14532$5N3.10185@bgtnsc05-news.ops.worldnet.att.net...
> Kai,
>
> In event viewer, there are 3 logs SYSTEM, Error (I think) and Application
> Log. It should be in Application Log. OTherwise, in Enterprise Manager
> expand MANAGMENT and look down to find "Current Logs" or "Logs" (you
> should
> have the last 6 logs) top file should be the most current log. Look in
> that
> one. This file is also in c:\program files\microsoft sql server\logs\
> directory.
>
> Oscar
>
> "kai" <kailiang@earthlink.net> wrote in message
> news:R4hDe.14071$aY6.12092@newsread1.news.atl.eart hlink.net...
>> Oscar,
>> Thanks for responding. I selected all, then I relogin, in the event log
>> viewer, I cannot find new login. Where should I read the log?
>>
>> Thanks
>>
>> kai
>> "Oscar Santiesteban Jr." <oscarsantiesteban@worldnet.att.net> wrote in
>> message news:JWgDe.14190$5N3.7915@bgtnsc05-news.ops.worldnet.att.net...
>> > Are you trying to log all SUCESSFULL logons and FAILED logons?
>> > You can right-click the server in ENTERPRISE MANAGER and check the
> options
>> > for each.
>> >
>> >
>> > "kai" <kailiang@earthlink.net> wrote in message
>> > news:crgDe.1579$6f.1305@newsread3.news.atl.earthli nk.net...
>> >> Hi, All
>> >> I use SQL Server 2000 and Win 2003 Server. I try to create a SQL
> Server
>> >> Login audit log using Profiler, but cannot find the tools. I looked at
>> >> the
>> >> Windows Event Viewer, it only logs Windows authentications users for
> SQL
>> >> Server not SQL Server users (Mixed mode users).
>> >>
>> >> Please help on creating SQL Server Login log (username,password and
>> >> time).
>> >>
>> >> Thanks
>> >>
>> >> Kai
>> >>
>> >>
>> >
>> >
>>
>>
>
>

#6 (**permalink**) 02-29-2008, 08:29 AM

kai (kailiang@earthlink.net) writes:
> Thanks, it is in the "Current Log", it contains the user. In Application
> log, user shows "N/A"

Strange. I enabled login audit on my server, and this is what I found in
the Event Viewer for an SQL login:

18454 :
Login succeeded for user 'sommar'. Connection: Non-Trusted.

I'm running Windows XP, but I don't really see that would make a difference.

What I found, though, was that I had to restart the server for the setting
to have effect.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

#7 (**permalink**) 02-29-2008, 08:34 AM

One thing that the manual says when changing the audit settings for the
database is that you have to shutdown and restart SQLServer in order
for the change to take effect.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

SQL Login audit log