Ttdfjt

I was monitoring some SQL calls using SQL Server Profiler and noticed a Domain Admin account accessing SQL quite a bit. We do not have a DBA at this point. There are two developers and I am one of them. Is this a bad practice and/or a security risk? I ask because the latest rash of ransomware attacks have a strong emphasis on hijacking Domain Admin account privileges so I am concerned.

You could use the following query to provide some quick details about the domain admin:

SELECT des.session_id
 , des.host_name
 , des.login_time
 , des.is_user_process
 , des.last_request_start_time
 , dest.text
FROM sys.dm_exec_sessions des
 INNER JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_id
 CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
WHERE des.login_name = N'DOMAINAccountName'

It'll show the name of the client machine where they are connecting from, and the last statement they executed.

In general, you probably want to explicitly control who has access to the SQL Server, especially for security-sensitive accounts such as members of the sysadmin and securityadmin server roles. The principle of least privilege applies.

This query will show you the members of each server-level role:

SELECT spr.name
 , spm.name
FROM sys.server_principals spr
 INNER JOIN sys.server_role_members srm ON spr.principal_id = srm.role_principal_id
 INNER JOIN sys.server_principals spm ON srm.member_principal_id = spm.principal_id
ORDER BY spr.name
 , spm.name;

In general, this list should be as small as possible. Pay particular attention to the securityadmin and sysadmin roles.

As an aside, you want to limit the number of people who have access to the Domain Admins AD group since members of that group could restart your SQL Server in such a way that they can gain access to it, even if they haven't been explicitly granted access. There are a lot of security implications to be aware of domain-wide for highly privileged groups such as the Domain Admins.

There is good information in the two existing answers, and you should follow-up on what they suggest.

But be aware that in some cases the account might not be actively connecting, we have seen cases where a user who has SSMS open and has a servers listed in their Central Management Servers (CMS), show as connected when if fact they are not actively doing anything on the server, nor are they intentionally connected.

I looked for documentation of this occurring and why, to reference in this post but I am not finding anything.

It's not good idea to have built-in accounts or default security groups i.e. Domain Users, Domain Admins etc.. to have access on SQL Server. Instead, create separate windows account/domain security groups to have access on SQL Server.

Also, some of best practices as follows, for more details have a look at security benchmark

Server Level

• Always disable sa login on production server (create a new login with sysadmin permissions before disabling sa)




• Do NOT let every login added into sysadmin server role, consider server hosts multiple databases that are related different applications, which have no relation each other. There are cases (I do not agree) that the application user required full permissions on the database server, in that case it's better to have separate instance.

Database Level

• Track regularly on Orphaned Users (especially when DB restored from unknown backups), drop them accordingly




• Beware db_owner database role have same level of access (within database) as sysadmin

Following query helps to find Orphaned Users from all databases:

exec sp_MSforeachdb
'Use [?];
select DB_NAME () as DBName, dp.name, 
 dp.principal_id, 
 dp.type, 
 dp.type_desc, 
 dp.sid, 
 sp.sid,
 ''use '' + cast(DB_NAME () as varchar(50)) + ''; if not exists (select containment from sys.databases where not containment = 0) drop user ['' + dp.name + '']'' as FixCommand
from sys.database_principals as dp
 left outer join sys.server_principals as sp on dp.sid = sp.sid
where dp.principal_id > 4 AND sp.sid is null and dp.type in (''S'', ''U'', ''G'')'
go