Voice of the DBA

Tag: security

  • Let NonAdmins Get Logins

    I saw a question recently about how to allow some users to see the logins on a server without being a sysadmin or securityadmin. This was in support of a migration effort, so users needed read rights without being able to change anything.

    This appears to be a good place to use the WITH EXECUTE AS option for a stored procedure. I decided to try. I have a normal, non privileged user, JoeDBA, with rights to connect to my Sandbox database. I decided to create a proc as a sysadmin user.

    Here is my code. Notice the WITH EXECUTE AS option. Since I created this with my sjones, sysadmin, account. This should be able to call into master and get the data.

    CREATE PROCEDURE GetLogins_NonSA
    WITH EXECUTE AS OWNER
    /*
    Description:

    Changes:
    Date       Who         Notes
    ———- —         —————————————————
    1/24/2017  PLATO\Steve Initial proc to get server logins
    */
    AS
    BEGIN
    SELECT name
         , principal_id
         , sid
         , type
         , type_desc
         , is_disabled
         , default_database_name
    FROM master.sys.server_principals

    RETURN
    END
    GO

    GRANT EXECUTE ON GetLogins_NonSA TO MigrationRole
    go
    ALTER ROLE MigrationRole ADD MEMBER JoeDBA

    Now, I can log in with a low privileged user. By default, a query against sys.server_principals should only return my login. If I query the DMV, I get this:

    2017-01-24 10_08_56-SQLQuery2.sql - (local)_SQL2016.sandbox (JoeDev (71))_ - Microsoft SQL Server Ma

    Now, I can execute the procedure. I’ve scrolled the results a bit, but you can see I view other users.

    2017-01-24 10_09_30-SQLQuery2.sql - (local)_SQL2016.sandbox (JoeDev (71))_ - Microsoft SQL Server Ma

    Certainly I could limit the columns returned, or transform them to provide more data, but this is a good way to give read-only access to a login about other logins.

  • Using a DMK in TDE

    When you setup TDE, you need to create a DMK (Database Master Key) in the master database if one doesn’t exist. This key forms the basis of a chain of protection for your data inside of the SQL Server instance. The hierarchy is:

    • The DMK exists in the master database. This protects a certificate or asymmetric key by encrypting it.
    • The certificate in turn encrypts the DEK in a database using TDE. This certificate is required to open the DEK in the database, or open the DEK during a restore operation.
    • The DEK encrypts the data.

    There is only one DMK in each database, so the DMK in master for TDE could be used to protect multiple certificates, asymmetric keys, or symmetric keys that exist in master for use by TDE or any other encryption mechanism.

    A particular DMK is not needed to restore a TDE database on another instance. Rather the new instance of SQL Server should have a DMK created in the master database. Then the backup of the certificate is restored on the other instance, protected by the DMK there. Once this is complete, the TDE database can be restored.

    If you’d like to know more about what a DMK is, I’ve written about it in a previous post.

  • What’s a Database Encryption Key (DEK) in TDE

    The encryption mechanisms in SQL Server are interesting, and they work well, but they are somewhat poorly named. I ran across a few people struggling to understand, so I decided to cover the concepts in a series of posts. This one looks at the Database Encryption Key (DEK).

    The Purpose of the DEK

    The DEK is designed to actually encrypt and decrypt the data in your mdf/ldf/ndf/backup files when you use TDE. This key is passed to the AES algorithm and allows SQL Server read a block of encrypted text from disk and decrypt it before placing it in memory. This same process is reversed, with data from memory being encrypted before being written to disk.

    The DEK is created in a database, specifically for use with TDE and isn’t used in any other encryption process. The DEK also cannot be backed up, or restored, except with a database restore.

    Protecting the DEK

    The DEK is protected with a certificate. This can be a purchased or self-signed certificate, but in either case, the certificate must reside in the master database of the instance hosting the TDE encrypted database.

    When a DEK is created, the DDL requires that either a certificate or asymmetric key is specified as protecting the DEK. SQL Server will use this certificate (or asymmetric key) to decrypt the DEK when the database is first opened and then uses the DEK to encrypt and decrypt all data.

    Without the certificate that has encrypted the DEK, the SQL Server instance cannot access any data encrypted by the DEK.

    That’s about it. The DEK simply provides encryption for TDE, and exists only inside of a particular database that will use TDE.

  • Delaying Patches is Problematic

    I was listening to a DevOps podcast from Josh Corman, of Rugged Software. Rugged Software aims to improve security by asking developers and sysadmins to adhere to their manifesto, which recognizes both the importance of software in the modern world, as well as the problems associated by not properly securing and patching software. These are duties that both developers and administrators should perform in their respective roles.

    The goal of the Rugged Software is to promote best practices and constant vigilence to create and deploy secure code, and ensure that software installations remain that way. This isn’t a security state, but rather a process. A way to approach software development, deployment, administration, and maintenance to improve the security of our computer systems. This maps nicely to a DevOps approach to software development, with a focus on security. The ideas here include not just patching, but fixing underlying issues, learning from mistakes and from other organizations, improving the skills of their developers and administrators. This is a holistic approach to ensuring security.

    This not only includes the software we write, but also the software products, platforms, libraries, etc. that we use. For many of us, this means the Windows host OS, the networking software and connections, http servers, vendor .NET libraries, and more. This also includes database software of all sorts, whether you use an RDBMS like SQL Server, perhaps a search service like ElasticSearch, a caching database like Redis, or some other NoSQL data store.

    The idea is that we not only code securely, but also make sure we patch our software. That’s a problem in many cases. In fact, MongoDB has had a security issue for the past few years that was well publicized but still affects many installations. Far too few people have patched their MongoDB databases, and it’s a problem. Data is exposed to anyone on the Internet, and it’s likely someone is taking advantage of this to access the bits. In fact, many hackers exploit flaws in software for which patches are often available. The updates just haven’t been deployed.

    Patching software is a challenge, and it seems that there is no shortage of ways in which companies deploy updates. Microsoft is pushing automatic patches. Some applications make these optional. Some apply changes in the background, some require downloads. If you use a service, then patches are seamless, but the changes may break functionality or force a new workflow on the user.

    Ultimately I think that we, as an industry, need to mature with the ways in which we patch software. Customers and clients should demand better, and the impact of applying patches should go down. Quality should go up, and while that has happened with some companies, I think we need better patterns and practices that are taught and adopted by more developers. Perhaps more people sharing their techniques, templates, and practices will help others become better at building and managing our software updates.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.5MB) podcast or subscribe to the feed at iTunes and Libsyn.