Questions from “SQL Server Security for Everyone”

2

November 4, 2015 by Kenneth Fisher

I recently gave my SQL Server Security for Everyone session at Summit 2015. (I’m still in awe about that by the way.)

First of all here are the PowerPoint slides:

KennethFisher_SQLServerSecurityforEveryone_20151013

Next there we a few questions I said I would look into and here they are:

  • I use relink to fix orphaned users. What do you think of it?
    Honestly I’ve never heard of relink before and after searching around I still can’t find it. All I can assume is that it’s a user generated piece of code or that I misunderstood the name. If the person who asked is reading this please comment and let me know.
  • Do you have any experience with RAP?
    I’d never heard of RAP either but this one I was able to find. RAP (Risk Assessment Program) is one of Microsoft’s premier services. Basically Microsoft will review your system for you and give you advice. While I obviously still don’t have any experience with it I was able to find some information here and here from people who do have experience.
  • Securing a Login so it can only be used by a single application.
    Basically what the questioner was looking for here is a way to secure a login so that it can’t be used by an individual, only by the application itself.

    So for example we have an application that is using a SQL Server authenticated login called APP_ID. The developers of course know the password of the ID because it’s written into the code in plain text. So how do we make certain that the ID isn’t used by one of the developers to make changes that they shouldn’t be? From what I can tell we have a couple of options.

    • A logon trigger.
      First of all let me say if you are using a logon trigger make sure you know how to log in and disable it if there are any mistakes. Basically what you are going to do here is create a logon trigger that will check for a specific application ID and a specific login name.

      CREATE TRIGGER connection_limit_trigger
      ON ALL SERVER 
      FOR LOGON
      AS
      BEGIN
      IF ORIGINAL_LOGIN()= 'test_app_login' AND
      		APP_NAME() <> 'test_app'
          ROLLBACK;
      END;

      The down side here is that you can fairly easily pretend you are coming in through a different application or even a different machine. The up side is that most people wouldn’t bother even if they did know how.

    • Application Roles
      With an application role you connect as one user and then the application sets the Application Role that completely replaces the current set of permissions with those of the role. The application role does require a password to set it but this can be encrypted (from what I read this is technically a one way hash so you’d want to use an encrypted connection as well). The down side here is that application roles are database principals and can’t connect to any other database. You might be able to get around it by using EXECUTE AS and cross database ownership chaining but I haven’t tried it so I can’t be certain, not to mention that might require some fairly extensive application changes. You also have to be careful with who knows the password, but let’s face it, until we are all using biometrics that’s always going to be the case.

     
    Obviously neither of these methods is perfect. The best bet in my opinion is going to be change the App_Id password, don’t give the password to the developers and use some form of encryption to avoid it being in plain text anywhere.

2 thoughts on “Questions from “SQL Server Security for Everyone”

  1. […] nolock, nolock and nolock. We have so many problems with security that it prompted me to write a session on SQL Server security and sp_dbpermissions and sp_srvpermissions (tools for security research). We have other issues but […]

  2. […] were the same the server and database principals were related. One of the major points I make in my SQL Server Security for Everyone session is that the server and database principals are joined by the SID and if you forget that and […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: