Voice of the DBA

Author: way0utwest

  • The Principle of Least Privilege

    One of the tenets of good security is that no person or process is granted more rights than it needs to accomplish a task. This is the Principle of Least Privilege, and is designed to ensure that your system is as secure, stable, and predictable as possible.

    For a SQL Server instance, this means that unless a person or process needs to perform instance level changes (configuration, patching, etc.), then they should not have instance level privileges. If a process accesses data in a database, there is no need to grant sysadmin privileges, even if they do need db_owner privileges.

    Many software packages require sysadmin access be granted, often without any justification. Developers build software as a sysadmin on their own workstation, or on a development server. They often don’t understand or realize that less privileges will still allow their software to work. As a result we find software running under elevated privileges, which can be a security risk if there are bugs or injection holes in the software.

    It’s hard to make changes later on, and it can be frustrating to argue with vendors that their software doesn’t need elevated privileges. The best way to combat this is to educate yourself and any developers you know about security.

    Adding login or user accounts does not require sa privileges. The securityadmin and db_securityadmin roles can be used. The rights to create objects in a database can be handled with granular permissions in a schema, or with the db_ddladmin role instead of db_owner.

    Work with limited rights when you can, and help other learn how to work with the minimum they need and we’ll all be more secure.

  • Annual Email Pruning

    Like most of us, I get a lot of email. I get subscribed to various services from different ads, events, etc. throughout the year. That’s fine. I understand how marketing works, and the reason that I get some free services, or some lower cost events is because of the marketing that comes through email newsletters or other information. The reason SQLServerCentral was able to grow and survive was because of marketing, and our email newsletter, which I’m thankful so many of you subscribe to on a daily basis.

    I like to think that the SQLServerCentral newsletters are interesting and worth your time to glance at. I don’t expect most of you to read them every day, but I do think that it must be interesting more often than not for your to keep subscribing. I know the Database Weekly ones are, and I hear from more than a few of you if we ever end up forgetting to send one out.

    Over the last few weeks, I’ve undertaken an annual tradition of weeding out some of the email advertisements I get. As new mail arrives in my inbox. I make a quick evaluation of whether or not I’ve been reading the other mail from this particular sender and if it’s valuable. If not, I’m unsubscribing.

    I do this periodically on Twitter as well, cleaning out my timeline if I find someone posting in there things that aren’t interesting enough for me to continue following them.

    Time is one of my most valuable resources. I’m willing to give vendors and people a chance to prove that they add something to my workday, but if they don’t, then I let them go.

    BTW: If there are things you’d like to see improved at SQLServerCentral or Database Weekly, let our webmaster know or post a note in the forums.

  • Better Licensing for SQL Server

    I don't know why licensing can't be simpler, but if it's not, then make it clear what is needed.
    I don’t know why licensing can’t be simpler, but if it’s not, then make it clear what is needed.

    People post licensing questions constantly at SQLServerCentral about the various scenarios and situations they face with their SQL Server instance configuration.  Many of the are fairly easy to answer, but more and more I read about environments that are complex. The problem is it is not clear from the licensing documents what purchases are needed.

    Multiple cores, virtual environments, and the restrictions for different editions of SQL Server mean there are often no simple answers. I know Microsoft is trying to earn a profit and these days hardware advances complicate matters, but I do think that Microsoft can make this much easier to understand than it currently is.

    There are lots of possible scenarios for systems, but publishing data on the Internet is easy. I don’t understand why the licensing documents do not include 10 or 20 possible configurations for SQL Server environments and explain what the licensing requirements are for each one. I bet that there are even some developers at Microsoft that can build a way for anyone to submit their own scenario, get it added to the list, and an answer published. I would guess there might even be able to build a notification feature for the submitter.

    It’s easy to contact a Microsoft licensing representative, but many of these people aren’t SQL Server experts, and often don’t understand some of the implications of complex environments. What’s worse is I have different friends getting different answers from Microsoft representatives.  By not using published scenarios and licensing requirements, and depending on employees’ interpretation of the documents, Microsoft is as likely to under charge as over charge customers. Neither situation is a fair one.

    I don’t mind paying for the software I use; I just want to know what the cost is before I architect a complex environment.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Virtual Benchmarks

    The consolidated server setup for the TPC-VMS
    The consolidated server setup for the TPC-VMS

    I noticed this week that the Transaction Processing Council (TPC) is working on a new benchmark designed to measure workloads across virtual machines: TPC-VMS. This benchmark builds on the existing benchmarks out there (TPC-C, TPC-E, TPC-H and TPC-DS) with the idea that companies want some idea of how various hardware and software might compare in virtual environments. In this benchmark, there are three systems consolidated onto one host running some type of hypervisor. If you are interested, you can read about the current benchmark, v1.1 (1.9MB PDF).

    Companies choose virtualization for efficiency reasons, sacrificing some stable, known level of performance from their systems. In many cases that trade-off isn’t a problem as we have many, many systems that are using only a fraction of their power. However as data professionals, we are often very concerned about the possible complications from virtualization. It’s our phones that ring and each of us that gets the blame when systems are not as responsive as users would like.
    This seems to be an ambitious undertaking from the TPC, and I suspect that more than a few hardware and software vendors will be nervous about submitting their wares for evaluation. The very nature of virtualization would seem to imply that as the load increases, the performance from any particular VM might vary from test to test. I will be curious to see how they present these results and how we can interpret them.
    I don’t know if the benchmarks will have any relation to the real world. As it stands today, the TPC results don’t seem to relate to actual systems in the real world, though they do confer some bragging rights for platforms. I’ve enjoyed seeing SQL Server in the various rankings, if for no other reason than to show it can perform at the same level as other RDBMS’s.