August 13, 2008 at 12:39 pm
Hi,
To give a quick background on what my team does and the current situation that I am requesting advice on:
As a standard practice, we now add our team accounts as SA to new instances of SQL and then remove the sysadmin role from the BUILTIN\Administrators group. We currently do not remove the BUILTIN\Administrators from SQL, but we leave public access configured.
I have enabled DDL triggers at the instance level and the database level and they are enabled and collecting data as expected.
We are currently auditing connections for this business critical SQL instance that was not previously managed by my team. The application owners used to have full reign and would push changes at will without proper change control.
There is a member of the builtin\administrators group that was able to create a table (DDL trigger picked it up), but that only has public access to SQL.
My question is: how in the world was this person able to connect to SQL and perform the work when they shouldn't even have access to connect? Am I missing something in my configuration / standard practice?
Currently, aside from our team members, these are the only SA logins:
sa
NT AUTHORITY\SYSTEM
I thought maybe he would have authenticated somehow with the system account and then created his login, did his work, then removed it, but the server level DDL trigger did not record anything like that.
I also then thought there may be a dbo role or account linked to his login and that allowed him to get in, but there is no indication of that either.
I am at a loss. The next step will be to disable the builtin\administrators group from logging in completely, but I don't think that should matter. The user shouldn't have been able to get in.
Thanks in advance for any thoughts/suggestions!
Steve
August 13, 2008 at 12:47 pm
How does he map into the database? Do any of those mappings give him db_owner or db_ddladmin? Who is the database owned by? If it's owned by him, he maps in as dbo.
If someone uses the DAC, DDL triggers do not fire. Therefore, if someone can log on to the server and then launch a connection using DAC, they can bypass said triggers. Given that if you schedule a task using the AT command it runs under the System account by default, this is a pretty trivial way for an administrator to bypass your security. He just would have to schedule a task using AT which launches SSMS (sqlwb.exe) or another tool interactively. Then he connects using DAC and is in. If he knows the sa password, he doesn't have to go through all that trouble. Just needs to log on to the box and use SSMS locally with a connection to the DAC.
Also, if you're using a Windows group and he has the ability to modify it, he could have dropped himself in, connected, made the change, and taken himself back out. That's why MS points out that you can only impede an administrator.
K. Brian Kelley
@kbriankelley
August 13, 2008 at 1:32 pm
Thanks for the reply.
I just went through each DB on the instance and queried with the following:
select [name], suser_sname([sid])
from sys.database_principals and was able to see that the dbo owner was mapped to the user's login.
However, the interesting part is that I had ran EXEC sp_changedbowner 'sa' before and it changed the DB owner to sa, but it left the user's name mapped to dbo. I re-ran that script just for the fun of it, and it corrected itself.
That is good information about the DAC- however, because the builtin admins group was revoked SA, they wouldn't be granted connect permissions. Can you provide a reference for the "AT" command you mentioned? He doesn't know the SA password, and he doesn't know the pw of our service accounts.
Because we took the SQL Server management over for this particular server, the domain admins are from a different team than the team we typically work with, so the first step that we are taking is to not allow them to modify anything within SQL Server without coming through our team.
We do have the groups running for the SQLService and SQLAgent accounts, but I don't think he went ahead and did that.
Thanks again for the suggestion of double-checking the mappings.
August 13, 2008 at 1:51 pm
SK (8/13/2008)
That is good information about the DAC- however, because the builtin admins group was revoked SA, they wouldn't be granted connect permissions. Can you provide a reference for the "AT" command you mentioned? He doesn't know the SA password, and he doesn't know the pw of our service accounts.
The BUILTIN\Administrators group was revoked, but you indicated the System account still is a member of the sysadmin role.
When you schedule a job using the AT command from a command prompt, by default the scheduled job will run as system. Because of that, if SSMS is run via this method, the user would have SSMS up on screen but under the context of System. That means the user would come into SQL Server as system.
Info on the AT command: How To Use the AT Command to Schedule Tasks
K. Brian Kelley
@kbriankelley
August 13, 2008 at 2:52 pm
Thanks Brian. This is excellent information to have.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply