April 29, 2021 at 3:32 pm
Trying to setup an extended event to capture bulk copy process. Was able to successfully capture some external applications but when I run manually the bcp command on server, the event is not captured. Also, when run on ssms the bcp command using xp_cmdshell it fails to capture the event. Not sure what I am missing.
cmd prompt:
bcp data_backup.dbo.tblname out "N:\audit\external.dat" -T -c
sql command:
set @cmd='bcp "select * from dba.dbo.one" queryout "'+@filename+'"'+' -T -c -t ^|'
exec xp_cmdshell @cmd
April 29, 2021 at 4:35 pm
better give us the extended events script - it's likely it needs slightly different capture settings and a few of us here can help with that.
April 29, 2021 at 4:48 pm
CREATE EVENT SESSION [BCP_Queries] ON SERVER ADD EVENT sqlserver.databases_bulk_copy_rows(ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)WHERE ([sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[username],N'''testuser1''') AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[username],N'''testuser2''')))ADD TARGET package0.event_file(SET filename=N'D:\bulkcopyrows.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=OFF)
April 29, 2021 at 5:33 pm
I'm at loss here - just tried it on my own server, resetting session completely even and bcp not trapped - bulk insert is logged fine (once I add the extra event)
April 29, 2021 at 8:41 pm
Since BCP is an external executable - all you can identify from either EE or Profiler will be the SQL statements that are generated and sent to the server. SQL Server has no way of knowing or identifying that those commands come from the BCP.exe application.
I found one recommendation is to put a comment into the SQL sent to BCP identifying it as part of a BCP command. Then you can filter on the SQL statement for that string - a bit clunky but might work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 29, 2021 at 8:57 pm
Thanks Jeffery. The problem here is identifying queries hitting server using bcp or freebcp to copy data without our notice.
April 29, 2021 at 9:42 pm
Thanks Jeffery. The problem here is identifying queries hitting server using bcp or freebcp to copy data without our notice.
Not sure you can audit for that - I would start by looking at the connection and seeing if you can identify the client application. But - I don't think either of those sets the application name and if they do it is a default 'Microsoft SQL Server' name.
Basically - if someone has access to SQL Server and read access to a database (or tables) then they can use many different applications to pull data. Excel, Access, SSMS, Azure Data Studio, BCP, DBeaver, DbVisualizer, Toad - to name just a few...
The only way to 100% prevent someone from pulling data out of SQL Server is to not give them access.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 29, 2021 at 10:02 pm
IF you control the connection string, you can use a specific app name, and then a logon trigger can review the app name and decide if it wants to let the logon be made. There are other things you could check in a logon trigger, of course, they might help you here, they might not. I don't know enough details of your environment to be sure.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 30, 2021 at 1:46 pm
yeah, it's down to whether or not the app name is included in the connection. If it's not there, it's probably not possible to tell where any given query is coming from.
Why are there open logins that let just anyone connect up with any application and access the data? Lock that down.
"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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply