Sql-server – Domain Admin Account Accessing SQL Server

Securitysql server

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.

Best Answer

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'DOMAIN\AccountName'

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.

Related Question