Voice of the DBA

Tag: security

  • Detecting Encryption

    I ran across an article recently from MSSQLTips by my friend, Brian Kelley. It talks about the ways you can detect encryption in use in your database. Brian’s approach, which is one I agree with, is that you can look for symmetric keys, asymmetric keys, and certificates in the system tables. The tables you query are:

    • sys.symmetric_keys
    • sys.asymmetric_keys
    • sys.certificates

    That’s a good way to detect SQL Server encryption in use, but not encryption in general. One of the things I’ve advocated for applications that contain sensitive data and need to be protected from the DBA is to have the application create temporary keys or use .NET libraries to encrypt data. In that case, SQL Server just sees data, and doesn’t detect encryption.

    Brian offers a solution that is to examine any columns containing these data types

    • binary
    • varbinary
    • image

    That’s a good start, but how do you detect that this string is encrypted?

    504b 0304 1400 0000 0800 1a86 4640 0d41 …

    That’s actually not encrypted; it’s the start of a zip file. However it could be a jpg, a tiff or some other binary format. The only way I thought of was mirrored in this Stack Overflow note: you’d have to compare known file types and look for a pattern in a header of some sort that doesn’t match. It wouldn’t be sure you didn’t have encryption, but you might make some educated guess if no file type that might fit the data matches.

    There was also a link in the comments to a Stack Exchange discussion on the same topic. It’s similar, though I saw the use of the KEY_NAME() function in there. I hadn’t used it, perhaps because of the poor documentation of encryption in SQL Server. I also found a KEY_ID() function that works similarly, returning the ID for the name of a key.

  • T-SQL Tuesday #63 – Security

    tsqltuesdayIt’s T-SQL Tuesday time again and this month we look at security. Kenneth Fisher has chosen this as his topic for February and you can read his invite here. There are lots of choices on what you write about, and I’m looking forward to reading what people choose.

    You can join in, by writing a post today and publishing it with a link in Kenneth’s invite. Or you can write later and just put your own thoughts down on the subject.

    T-SQL Tuesday is the idea of Adam Machanic (b/t) , and it’s a monthly party where everyone writes on a specific topic. The first Tuesday of the month usually has a new invitation issues, and you have to watch for it. I’d recommend putting a reminder in your calendar. The second Tuesday of the month is when we publish posts.

    If you’d like to host, contact Adam.

    Security Across Environments

    At one point in my career, I worked with a startup company. We had a number of experienced people working in development, and we wanted to set up a series of environments early on to perform agile development. When I arrived, the application we built had been running for about 4 months, and we were looking to improve our data handling and development processes.

    At the time, we had a production server and a development server. There were accounts for the web application and the initial security had been to grant security on tables as appropriate for the web application. Any tables that existed for administrative use were limited to sysadmin access.

    This wasn’t a bad plan, but as we implemented a test environment, I knew this would be an issue. We didn’t want to give testers (or developers), access to the production AD account that was used by the web application. We also didn’t want any problems during deployment.

    Moving to Roles

    It can be hard to change security around on an existing application. Fortunately we had limited numbers of objects and applications accessing our SQL Server database, so I could easily determine if refactoring was going to break anything.

    My first refactor was to create two groups in each environment. I used code similar to this in Development, QA, and Production

    CREATE ROLE WebAppUser;

    CREATE ROLE WebAppAdmin;

    By creating these roles in each environment, we had a consistent place to set security for objects. We proceeded to assign generic read/write/execute security to objects to these roles as needed. The WebAppAdmin role accesses all objects (essentially as datareader/datawriter). to grant rights to the WebAppUser role for specific objects, we scripted out the rights assigned to the current WebUser user and then granted those rights to the role.

    The last step was adding the WebUser to WebAppUser. Once this was done, we essentially had duplicated permissions for the user, WebUser, through the user account and the role.

    Our test procedure for the change was to begin removing the rights granted to the user in the QA environment. Once we verified the web application still functioned, we made one final change.

    In the Development environment, we created a new user, WebAppUserDev and put this user in the WebAppUser role. We then changed IIS to use this user account. From this point forward, development was separated from production. The Windows admin changed the WebUser password and development was locked out of production. We did the same thing in QA and created a new account there.

    Once everything was done, we removed all direct object rights from the WebUser account in production. This was a scary day, as we were counting on our role having all the correct rights. Fortunately our process had worked, and the application continued to function.

    That was a lot of work, but a refactoring that doesn’t break anything can take some time. There will be multiple steps and it can take days or weeks to implement.

    Moving Forward

    From this point forward, development proceeded without problems. All of our object code now included one, or both, of these lines at the bottom of the script.

    GRANT EXECUTE on MySP to WebAppUser;

    GRANT EXECUTE on MySP to WebAppAdmin;

    We migrated object code between environments, but not security. Security for each environment was handled separately, with separate accounts added to these roles. Our deployments became much easier.

    When we needed a new role (for client auditors that could access specific tables), we added the role as a part of our deployment and assigned security to the role. The role deployment was handled separately from application deployment with a new role being deployed in a script, but a different user added in production by the sysadmin to AD and the role. Later deployments had permissions grants for the role that were the same in development, QA, and production.

    When we added a Staging environment, it was as simple as restoring the production database, deleting the orphaned WebAppUser user from SQL Server and creating a new user for that environment.

    Consistency

    One of the big issues with deployments not proceeding smoothly comes when one environment is not set up the same as others, and the scripts run in one environment do not execute on another. When users are included in all environments, you have a security hole, but when different users get different security, you create scripts that must be edited, and potentially mid-edited.

    The more you can abstract away portions of your application, whether this is through roles, synonyms, linked servers, or other items that can be named consistently, but configured differently in separate environments, the smoother your deployments will be.

    There are certainly challenges with deploying new items across environments, but that’s a discussion for another day. For security purposes, I think that roles are an important way to ensure that security is maintained, but deployments are not impacted.

  • It’s a Problem When a SysAdmin Can’t Log In

    I showcased a demo recently that looked at a potential issue with an application where a user used a simple update statement to gain privileged access to a system. That’s scary, and it’s a potential issue for many applications that handle their own authentication.

    The problem with this type of attack is that it can be hard to detect. A system administrator might not log in with their account for some length of time, during which the attacker has free reign in the system. However even if the administrator logs in, they might not detect the attack.

    How often have you entered your credentials for a system and it informs you that something was typed wrong. How many times have you locked out your account accidentally with too many bad password entries?

    Have you ever thought that your account was under attack?

    I know some people will have their account unlocked and retry their password, but I know plenty of administrators that would just reset their password.

    That’s bad.

    You should always check the login times for your privileged accounts. In fact, I’d like to be sure that you are using your privileged accounts regularly so that if someone were to gain access, you’d know it. Or if someone changed your password, you’d be aware.

    Treat your sysadmin account for what it is: a privilege, and a target for attack. We are seeing more and more attacks on our systems, and I expect the problems to only grow, not shrink.

  • Beware of Login Issues for Privileged Accounts

    In a recent post, I described an attack against a privileged account using a simple SQL Injection technique of updating data in a table. One of the things showed was an administrator using their user and password credentials, but being unable to log on.

    pwd6

    In this case, the administrator might easily assume there are mistyping their password, try again, and at some point reset their password.

    NEVER DO THAT.

    I mean, you might need to reset your password, but don’t take this lightly. If you are logging in with a privileged account, and you should do this sparingly,