April 6, 2018 at 1:53 pm
Hello Folks,
I'm trying to find a user/developer who is trying to execute queries using SQLCMD. When I check using DMVs, the user is using an application account not domain account. Also SQLCMD is running from the db server. But no one has RDP/admin access to db server other than DBAs. Any thoughts on how to track this user. Also can you disable using sqlcmd?
SQL server - Failover cluster instance
Connection Details:
Host_name - sql_virtual_cluster_name
login_name - application account (sql account)
program_name - SQLCMD
Many thanks.
Any advice/modifications to the following script to track/deny access to the user (using logon trigger and extended event).
=============================================================================================
CREATE EVENT SESSION [Log17892] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION
(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.session_server_principal_name
)
WHERE
(
[error_number]=(17892)
AND [severity]=(20)
AND [sqlserver].[session_server_principal_name]=N'ApplicationAccount'
AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%Management Studio%')
OR [sqlserver].[client_app_name]=N'SQLCMD')
)
ADD TARGET package0.event_file(SET filename=N'M:\TRACE\Audit\Log17892.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [Log17892] ON SERVER STATE = START;
GO
---------------------------------------------------------------------------------------------------------------------------
--Create Logon Trigger
USE [master]
GO
/****** Object: DdlTrigger [LogonTrigger] Script Date: 4/10/2018 12:17:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [LogonAuditTrigger]
ON ALL SERVER
FOR LOGON
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@login SYSNAME = ORIGINAL_LOGIN(),
@app SYSNAME = APP_NAME();
IF @login = N'ApplicationAccount' AND @app LIKE N'%Management Studio%'
OR @app =N'SQLCMD'
BEGIN
ROLLBACK TRANSACTION;
END
END
GO
ENABLE TRIGGER [LogonAuditTrigger] ON ALL SERVER
GO
---------------------------------------------------------------------------------------------
April 7, 2018 at 8:09 am
You can't disable it, no. The best thing to do would be to try capturing the logins and connections using extended events. You can maximize the details available to you that way. You can even add the Action to capture the host name to track down which machine is making the connections. Here's a video that should help get you started.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2018 at 12:11 pm
SQL!$@w$0ME - Friday, April 6, 2018 1:53 PMHello Folks,I'm trying to find a user/developer who is trying to execute queries using SQLCMD. When I check using DMVs, the user is using an application account not domain account. Also SQLCMD is running from the db server. But no one has RDP/admin access to db server other than DBAs. Any thoughts on how to track this user. Also can you disable using sqlcmd?
SQL server - Failover cluster instance
Connection Details:Host_name - sql_virtual_cluster_name
login_name - application account (sql account)
program_name - SQLCMDMany thanks.
It's not real easy if they are deliberately trying a back door. Host name can be spoofed with sqlcmd using the -H option so that's not necessarily accurate. And then if they are using a SQL account, all you really know is the program name. You can spoof the program name with connections strings but I'm not sure you can do that with sqlcmd.
If you think whoever is actually accessing the server you can check the security event logs on the server.
You can't disable sqlcmd. I think about as close as you can get would be a login trigger that checks for APP_NAME() = 'SQLCMD'
Sue
April 9, 2018 at 12:22 pm
Grant Fritchey - Saturday, April 7, 2018 8:09 AMYou can't disable it, no. The best thing to do would be to try capturing the logins and connections using extended events. You can maximize the details available to you that way. You can even add the Action to capture the host name to track down which machine is making the connections. Here's a video that should help get you started.
Technically can't you just delete the sqlcmd exe?
April 9, 2018 at 4:13 pm
Thanks Grant
April 9, 2018 at 4:17 pm
Thanks Sue. Seems like user is using -H to spoof host name. This is something new to me. Thanks a lot! Instead of logon trigger can I make use of extended events, hope it’s low impact.
April 9, 2018 at 4:19 pm
Good thought... if this is deleted from the server, they can utilize sqlcmd from their remote machine with -H switch??
April 9, 2018 at 4:26 pm
SQL!$@w$0ME - Monday, April 9, 2018 4:19 PMGood thought... if this is deleted from the server, they can utilize sqlcmd from their remote machine with -H switch??
Yes. I would assume that they can't remote to your server. However, if they have access to the server through a login and know the name, ip address, port, they can connect with just about anything, including SQLCMD.EXE. You can lock down SQL Server instances quite easily, without deleting stuff you might want to use yourself, like SQLCMD.EXE (although, I'd probably always just use PowerShell myself). However, you need business buy-in because if everyone is used to having 'sa' or 'dbo' privileges and you suddenly take all that away, there will be issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2018 at 4:46 pm
Yeah.. they can’t remote to the server..
April 10, 2018 at 7:30 am
SQL!$@w$0ME - Friday, April 6, 2018 1:53 PMHello Folks,I'm trying to find a user/developer who is trying to execute queries using SQLCMD. When I check using DMVs, the user is using an application account not domain account. Also SQLCMD is running from the db server. But no one has RDP/admin access to db server other than DBAs. Any thoughts on how to track this user. Also can you disable using sqlcmd?
SQL server - Failover cluster instance
Connection Details:Host_name - sql_virtual_cluster_name
login_name - application account (sql account)
program_name - SQLCMDMany thanks.
Any advice/modifications to the following script to track/deny access to the user (using logon trigger and extended event).
=============================================================================================CREATE EVENT SESSION [Log17892] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION
(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.session_server_principal_name
)
WHERE
(
[error_number]=(17892)
AND [severity]=(20)
AND [sqlserver].[session_server_principal_name]=N'ApplicationAccount'
AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%Management Studio%')
OR [sqlserver].[client_app_name]=N'SQLCMD')
)
ADD TARGET package0.event_file(SET filename=N'M:\TRACE\Audit\Log17892.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [Log17892] ON SERVER STATE = START;
GO
---------------------------------------------------------------------------------------------------------------------------
--Create Logon Trigger
USE [master]
GO
/****** Object: DdlTrigger [LogonTrigger] Script Date: 4/10/2018 12:17:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [LogonAuditTrigger]
ON ALL SERVER
FOR LOGON
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@login SYSNAME = ORIGINAL_LOGIN(),
@app SYSNAME = APP_NAME();
IF @login = N'ApplicationAccount' AND @app LIKE N'%Management Studio%'
OR @app =N'SQLCMD'
BEGIN
ROLLBACK TRANSACTION;
END
END
GO
ENABLE TRIGGER [LogonAuditTrigger] ON ALL SERVER
GO
---------------------------------------------------------------------------------------------
Any advice/modifications to the following script to track/deny access to the user (using logon trigger and extended event).
=============================================================================================
CREATE EVENT SESSION [Log17892] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION
(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.session_server_principal_name
)
WHERE
(
[error_number]=(17892)
AND [severity]=(20)
AND [sqlserver].[session_server_principal_name]=N'ApplicationAccount'
AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%Management Studio%')
OR [sqlserver].[client_app_name]=N'SQLCMD')
)
ADD TARGET package0.event_file(SET filename=N'M:\TRACE\Audit\Log17892.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [Log17892] ON SERVER STATE = START;
GO
---------------------------------------------------------------------------------------------------------------------------
--Create Logon Trigger
USE [master]
GO
/****** Object: DdlTrigger [LogonTrigger] Script Date: 4/10/2018 12:17:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [LogonAuditTrigger]
ON ALL SERVER
FOR LOGON
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@login SYSNAME = ORIGINAL_LOGIN(),
@app SYSNAME = APP_NAME();
IF @login = N'ApplicationAccount' AND @app LIKE N'%Management Studio%'
OR @app =N'SQLCMD'
BEGIN
ROLLBACK TRANSACTION;
END
END
GO
ENABLE TRIGGER [LogonAuditTrigger] ON ALL SERVER
GO
---------------------------------------------------------------------------------------------
April 10, 2018 at 8:56 am
Is it possible to use sys.dm_exec_connections to see what computer they are actually connecting from? This DMV shows the IP address of the client, or is this also affected by that -H flag mentioned earlier?
April 10, 2018 at 10:34 am
Yes, you can see the client_ip from dm_exec_connections. But I want to audit/deny the user activity from sqlcmd. Thanks!
April 10, 2018 at 10:38 am
Since these are dynamic ips, it won’t help that much to track the user unless you ping the system as soon as possible to find host name.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply