THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Securing Production Servers from Developers with SSMS and Application Usernames/Passwords

As a DBA I have all kinds of elevated access that most people don't.  I guess when I was younger this was a novelty but it really isn't anymore.  I have access to the master list of usernames and passwords for all kinds of stuff, but would never use them for anything unless it was appropriate to do so.  So it really aggravates me when I find or catch someone using an application's username/password to log into SQL with SSMS for the simple fact that I would never do this, not to mention that it is a violation of security policies.  It seems to happen however, so how do we go about preventing it. 

Well with 2008 I have been told a that it should be possible, but it really is possible to manage this with SQL 2005 as well:

CREATE TRIGGER CheckLoginFirst
ON ALL SERVER WITH EXECUTE AS CALLER
FOR
LOGON
AS
BEGIN
IF
Is_Srvrolemember('sysadmin') = 0 AND (APP_NAME() LIKE '%Microsoft SQL Server Management Studio%')
BEGIN
RAISERROR
('You have violated the Security Policy by using a Service Account to Access SQL. This has been logged and will be reported.', 16, 1) WITH LOG
ROLLBACK
END
END



This is a really simple DDL Trigger that will first, prevent the login for any non-sysadmin, your application users aren't sysadmins are they, that tries to login with Management Studio, and then log the event to the SQL ErrorLog, you do monitor your error log right?



The above code is founded on a blog by Jens Suessmeyer.

Published Wednesday, June 18, 2008 1:28 PM by Jonathan Kehayias

Comments

 

Denis Gobo said:

I would add SQL Query Analyzer to that list, I still use QA to login to 2005 and 2008 servers

May 12, 2009 1:39 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement