Top 13 Mistakes and Missteps in SQL Server – #5: SSMS Is A Weapon of Mass Destruction

Tim Ford Best Practices, DMV Book, Dynamic Management Objects, SQL, SQL Server, Top 13 Mistakes 8 Comments Apr 10, 2013

There are No Background Checks

SQL Server Management Studio is a lovely product. It’s powerful. It’s feature-rich. It’s not yet been wrapped in a Metro interface. It’s free and easily-accessible. Your kids can download it (probably easier than most adults can.) Your kids can share it with other kids. Heck, the middle-manager in the finance can even find a way to download and install it onto his workstation as well and that guy can barely launch Spider Solitaire without calling the 1st Level Helpdesk support hotline.

Kim Dogg Un, with the assistance of his crack team of IT Comrades can even do it. Dear Free World: do you really thing Un-Dogg should have a conduit into any SQL Server instance on the face of this Earth?

I say “No!”

The Siren Song of False Security

In an earlier post in this series I rallied against assigning db_owner rights to ALL the users of a SQL Server database despite what the Vendor/Developer/Manager tells you. Do not be lured by their siren song with the hooky chorus of “We limit rights to the users in the application.” it doesn’t hold water when you have a simple and free way to bypass the application and get right to the data.

Consider this drama in one act; ripped, as they say, from the headlines:

ISV Representative: “Our application requires that each user’s Active Directory login be assigned to the database. Oh, and they need to be members of the db_owner role as well.”

DBA: “I can think of any number of reasons why this is not a good idea. First of all -”

Application Analyst: “Umm, yeah… DBA, we’re going to need to have you do whatever the vendor says or else they say they won’t support their product.”

DBA: ” – but…”

Application Analyst: “Umm, yeah… I am sure you’re smart and all, but these folks <em>built</em> this product. They know what they’re doing.”

ISV REPRESENTATIVE AND APPLICATION ANALYST THEN EXIT STAGE LEFT AND PUSH RANDOM INTERN DOWN FLIGHT OF STAIRS LAUGHING IN THE PROCESS. LEAVE BUILDING, SACRIFICE FERREL CAT IN FIELD BEHIND OFFICE.

END OF SCENE ONE.

By granting such access, anyone who has an AD login assigned rights in the database – db_owner role rights nonetheless – can download SQL Server Management Studio, connect to the SQL instance using AD Authentication, and bypass any “security” limits in the vendor’s application and get right down to deleting records, dropping tables, draining free space from the physical drives. They can do all this by simply visiting the Microsoft download site without ever having to bust out the plastic.

Such a Dangerous Weapon Should Never Fall Into The Wrong Hands

Obviously we can’t block access to Microsoft’s download site. What you can do is say the magic word when someone asks for you to install Management Studio on a workstation, server, laptop, or gaming console they may have access to on your domain. That magic word is

“No.”

It may not stop them, but it does put a road block in their way. One of those I-have-to-do-it-myself roadblocks that brings with it a deterrence to some. So, why you can’t stop users from downloading and installing Management Studio (unless you have Draconian laws preventing users from installing software in general, which would be an option) you can at least monitor for users running Management Studio and then do any number of things to prevent them from trespassing in such means again: killing the user’s session, ~~visiting their desk and leaving them threatening emails and bouquets of black roses, or re-education camps~~ a polite but stern email are options. If there is nothing you can do to prevent access via SSMS then the simple solution would be to work with the vendor to help them to understand what the risks are and then dial back the rights to be at a level of what the users really need. I go into this more in the post on db_owner.

How does one monitor for connections from SQL Server Management Studio? I wrote about this in my book and included the following script:

SELECT C.client_net_address
	, S.host_name
	, S.login_name
	, ST.text
FROM sys.dm_exec_sessions S
	INNER JOIN sys.dm_exec_connections C
		ON S.session_id = C.session_id
         CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) ST
WHERE S.program_name LIKE 'Microsoft SQL Server Management Studio%'
ORDER BY S.program_name
	, C.client_net_address;

If anyone is accessing the instance you’re querying using SSMS this query will return the IP address, login and host name for the connection. It practically lays bread crumbs for you to reach this trespasser!

Installing Management Studio on a SQL Server

You may notice that this conversation never reached into the bag of Internet Debate that looms over installing client tools directly on a SQL Server. That is because I firmly believe that it is both right and wrong to do this. I believe that there are arguments on both sides of this debate that make sense. Should (the Royal) Something happen that we need to connect directly to the server as DBAs to fix whatever is causing the Something we can do so if SSMS is installed. Conversely, SSMS and the graphical user interface for Windows Server is a resource hog. It would make sense to not have any graphical user interface or applications that make use of the GUI installed on a highly-performant server. This is the case for installing and running SQL Server 2012 on Windows Server Core, which is a viable option. Of course, if you do install SSMS on the server then we also need to discuss what is one of the other Mistakes and Missteps of administering SQL Server: Permitting Direct Server Access.