audit the hosts on a server

Question

audit the hosts on a server

brave heart

SSC Enthusiast

Points: 104
More actions
July 28, 2010 at 10:41 am

#157111

Hi everyone,
We have a server 'abc' and we have two sql logins 'sam' and 'jam' on the server.We have two authorized hosts 'auth1' and 'auth2' to use these sql logins but we usually see lot of un authorized hosts on the server using these logins.We need to schedule a daily job(sql query) that finds the unauthorized host and their details and put them into a table.can someone help with a query that finds the unauthorized hosts(not auth1 and auth2) connecting to my server 'abc' using the logins 'sam','jam' using the system tables.thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

rather than tracking where the inapproprate acces sis coming from, you could change the passwords for those two users, or maybe use a logon trigger to prevent access for those two usernames form anywhere except the authorized servers.

note that a trigger llike this needs to be tested....you might block out access for users that should have access.

CREATE TRIGGER logon_trigger_not_FromTheRightPC

ON ALL SERVER FOR LOGON

AS

BEGIN

IF suser_name() IN('sam','jam' )

AND host_name() NOT IN('auth1','auth2')

--raise an error, which goes to the error log

RAISERROR('Unauthorized use of login from inpermissible host.', 16, 1)

--prevent connection with these two usenrames

ROLLBACK

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

brave heart SSC Enthusiast Points: 104 More actions · Answer 2

lowell,

thank you very much for the reply.I really appreciate it.I was asked to just monitor the un authorized activity and file it into a table using the sytem tables in a server.thanks in advance.

Lowell SSC Guru Points: 323518 More actions · Answer 3

in that case, look at this article, which shows how to audit access to the databases with a server side trace:

http://www.sqlservercentral.com/articles/Administration/trace/872/

once that is in place, you can insert the results into a temp table, query it, roll it up with groups, etc to count unauthorized access and stuff.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Shinoj Ramanathan SSC Eights! Points: 894 More actions · Answer 4

You can select it this way and insert it into a table

SELECT COALESCE(@HostNames + ',', '') + host_name

, COALESCE(@LoginNames + ',', '') + login_name

FROM sys.dm_exec_sessions

WHERE

program_name LIKE '%Management Studio%'

AND login_name NOT IN ('sam', 'jam')

This can be scheduled

Shinoj