Voice of the DBA

Tag: security

  • The Danger of xp_cmdshell

    Securing a computer is a challenge. There are all sorts of potential issues in every platform, and ensuring safety for your data can be less a reflection of your ability and more the good fortune there isn’t a focused effort to attack your systems. However, we certainly also face issues with inside users, many of which may make mistakes that are accidental more than malicious. It’s for these reasons that we look for secure by default applications and a reduced surface area for any system.

    Many people refuse to turn on xp_cmdshell as an option for scripting in SQL Server. This is disabled by default, and quite a few DBAs are glad of this setting. However, there are plenty of people that think xp_cmdshell isn’t a big security risk. There are certainly ways to mitigate the usage by non-privileged users, and this can be a tool that is very handy for accomplishing work without a lot of development time.

    This week, as security issues become more important to us all, I’m curious how you feel.

    Do you think xp_cmdshell is dangerous?

    I have to admit that I’m torn. I don’t think this inherently dangerous. It does open up some attack vectors, but the last few versions of SQL Server have allowed some limitations, so I would enable this if needed to solve some issues without too many concerns. However, I wonder if many of you feel the same way.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Encryption Keys Matter

    Perhaps the importance of protecting encryption keys is even greater than we realize. It appears that the NSA and the US Government have been able to read encrypted traffic for some time on the Internet, perhaps for most of the last decade. There was an exploit in Cisco PIX routers, which I’ve used in many companies, that allowed the NSA to gain the encryption keys used by VPN traffic. In fact, it is likely the NSA could actually penetrate the networks on either end of the VPN with those keys.

    Some of you may be against back doors for governments in encryption products, and some of you may be for allowing governments access with legal protections. However, the most disturbing part of this for me is that no organization knew their communications, supposedly secure, were being intercepted and read. The nature of the digital world is that exploits can copy information without the holders of that information being aware of the effort. This is much harder with physical items, where the movement of an object from one person to the next is easily noticed.

    One of the very difficult things with protecting the data in our databases is trying to understand when someone has actually retreived information they shouldn’t. This is much more difficult than just tracking changes to data, which is more straightforward. We have auditing mechanisms that easily track changes to data, though most of us don’t have this set up or configured to catch all changes. In practice, that might be good enough to prevent data quality issues, but it doesn’t necessarily protect data from read disclosure.

    Monitoring what information is accessed is far harder than tracking changes. Do you know if someone in Sales is accessing a single row with customer details or the sensitive information for all customers? Can you tell when a request is legitimate for an application or if there’s an unusual query that might be seeking massive amount of data for download? Those are hard questions, and ones that I think can only be handled by a large amount of activity monitoring along with machine learning assistance to look for patterns in user activity. Other features like Row Level Security can help limit the inadvertent mistakes made by developers or users, but not necessarily prevent a single user, especially a malicious user, from querying information.

    Apart from the activity issues, we should ensure that where it is possible, we should be using encryption to prevent accidental disclosures outside of our applications. I think Always Encrypted has possibilities for the database, but the key management for this, as well as key management for VPNs, disk encryption, and other protection mechanisms, needs both more maturity and the open disclosure to prevent back doors from being included in products. We also need more maturity in our software development that takes the implementation and protection of encryption mechanisms seriously.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Separate Accounts

    This editorial was originally published on Aug 10, 2012. It is being re-run as Steve is on vacation.

    Many years ago I worked in a small company that only had about 5 or 6 servers. We had one system administrator whose job it was to manage all the servers. One day our sysadmin was on vacation when there was a problem with the Exchange server. One of the other developers worked on the system and ended up fixing it, but changed the service account password while doing so. The next day I walked into the office to find a group of people stymied as to what was wrong with the development server and version control system. Everyone claimed they hadn’t changed anything on that server, and they were right. However our admin used the same domain account for all servers, including my SQL Servers. I changed the SQL service account that day.

    One of the recommendations that I learned a long time ago, and one that I make regularly, is that every SQL Server instance should have a separate security account. In that case, I had separate accounts created for each database instance, and for each SQL Agent instance. We used long, random passwords that were never stored, and if we needed to access a password, we just changed it. That kind of flexibility and separation prevented any crosstalk issues between services, and it allowed us to easily alter permissions or passwords for one service without affecting any others.

    The other day I saw someone recommending a single service account for all SQL Servers. Someone else recommended a single account for each version of SQL Server, using separate accounts where it’s really needed. That’s a better recommendation, but I still prefer completely separate accounts. I know that some security groups don’t like that, but is it that big a problem? This Friday I wanted to ask you about your experiences.

    Do you find separate accounts for each instance (or Agent) to be a security or administrative issue?

    I’m not sure why this is unwieldy. Service accounts rarely change, and you could easily script changes to a group of accounts with PowerShell or some other tool. Once I set a service account, the only thing I might ever do later is alter the permissions to add access to a folder. When that happens, I definitely want to have separate accounts for each instance.

    Steve Jones

  • Security Leaks from Websites

    One of the main issues with connecting databases to the Internet is that if a hacker finds a way to get access to the database with credentials, perhaps using a well known account (*cough* sa *cough*) and a weak password, they can get a lots of data. However, even if your database is not on a DMZ or addressable from the Internet, chances are some application that accesses your database is connected to the Internet. Websites are a perfect example of this.

    That means our data security is controlled by the security of the web application, and the security coding practices of our developers. That’s a scary in many cases, primarily because we, as an industry, don’t do a good job of actually sharing secure coding practices and habits widely. As much as people talk about and publish information about writing secure code, this doesn’t get spread around to many developers, who have years and years of bad habits. Even here at SQLServerCentral, we don’t have nearly enough information on SQL Injection, which is something I need to work on.

    However, even if you aren’t exposing your database server and aren’t vulnerable to SQL Injection, is your data secure? I read a rather sad article from Troy Hunt that looks at how personal data can be leaked. Far too many applications don’t follow secure information handling practices and unintentionally allow information to leak. Perhaps the most common item is verifying whether an account or email address actually exists or not in a login or password reset box. There are other examples, including not properly securing an entire page with SSL, allowing a simple thing like an order number to return user details, or even allowing someone to reset a password by guessing the answer to a security question.

    These seemingly benign issues often expose real vulnerabilities. I’m sure many managers and even developers would say that a hacker wouldn’t sit at a web page and try lots of email addresses. That’s probably true. However, does your web application have limits for how often someone can submit code? Extremely powerful scripting tools like Perl, Python, and PowerShell mean that a user could make submissions to your website in an automated fashion, retrieving the code and parsing it to find details.

    Most attacks on a system aren’t of the Hollywood movie variety. A hacker doesn’t find some hole and miraculously gain access to data in a few minutes or hours. Instead, a malicious user (outside or inside your organization), might make a series of attacks across days or weeks. They might try millions of emails or order numbers, gathering details and planning their future attacks that will actually reveal sensitive information. All those tools that make your job easier as a developer, or allow complex data analysis, can work just as well against your database as they do with your data.

    Steve Jones

    The Voice of the DBA Podcast

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