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.

Filed under: Microsoft SQL Server, Pass, Security, Speaking, SQLServerPedia Syndication Tagged: microsoft sql server, security, speaking