May 24, 2011 at 9:01 am
Is there a way to trace a sql login. What i am trying to do is,i have a sql login which is used for an application but i want to track any person who connects to management studio using that sql login. I have no issues if people access the databases using the application but i don want them to connect to sql server diretly though i want to provide them access, this might sound funny but is there an option to do that?
May 24, 2011 at 9:04 am
You could try a logon trigger
http://msdn.microsoft.com/en-us/library/bb326598(v=SQL.90).aspx
May 24, 2011 at 10:02 am
Since they are connecting as a SQL Server login, you can't get any information about the Windows user because it's not passed unless the application does in some way. The logon trigger will allow you to capture the IP address that's connecting (along with the workstation name) and that might give you a clue. However, if you decide to write to a table, make sure everyone who could connect as that login has INSERT privileges on said table.
K. Brian Kelley
@kbriankelley
May 24, 2011 at 10:09 am
I like using a logon trigger to track this, grab the IP and go from there. Best to just get the info you can, and then handle it offline by talking to them.
May 24, 2011 at 10:20 am
I created logon trigger but it not allowing the user to logon which i dont want,I want to allow them to logon and may be email me who logged on using that sql login. can some one provide me a script something like this?
May 24, 2011 at 10:30 am
Please post your trigger code.
Do you have Database Mail ready and able to send emails from your database instance?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2011 at 10:44 am
Tara-1044200 (5/24/2011)
I created logon trigger but it not allowing the user to logon which i dont want,I want to allow them to logon and may be email me who logged on using that sql login. can some one provide me a script something like this?
Likely they don't have permission to do whatever it is you're saying for the trigger to do. The logon trigger will execute in the context of the SQL Server login. Also, keeping the information in a table is preferable, but if you want to go the mail route, you should use database mail.
And as asked, posting what you've got for code (changing the name of the login you're trapping for) would be helpful for us to figure out why the login is being blocked.
K. Brian Kelley
@kbriankelley
May 24, 2011 at 10:44 am
yesi have database mail ready and tried to use the same code from the above link provided.
May 24, 2011 at 11:00 am
Did you leave ROLLBACK in? If so, this kills the connection and would explain why the login fails.
K. Brian Kelley
@kbriankelley
May 24, 2011 at 11:47 am
I am doing this but i am sure this is not sufficiant.. Let me know.
GRANT VIEW SERVER STATE TO EctApp;
GO
CREATE TRIGGER connection_info_trigger
ON ALL SERVER WITH EXECUTE AS 'EctApp'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'EctApp'
EXEC msdb.dbo.sp_send_dbmail @recipients='mlevan@state.nh.com',
@subject = 'EctApp User connected to SQL Server',
@body = 'user X connected directly to SQL Server using EctApp account',
@body_format = 'HTML' ;
END;
How can i capture the user name who used this sql account to connect to the sql server when they have to actually connect only using application, I would also like to save all these information in a table like you guys menioned.
May 24, 2011 at 11:55 am
create table UserTracking
( trackid int identity(1,1)
, userlogin varchar(200)
, logintime datetime
)
go
grant insert on UserTracking to Public
Change your trigger:
CREATE TRIGGER connection_info_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
insert UserTracking (userlogin, logintime)
select ORIGINAL_LOGIN(), getdate()
END;
Write a job that examines this table every minute, looking for new entries (use dateadd() as a filter) and then sends you a note. That way you aren't tying the email, and potential issues to logins.
May 24, 2011 at 12:33 pm
How can i capture the user name who used this sql account to connect to the sql server when they have to actually connect only using application, I would also like to save all these information in a table like you guys menioned.
If people you're concerned about always use SSMS you can code something like this in your trigger code to check if they are entering in a way you want to prevent:
SELECT host_name, login_time
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
AND login_name = 'app_login_name'
AND program_name LIKE 'Microsoft SQL Server Management Studio%' ;
Or better yet, if you can modify your application to supply an explicit application name in the connection string you can do something like this to find the offenders:
SELECT host_name, login_time
FROM sys.dm_exec_sessions
WHERE session_id = @@spid
AND login_name = 'app_login_name'
AND program_name != 'explicit application name in the connection string' ;
Reference for adding "Application Name" to your connection string: http://johnnycoder.com/blog/2006/10/24/take-advantage-of-application-name/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2011 at 12:50 pm
After creating the trigger as you gave, i coulnd not connect to the instance, the trigger is kicking out any login to access the server.
May 24, 2011 at 7:23 pm
Tara-1044200 (5/24/2011)
After creating the trigger as you gave, i coulnd not connect to the instance, the trigger is kicking out any login to access the server.
Log on to the server locally through Windows. Use SQLCMD with the -A switch to connect to the DAC and disable the trigger.
K. Brian Kelley
@kbriankelley
May 25, 2011 at 12:12 pm
ok, following up here, i created a server trigger, and a user with the same name as the OP for testing.
I'm gathering info based on this snippet to get the IP address; it returns a real IP or '<local host>' in SSMS when i run it.
--the auditing snippet below works fine in a
--login trigger,
--database trigger
--or any stored procedure.
SELECT
getdate() AS EventDate,
DB_NAME() AS DBName,
HOST_NAME() AS HostName,
APP_NAME() AS ApplicationName,
OBJECT_NAME(@@PROCID) AS ProcedureName,
USER_ID() AS Userid,
USER_NAME() AS UserName,
SUSER_ID() AS sUserid,
SUSER_SNAME() AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
client_net_address AS ipaddress,
auth_scheme AS AuthenticationType
FROM sys.dm_exec_connections where session_id = @@spid
So far, so good. For my logon trigger,
I'm using EXECUTE AS SELF on the trigger, under the theory that the admin account i created the trigger is "SELF", and would thus be able to insert into tables the login would not have access to, or to send dbmail the user would not have rights to do.
without execute as SELF, the trigger rollsback the connection, because by default the user does not have access to sys.dm_exec_connections
With execute as SELF,the trigger fires correctly when that specific user connects, the email is coming through, all pretty and formatted, but i'm not getting the IP address back from the query results..whether it's <local host> or a real IP;
can anyone tell me what i'm doing wrong to get the IP address?
ALTER TRIGGER TR_CONNECTION_INFO_TRIGGER
ON ALL SERVER
--EXECUTE AS SELF is equivalent to EXECUTE AS user_name, where the specified user is the person creating or altering the module
--I'm doing this in case the trigger writes to a table the login has no access to ,
--or where the login has not rights to msdb to send mail.
WITH EXECUTE AS SELF
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN() = 'EctApp'
BEGIN
--get detailed information about the offending user
DECLARE
@TableHead varchar(max),
@TableBody varchar(max),
@TableTail varchar(max)
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'table {border-style:solid;border-width:1px;border-color:#000000;}' +
'td {border-style:solid;border-width:1px;border-color:#000000; padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FFEFD8><td align=center><b>Item Tracked</b></td>' +
'<td align=center><b>Value</b></td></tr>';
SELECT @TableBody =
'<TR><TD>EventDate ' + '</TD><TD>' + CONVERT(VARCHAR(35),getdate(),112) + '-' + CONVERT(VARCHAR(35),getdate(),114) + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>DBName ' + '</TD><TD>' + ISNULL(CONVERT(VARCHAR(256),DB_NAME()),' ') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>HostName ' + '</TD><TD>' + ISNULL(CONVERT(VARCHAR(256),HOST_NAME()),' ') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>ApplicationName ' + '</TD><TD>' + ISNULL(CONVERT(VARCHAR(256),APP_NAME()),' ') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>ProcedureName ' + '</TD><TD>' + ISNULL(CONVERT(VARCHAR(256),OBJECT_NAME(@@PROCID)),' ') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>Userid ' + '</TD><TD>' + ISNULL( CONVERT(VARCHAR(50),USER_ID()),' ') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>UserName ' + '</TD><TD>' + ISNULL(CONVERT(VARCHAR(256),USER_NAME()),' ') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>sUserid ' + '</TD><TD>' + ISNULL(CONVERT(VARCHAR(50),SUSER_ID()),' ') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>sUserName ' + '</TD><TD>' + ISNULL(CONVERT(VARCHAR(256),SUSER_SNAME()) ,' ') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>[Is_ServerAdmin_Sysadmin] ' + '</TD><TD>' + CONVERT(VARCHAR(50),IS_SRVROLEMEMBER ('sysadmin')) + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>[Is_DB_owner] ' + '</TD><TD>' + CONVERT(VARCHAR(50),IS_MEMBER('db_owner')) + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>[Is_DDL_Admin] ' + '</TD><TD>' + CONVERT(VARCHAR(50),IS_MEMBER('db_ddladmin')) + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>[Is_DB_Datareader] ' + '</TD><TD>' + CONVERT(VARCHAR(50),IS_MEMBER('db_datareader')) + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>[ORIGINAL_LOGIN] ' + '</TD><TD>' + ISNULL(CONVERT(VARCHAR(8000),ORIGINAL_LOGIN()),' ') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>IPAddress ' + '</TD><TD>' + ISNULL(client_net_address,'<local host>') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '<TR><TD>AuthenticationType ' + '</TD><TD>' + ISNULL(CONVERT(VARCHAR(80),auth_scheme),' ') + '</TD></TR>' + CHAR(13) + CHAR(10)
+ '</TABLE>'
FROM sys.dm_exec_connections
WHERE session_id = @@spid
--put my pretty html together
SET @TableBody = @TableHead + @TableBody + @TableTail
--now send the email...
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'somedomain DBMail',
@recipients='lowell@somedomain.com',
@subject = 'EctApp User connected to SQL Server!',
@body = @TableBody,
@body_format = 'HTML' ;
END;--IF
END; --SERVER TRIGGER
Lowell
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply