Voice of the DBA

Tag: security

  • It’s a Problem When a SysAdmin Can’t Log In

    I showcased a demo recently that looked at a potential issue with an application where a user used a simple update statement to gain privileged access to a system. That’s scary, and it’s a potential issue for many applications that handle their own authentication.

    The problem with this type of attack is that it can be hard to detect. A system administrator might not log in with their account for some length of time, during which the attacker has free reign in the system. However even if the administrator logs in, they might not detect the attack.

    How often have you entered your credentials for a system and it informs you that something was typed wrong. How many times have you locked out your account accidentally with too many bad password entries?

    Have you ever thought that your account was under attack?

    I know some people will have their account unlocked and retry their password, but I know plenty of administrators that would just reset their password.

    That’s bad.

    You should always check the login times for your privileged accounts. In fact, I’d like to be sure that you are using your privileged accounts regularly so that if someone were to gain access, you’d know it. Or if someone changed your password, you’d be aware.

    Treat your sysadmin account for what it is: a privilege, and a target for attack. We are seeing more and more attacks on our systems, and I expect the problems to only grow, not shrink.

  • Beware of Login Issues for Privileged Accounts

    In a recent post, I described an attack against a privileged account using a simple SQL Injection technique of updating data in a table. One of the things showed was an administrator using their user and password credentials, but being unable to log on.

    pwd6

    In this case, the administrator might easily assume there are mistyping their password, try again, and at some point reset their password.

    NEVER DO THAT.

    I mean, you might need to reset your password, but don’t take this lightly. If you are logging in with a privileged account, and you should do this sparingly,

  • SQL Injection Issues–Password Hashing

    I’ve got a demo for one of my talks that really highlights some issues we have with SQL Injection. It’s part of my encryption talk, and it goes like this.

    NOTE: I am showing a simple example here, not one that I would deploy into production. The concepts are similar, but this specific code is not designed or applicable for cut/paste into a production system.

    Imagine I have a simple table of users and passwords.

    go
create table UserTest
( firstname varchar(50)
, passwordhash varbinary(max)
);
go
-- insert passwords
insert usertest select 'Steve', HASHBYTES('SHA2_512', 'AP@sswordUCan!tGuess');
insert usertest select 'Andy', HASHBYTES('SHA2_512', 'ADiffP@sswordUCan!tGuess');
go

    I’ve got two users and a fairly strong hash of their passwords. I’m using the SHA2 algorithm, at 512 bits, and complex passwords. I’m showing this in T-SQL, though you could easily hash these passwords in the application layer and just store the values in the database.

    I create a simple proc that takes a username and a password as parameters.

    create procedure CheckPassword
   @user varchar(200)
 , @password varchar(200)
as
if hashbytes('SHA2_512', @password) = (select passwordhash 
                                 from UserTest
                                 where firstname = @user
                                )
  select 'Password Match'
else
  select 'Password Fail'
  ;
return
go

    NOTE: This is shown at the DB layer for simplicity, but having a user’s password transit the network in plaintext and be passed to a proc is a poor practice. It would be better to hash this and only send the hash to SQL Server.

    If I want to verity a user, I can do this:

    declare @p varchar(200);
select @p = 'AP@sswordUCan!tGuess';
exec CheckPassword 'Steve', @p;
go

    The result of this call is the password matches.

    pwd1

    If I try a different password, say the one for the other user, it will fail.

    pwd2

    That’s good. This is very similar to how many applications, including AD and SQL Server, validate users. However, here’s one problem with a simplistic implementation like this.

    Imagine that through SQLInjection, someone learns the structure of the table. Not hard to do. Now the data in the table is hashed, and there are lots of hashing algorithms. Certainly it’s a lot of work to try all different combinations of possible passwords, and algorithms to find a match. It’s possible and it’s a brute force attack.

    Here’s the data in the table.

    pwd3

    However, the hacker, Andy,  doesn’t need to decode the password. Imagine that the hacker creates his own account, which is probably a low level account. However the hacker runs code like this, substituting different accounts for “Steve” until a privileged account is found.

    pwd4

    Now the attacker does this. They use my (Steve’s) privileged account, with their password:

    pwd5

    The hacker (Andy), can now log in as Steve using his password. Any rights that are assigned to Steve are available for Andy.

    We have an attack without decryption.

    This is one reason that SQL Injection is a big problem in applications, especially those that implement some type of their own security. Solving this is slightly tricky, and I’ll talk about it in another post.

    One side note, the only way this is usually detected is if Steve logs in with his password. He’ll see this:

    pwd6

    Even then, unless Steve suspects an attack, he might write this off to a mistyped password, try multiple times and eventually reset his password without a second thought.

  • Encryption in Colorado Springs – Encrypting in the Application?

    Last night was my annual presentation at the Colorado Springs SQL Server User Group. I try to make sure I get down there at least once a year, and it’s been only once a year for the last few years. Far too busy, and I’m sorry for that, but I am glad I get invited to go down.

    I presented The Encryption Primer, and there were a few interesting questions asked. Always good to see people debating and asking questions.

    One interesting one from a developer – If I can perform encryption in the application, why would I do use something like TDE or column level encryption?

    To me, I prefer to do encryption as close to the source as possible. If I can do the encryption in the application front end, I’d do it there. It reduces the chances of having the data accidentally disclosed. I don’t have to worry about having data read across the wire, or in a backup tape, or anywhere else.

    However that takes time and effort. Developers are expensive, and they have to write good, solid, secure code in the application. They also have to write this encryption code in every application that accesses the database (reports, ETL, etc.).

    Something like TDE is much easier to setup and use. Column level encryption, while still coding, is centralized.

    It’s a balance, and one you need to consider carefully and thoroughly. It also helps to debate and discuss the decisions about what you protect, why, and what it costs.