Voice of the DBA

Tag: syndicated

  • 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.

  • Parsing SQL Saturday Data – Getting the Titles

    I wrote about downloading the SQL Saturday data with Powershell, and that has worked well. However, I also need to parse this data. You can look at a sample XML file from the site with this link, and examine the structure.

    Essentially, it’s something like this:

    <event>

    …

      <title>x</title>

      <speakers>

         <id>1</id>

         <speaker>a</speaker>

      </speakers>

    …

    </event>

    I’ve left a lot out, but it’s not important. For my purposes, this is the main stuff I’m concerned about.

    As a first step, I wanted to print out some information. I’m tackling this in stages, so this is the first step.

    SelectNodes

    I found a number of ways to do this, but I liked the SelectNodes method. I won’t include all the code, since the loading of the XML file was covered in the previous post. I have the XML data in the $doc variable, so I did this:

    $doc.SelectNodes("//guide/name")

    That gives me this:

    sqlsatloop_b

    This is the path to an element in the document. However this isn’t what I care about here. I’ll need this later as I store other data, but for now I want session titles.

    If I change my code to:

    $doc.SelectNodes("//event") | Format-Table title, description

    I get this:

    sqlsatloop_c

    That’s a good start. I didn’t need the description, but I wanted to show multiple values in the table as a test.

    My plan was to get the speaker, but speaker isn’t an element below event. It’s below "Speakers", which is separate.

    That’s somewhat OK, as I’ll need to parse those out appropriately. The next step is getting the speakers. A little more complicated. The speakers are a child element below the event.

    I’ll tackle that in another post, because it’s slightly more tricky and I want to be sure I can devote a bit more time to discussing a way to do this.

    In the meantime, I cleaned up the code to be simpler and used the

    References

  • I May Not Shake Your Hands

    I’m traveling next week to the SQL Konferenz in Germany, and then on to Red Gate in the UK.

    However, I may pull a Wil Wheaton at the event and the office and not shake hands. I haven’t had issues with this in the past, with lots of handshakes, hugs, and even a few cheek kisses at times. This time, though it’s me, not you.

    I’ve been fighting a virus of some sort for a little over a week. I don’t have bad symptoms, mostly annoying chest tightness, sore throat, and a slightly runny nose. My son has bronchitis, but I don’t have a lot of his symptoms, and I don’t think antibiotics will help. No fever, so I think I have to wait things out.

    I may be miserable, but certainly not sick enough to cancel anything. I’ll be loading up on medicines to mask symptoms and ease travel, but I may not engage a lot with some of you.

    I also may limit questions, but I’ll bring a pad and if I can’t answer in person, I’ll be sure to blog or post answers later. My typing isn’t affected, so you’ll likely get a better answer anyway.