Voice of the DBA

Tag: security

  • How Application Roles Work in SQL Server

    One very interesting security technique available in SQL Server is the application role. It’s an interesting way of applying security to a user, and perhaps a way of preventing users from accessing data with unauthorized applications.

    An application role is a role just like any other role. It has a default schema, and it can contain specific rights on securables. Below you can see I have an application role that has rights to a specific table in my database. In this case, at the bottom you can see this table has SELECT rights.

    approle2

    I could grant other rights, but in this case I haven’t.

    How is this different than a normal role? On the “General” page of this dialog, you can see something different.

    approle1

    In a normal (database) role, there are members on this page. Here we just have schemas and a password. This password is what gives you the power of this security feature.

    If I connect as a normal user to my database and issue a SELECT, I get this:

    approle3

    This user has no rights in this database for this table. However if I invoke the application role, I can select from the table.

    approle4

    Application roles are assigned to a user when the user executes sp_setapprole, with the role name and the appropriate password. Previous permissions are removed, and these permissions are granted.

    This can be very handy, as assigning a user rights to connect to an instance, but no rights in a database allows them to connect, but not perform any data manipulation. This means a user connecting from Access, Excel, etc. cannot work with data.

    If the user connects with an application that is configured to execute sp_setapprole after the connection, if there are rights for DML assigned to that application role, the user can perform the data manipulation needed for users.

    It’s not a perfect security mechanism, and it requires the password to be kept secret along with application programming, but it can be a good way to prevent users from working with your data outside of an application.

  • Locking Your Disk

    disk drive
    You should be protecting your disks with encryption.

    This editorial was originally published on Feb 28, 2008. It is being re-run as Steve is on holiday.

    With the tremendous growth in disk sizes and the trend towards more and more people using laptops, someone sent me a note about protecting that data that got me thinking. The SQL Server space has grown tremendously, and not just up. We have SQL Server Express and SQL Server Compact Edition, both of which are designed to run on smaller devices, with (supposedly) smaller data sets. However my phone has more storage than quite a few hard drives I’ve owned in my life and my new laptop rivals the storage in my 3 year old desktop.

    It seems that often that administrators don’t think about the data that gets moved off their servers and with new replication and ETL technologies, a significant amount of data might be duplicated on other instances of SQL Server away from your primary database.

    And your boss might be expecting that you’ve ensured its security.

    So this Friday, I had a poll to see who’s thinking about the issue.

    Do you use disk encryption?

    Or do you think it’s important? Should you be using it for your laptops?

    My laptops really just carry my writings, articles I’m editing, books, etc., and no financial information. I do have Password Safe on them with databases, and I suppose that someone could crack that if given enough time. However since I’m not carrying around data that involves other people, I haven’t worried about it.

    I used to have an encrypted disk when I worked for JD Edwards. At first I thought it was a pain, but after a month or so, it wasn’t a big deal. Except when I had booting issues one morning. Talk about being anxious for a few hours. Fortunately the manufacturer has included utilities and a help desk person was able to get my disk unencrypted and then re-encrypted again.

    Disk encryption is a good idea and it definitely protects accidentally releases of data. If you carry around client data, financial information, or identity information, I’d highly recommend you use some type of encryption to protect the data. However, as this article shows, that might not be enough.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed: or now on iTunes!

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • Better Secure Hashing – SHA3

    I’ve been giving an encryption talk over the last year, focusing on educating DBAs on the various features and capabilities available in SQL Server. One of the things I note in the talk is that SHA1 is not considered secure. You should avoid SHA or SHA1 if you are using SQL Server 2012. If you’re on an earlier version, you don’t have a choice.

    Hopefully we’ll have more choices in the future. NIST has selected an SHA-3 algorithm as a new standard. This should provide more security, though there will be some time before we find out if there are any problems or potential attacks against this algorithm.

  • Unprotected Queries

    SQL Injection
    SQL Injection is a constant problem in many applications.

    Today’s editorial was originally released on Dec 4, 2007. It is being republished as Steve is at the PASS Summit.

    This is absolutely amazing;over half a million database servers have no firewall. How can you put up a database server, SQL Server, Oracle, DB2, even MySQL, without a firewall?

    How can you put any server on the Internet without a firewall? Even most home routers enable a NAT router and basic firewall these days, not allowing connections in by default. In the last 5-6 years, the technology has been widely available, even to uninformed home users, to not deploy any system on the Internet without protection.

    So how do these servers get out there? Are these development systems? Are people opening 1433 so they can test an application or access their remote SQL Servers? That’s what I suspect. Many developers I know are optimists and they don’t expect people to be pinging their servers or accessing their systems in any way other than how it’s designed.

    We’ve been hacked here at SQLServerCentral.com a few times over the years with SQL Injection techniques, but never to my knowledge with an attack directly against our SQL Server. For a long time we did have our SQL Server exposed, but not on 1433. It was on a high, random port that was unused by any other service and we had strong passwords on accounts. It was a convenience service, we had login tracking, and I never saw an unexpected attempt in our logs.

    However if you run a corporate SQL Server and need to stick servers outside your firm’s firewall in some type of DMZ, at least close off port 1433 to anonymous access. Go spend the $100 out of your pocket for a small router that can at least protect your servers with basic NAT and prevent traffic from getting directly to your database server. It might not be the best solution, but it’s better than nothing.

    There’s no excuse these days for putting a server out on the Internet without at least basic NAT protection. Some type of router or firewall should protect every server, and probably every computer, and only allow those services that are really needed. For most servers, this is port 80 and nothing else. Allowing access to SQL Server, RPCs, or any other port that’s not meant for anonymous access, is really stupid.

    And if you can’t figure out a way to securely make your service available to partners or customers, then you should hire someone that can. There are plenty of networking professionals out there that can help you set things up correctly.

    Know your limits, ask for help, and don’t jeopardize your company’s security because of ignorance, pride, or laziness.

    Steve Jones

    The Voice of the DBA

    Wakamojo

    The podcast feeds are now available atsqlservercentral.podshow.comto get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted. You can get feeds from there.

    Today’s podcast features music by Wakamojo, the Kansas band featuring our very own Adam Angelini, DBA from the heartland and SQLServerCentral.com community member.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.