May 19, 2017 at 2:53 pm
Recently, there's been a spate of malicious mischief one of my employer's key servers.
> A database created, the owner falsified.
> A sysadmin had his login disabled and his password changed, 3 times.
I'm in the middle of locking down permissions on this server so I know which employees have membership in which roles. My employer currently has 6 people with sysadmin access to the production server and its databases. These people, counting me, are the only possibles.
I'm the lead sysadmin and it's my job to conclusively identify the perpetrator but I'm having a difficult time doing it. For instance, the server error log shows clearly when a database is dropped but not when it's created. The error log shows nothing about permissions and passwords being altered. The error log shows nothing about impersonations.
I've developed code that will allow me to comb the default trace for pertinent information but I'm wondering if that is enough.
I'm open to suggestions. If you were me, what tools would you use to conclusively identify the person responsible?
May 19, 2017 at 4:08 pm
One of the first things I would do is to not assume it's someone who is a current sysadmin with the company. It could be someone who is not a sysadmin who has managed to compromise an existing account - or created one that no one is aware of. Depending on your network access, could be a vendor that has done the same, a former employee who has done the same or just a flipping idiot creating headaches. Someone accessing another persons computer, someone visiting a current employee. Lots of possibilities. Through the years I've been at places that have been very surprised at who or how security was compromised. It's a painful way to learn how not to assume anything on those types of things.
I'd probably look into creating some type of increased auditing for the network and likely implementing this with a security policy. What that would look like really depends on your infrastructure, environment. Maybe look through the following and the related links to get some ideas:
Security Auditing Overview
Sue
May 20, 2017 at 8:37 am
In addition to what Sue suggested, also look for logins with the securityadmin role. If you have 6 logins with sysadmin, make sure they're all AD logins so you know they haven't shared login credentials. That sentence alone is pretty scary if you have to think about someone with sysadmin being trustworthy.
Have you considered that a sysadmin could have created a procedure and it happens when the procedure is executed? If your databases are owned a login with elevated privs and a procedure is defined WITH EXECUTE AS OWNER, then it'll have the privs to change the sysadmin's password. You can query for procedures defined like this using the following:
SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE execute_as_principal_id = -2;
You mentioned that the log doesn't show when a database is created. You can get that by querying sys.databases:
SELECT name, create_date
FROM sys.databases;
It might be something completely different, like someone outside the company. Make sure the "sa" login is disabled and stays that way.
There's a lot of directions to consider here.
May 30, 2017 at 5:43 am
as far as tracking if it happens again, i''d create an extended event to capture database creation and deletion, and add login disabled, as well as anything else you think needs to be monitored.
The GUI makes it really easy to create one.
you might want to consider a SQL Server Audit also, so you cover multiple bases. create a blank, new audit, then a specification for that audit, and add things related to logins and databases.
Lowell
May 30, 2017 at 10:37 am
Thanks for all of your replies. I've been so busy I haven't been able to reply.
I'm already doing some things that you recommend ("sa" login disabled, elimination of almost all SQL Server logins, and other things) and I keep adding things to tighten up security and my ability to see who did what, when. I've greatly enhanced by ability to find significant events in the default trace and the SQL Server and SQL Agent log files. I'm using some undocumented commands and stored procedures to look "under the hood" and see things not normally available for inspection (DBCC LOG, fn_dblog).
Tightening up security is a process, not an event. I plan to eventually get back to you with more details as I make progress.
I already know who the perpetrator is. I just need to prove it to management. It's a young person who doesn't understand that this is a business, not a game. So far, his intermittent mischief has not been harmful, only mischief, but it is completely inappropriate and I intend to prove it to him.
May 30, 2017 at 10:53 am
Thanks for posting back. Glad it's not any worse than it seems right now but that does bite having someone do that. Unfortunately sometimes lessons need to be learned the hard way. Hope he realizes he is lucky...plenty of other places would toss him out with that kind of behavior.
These kind of things force us into security reviews and looking at what else we can do to lock things down. Not a fun way to get there but still valuable.
Sue
May 30, 2017 at 1:58 pm
At the risk of posting thing people already know, I'll start posting some of my work and tools I produced to get "under the hood" of SQL Server.
Very little of what I will be posting will be original. I've done a lot of research and I'm going to post my altered versions of some very valuable code/tools that others were willing to share.
/*************************************************************************/
-- READ DEFAULT TRACE FILES
/*************************************************************************/
use master;
set nocount on;
dbcc traceon(2536,0); -- Ignore the tlog truncation point and look at all log records.
declare @PathAndFile nvarchar(max);
select @PathAndFile = convert(nvarchar(max), value)
from ::fn_trace_getinfo(0) i
join sys.traces t on t.id = i.traceid
where t.is_default = 1 and i.property = 2;
/*************************************/
-- Use the next line of code to determine where the default trace files are located.
--select @PathAndFile;
--return;
-- Then, you have the option of looking at older trace files, not just the current one.
/*************************************/
if @PathAndFile is null
begin
select 'The default trace path and/or filename is invalid.';
print 'The default trace path and/or filename is invalid.';
return
end
/*************************************/
-- set @PathAndFile = reverse(substring(reverse(@PathAndFile), charindex('\', reverse(@PathAndFile)), 500)) + N'log.trc' -- What did this line of code do in the original version of this script?
-- Use the next line of code to alter the trace file name in case you want to look at an older one.
--set @PathAndFile = 'F:\ErrorLog\log_285.trc';
/*************************************/
select last_occurrence, name + isnull(' (' + subclass_name + ')', '') EventName,
DatabaseName, LoginName, RoleName, TargetUserName, TargetLoginName, SessionLoginName, num_occurrences, TextData
from(select e.name, v.subclass_name, df.ApplicationName, df.DatabaseName, df.LoginName, df.RoleName,
df.TargetUserName, df.TargetLoginName, df.SessionLoginName, convert(nvarchar(255), df.TextData) TextData,
max(df.StartTime) last_occurrence, count(*) num_occurrences
from ::fn_trace_gettable(convert(nvarchar(max), @PathAndFile), 0) df
inner join sys.trace_events e on df.EventClass = e.trace_event_id
left join sys.trace_subclass_values v on v.trace_event_id = e.trace_event_id
and v.subclass_value = df.EventSubClass
where e.category_id = 8
and e.trace_event_id <> 175
and TextData NOT LIKE 'RESTORE%'
and TextData NOT LIKE 'BACKUP%'
and TextData not like 'DBCC OPENTRAN'
and TextData not like 'DBCC SQLPERF(LOGSPACE)'
and TextData not like '%3604%'
and TextData not like '%DBCC FREEPROCCACHE WITH NO_INFOMSGS%'
and TextData not like '%DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS%'
group by e.name, v.subclass_name, df.ApplicationName, df.DatabaseName, df.LoginName, df.RoleName, df.TargetUserName, df.TargetLoginName, df.SessionLoginName, convert(nvarchar(255), df.TextData)) x
order by last_occurrence desc
May 30, 2017 at 2:05 pm
And...
/********************************************************************/
-- SERVER LOGINS PROPERTIES
/********************************************************************/
select
-- sp.create_date as [Create Date],
-- sp.modify_date as [Modify Date],
sp.[name] as [Login Name],
sp.[type_desc] as [Login Type Desc],
case when sl.isntname = 0 then 'no'
when sl.isntname = 1 then 'YES'
else NULL
end as [Is Windows Name],
case when sl.isntuser = 0 then 'no'
when sl.isntuser = 1 then 'YES'
else NULL
end as [Is Windows User],
sl.dbname as [Default Database],
case when sl.hasaccess = 0 then 'NO ACCESS'
when sl.hasaccess = 1 then 'yes'
else NULL
end as [Has Access],
case when sp.is_disabled = 0 then 'no'
when sp.is_disabled = 1 then 'DISABLED'
else NULL
end as [Is Disabled],
case when sl.denylogin = 0 then 'no'
when sl.denylogin = 1 then 'DENIED'
else NULL
end as [Deny Login],
case when sl.sysadmin = 0 then 'no'
when sl.sysadmin = 1 then 'SYSADMIN'
else NULL
end as [Is Sysadmin]
--case when sl.securityadmin = 0 then 'no'
-- when sl.securityadmin = 1 then 'YES'
-- else NULL
-- end as [Is Security Admin],
--case when sl.setupadmin = 0 then 'no'
-- when sl.setupadmin = 1 then 'YES'
-- else NULL
-- end as [Is Setup Admin]
--case when sl.processadmin = 0 then 'no'
-- when sl.processadmin = 1 then 'YES'
-- else NULL
-- end as [Is Processadmin]
from sys.server_principals sp
inner join sys.syslogins sl on sp.[name] = sl.[name]
where sp.[type_desc] <> 'SERVER_ROLE'
and sp.[type_desc] <> 'CERTIFICATE_MAPPED_LOGIN'
and sp.[name] not like '##%'
order by sp.[type_desc] asc, sp.[name] asc
--order by [Create Date] desc
--order by [Modify Date] desc
May 30, 2017 at 2:25 pm
GailW here's something I wrote to enumerate the people who inherit permissions from a group, and do not have an explicit login in sys.server_principals
IF OBJECT_ID('[dbo].[sp_help_adusers]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_help_adusers]
GO
CREATE PROCEDURE [dbo].[sp_help_adusers]
AS
BEGIN
--###############################################################################################
-- Quick script to enumerate Active directory users who get permissions from An Active Directory Group
--###############################################################################################
--a table variable capturing any errors in the try...catch below
DECLARE @ErrorRecap TABLE
(
UnableToReadFromAD VARCHAR(50) DEFAULT 'Unable To Read From AD',
ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
AccountName NVARCHAR(256),
ErrorMessage NVARCHAR(256)
)
IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
DROP TABLE #tmp
--table for capturing valid resutls form xp_logininfo
CREATE TABLE [dbo].[#TMP] (
[ACCOUNT NAME] NVARCHAR(256) NULL ,
[TYPE] VARCHAR(8) NULL ,
[PRIVILEGE] VARCHAR(8) NULL ,
[MAPPED LOGIN NAME] NVARCHAR(256) NULL ,
[PERMISSION PATH] NVARCHAR(256) NULL )
DECLARE @groupname NVARCHAR(256)
DECLARE c1 cursor LOCAL FORWARD_ONLY STATIC READ_ONLY for
--###############################################################################################
--cursor definition
--###############################################################################################
SELECT name
FROM master.sys.server_principals
WHERE type_desc = 'WINDOWS_GROUP'
--###############################################################################################
OPEN c1
FETCH NEXT FROM c1 INTO @groupname
WHILE @@FETCH_STATUS <> -1
BEGIN
BEGIN TRY
INSERT INTO #tmp([ACCOUNT NAME],[TYPE],[PRIVILEGE],[MAPPED LOGIN NAME],[PERMISSION PATH])
EXEC master..xp_logininfo @acctname = @groupname,@option = 'members' -- show group members
END TRY
BEGIN CATCH
--capture the error details
DECLARE @ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
--put all the errors in a table together
INSERT INTO @ErrorRecap(AccountName,ErrorMessage)
SELECT @groupname,@ErrorMessage
--echo out the supressed error, the try catch allows us to continue processing, isntead of stopping on the first error
PRINT 'Msg ' + convert(varchar,@ErrorNumber) + ' Level ' + convert(varchar,@ErrorSeverity) + ' State ' + Convert(varchar,@ErrorState)
PRINT @ErrorMessage
END CATCH
FETCH NEXT FROM c1 INTO @groupname
END
CLOSE c1
DEALLOCATE c1
--display both results and errors
SELECT * FROM #tmp
SELECT * FROM @ErrorRecap
END
GO
Lowell
May 30, 2017 at 2:42 pm
Sue_H - Tuesday, May 30, 2017 10:53 AMHope he realizes he is lucky...plenty of other places would toss him out with that kind of behavior.These kind of things force us into security reviews and looking at what else we can do to lock things down. Not a fun way to get there but still valuable.
Sue
If that's not the truth, I don't know what is.
May 30, 2017 at 2:49 pm
Let's assume that one of the 6 admins may be the culprit. There is very little that you can do that they cannot undo. Unless you can put auditing on place and completely restrict access to it, as long as other admins exist you may never actually get to the bottom of it.
Sue hit the proverbial nail on the head. I'm strongly guessing that there is a rogue or compromised account in your environment. One of the most common sources of malicious activity in my experience is someone gaining access to an unlocked PC, a compromised password, or service account credentials that have been passed around and this account has elevated rights.
Quick story. An admin was planning a big weekend, and didn't want to perform the off-hours work. His "buddy" who was not an admin, volunteered to do him a favor. He couldn't elevate his rights because that would have been caught by the auditing that was in place, so he gave the buddy his user name and password. The rest of the story is not pretty.
First step would be to force all the admins to change their passwords, and force them to have some pretty strong passwords.
Here's an idea. I'm making a few assumptions, but here goes:
1. Create a new set of "admin" accounts for the administrators. Maybe one at a time?. So, I would create an adm_mjohn (or whatever) for myself.
2. Grant that account the admin rights.
3. Remove all administrator rights from EVERY non adm_ accounts.
Whenever you need to do administrative work, you need to log in using your adm account.
Now, you have a clean slate. If someone's non-adm account is used to try to do something, that's something that is easier to track.
One other thing. Is your organization large enough to completely support a separation of duties? Do the domain admins also belong to the DB admins, and maybe visa-versa? What about service accounts? They do not even need to have log in rights to SQL.
Good luck!!!! I think you should probably stock up on caffeine, pain pills, booze, and maybe nuclear weapons.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 31, 2017 at 10:39 am
"...something I wrote to enumerate the people who inherit permissions from a group.,,"
Thanks for posting that script, Lowell. I've been looking for a script that would do exactly what it does.
May 31, 2017 at 10:46 am
Sue, Michael, Ed, Lowell, thanks to all of you for your posts and ideas on how to break this problem.
Some of what you recommended has been done (we're switching over to Microsoft MSAs (Managed Service Accounts for all service accounts)) which have tight security. Other things are in process.
I'll get things completely locked down eventually. Already, since I first posted, the mischief has stopped. Either I locked down the right things or the perpetrator realized that he was going to get caught.
May 31, 2017 at 12:10 pm
Gail Wanabee - Wednesday, May 31, 2017 10:46 AMSue, Michael, Ed, Lowell, thanks to all of you for your posts and ideas on how to break this problem.Some of what you recommended has been done (we're switching over to Microsoft MSAs (Managed Service Accounts for all service accounts)) which have tight security. Other things are in process.
I'll get things completely locked down eventually. Already, since I first posted, the mischief has stopped. Either I locked down the right things or the perpetrator realized that he was going to get caught.
Thanks a lot for posting back. As I said earlier, a valuable lesson if nothing else. You always learn more about security and locking down servers when something like this happens. Just like everyone learning more about ransomware a week or two ago.
I would guess you locked down something he was using and then he realized he was going to get caught. So both. 🙂
Sue
May 31, 2017 at 12:22 pm
What's going on could be the result of a mis-configured schema sync or deployment tool; perhaps where the target is inadvertently pointing to production instead of the intended development instance.
Regarding querying the Default Trace, the fn_trace_gettable() includes the columns [StartTime], [SPID], [HostName], and [NTUserName]. You can also join sys.dm_exec_connections on [SPID] to get the [client_net_address].
Is that not enough information to identify at least the domain account and machine making the changes?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply