SQL Server Security Checklist

This month we are going to look at the subject of security. The article
is not an in depth tutorial, and is in no way comprehensive. Instead, what I
have tried to do is to simply highlight some of the areas where many people (myself
included) commonly leave gaps in their security.

SQL Server Passwords

  • When
    using SQL Server security, make sure there are no blank passwords – a simple
    query will reveal which users have not set a password:

             select name from master..syslogins where password is null 

    Watch out for NT logins, which of course have no password!

  • After
    the SQL Slammer virus experience, nobody should be left with a blank password
    for their sa login

  • Remove
    the guest login – it allows unauthorised access by default.

  • Choose
    a strong password for the sa login, and encourage your users to do the same.

  • User
    IDs and Passwords in user tables are bad news, the data is in too public a
    place. Cryptography is Maths-PHD and million-pound-supercomputer territory and
    "home made" encryption is usually weak. If one of your developers
    reckons they can write their own uncrackable encryption scheme, they should be
    working for the spooks.

    Integrated security

  • Choose
    strong passwords for all local and network logins. If an intruder can guess a
    machine’s local administrator login, they will own your server.

  • Do
    your NT administrators really need admin rights inside your SQL Server? Check
    that no administrator groups are accidentally granted rights via Integrated
    Security.

  • Lock
    down the extended stored procedures – you do not want users running command
    shells with administrator rights on your servers.

    Database rights

  • Nobody
    needs access to the master database to log in, and most people do not need
    access to MSDB and the scheduling engine. Lock them both down.

  • Grant
    only the minimum rights to a user in order to get things done. Understanding
    ownership hierarchies helps here: If a stored procedure accesses a table, users
    do not need rights on the table in order to run the SP successfully, provided
    that the ownership of both SP and table is the same. The same rule applies to
    views and their underlying tables, and to nested stored procedures.

  • Always
    grant user rights at role level, and make sure all your users are grouped
    according to functionality.

  • Never
    allow your developers to produce clients that log in as ‘sa’, and never let
    them develop as ‘sa’, even in an environment where security is not such an
    issue. All too often apps are developed using sa privileges, and you find out
    just before they go-live that these privileges have magically become necessary
    for the system to work. Take my word on this: "we will fix it in the next
    release" is right up there with "the cheque is in the post."

  • How
    many times have you heard the story "No, you cannot change the password,
    it’s hard coded"? Passwords are meant to be typed in by authorised
    users, not hard coded into programmes, stored in .ini files or held in the
    registry. Use Integrated Security if users feel the need to connect without
    typing a password.

  • Encourage
    your client developers to build security into a database and client at design
    time, not bolt it on as an afterthought. Developers always want to concentrate
    on functionality, which is understandable, because that is their job, so it is
    up to you to get them thinking about security at an early stage.

  • Consider
    using application roles to enhance security.

    Patching

  • A
    touchy subject if ever there was one. It’s easy to say that you should always
    apply the latest SQL Service Packs, SQL Hotfixes, Windows Updates and what-have-you,
    but there is a problem here: You cannot just install any old software into your
    mission critical environment as soon as it is released. What if it breaks
    something? How do you roll it back? If you apply a patch without testing it,
    and it breaks your environment, you can bet Microsoft will not be sending you
    compensation money any time soon.

  • Sometimes,
    good practice helps you eliminate the need for applying patches in a hurry: If
    you did not have a blank SA password in the first place, you would not need the
    Slammer hotfix.

    Network Security

  • Taken
    a backup of your database and left the file lying round on the network for a
    while? Who can see that network drive? Anyone who can, can simply pick up your
    file and restore it onto another instance of SQL Server and voila – there goes
    your data!

  • Are
    your development environments as safely guarded as your live ones? Or, is
    sensitive data ported back to your test and development environments where half
    the IT department can see them.

  • If
    you are opening a test database to other people, is there sensitive data in
    there that you might want to encrypt first? Personnel databases that list people’s
    salaries are a concern in every company.

  • Off
    to a leaving drink? They might have been the nicest, most reliable people in
    the company, but you really should disable or remove their account before you
    get that first beer down your neck.

  • Where
    are your backup tapes? Is your off-site backup provider as security-conscious
    as you would like them to be?

  • It
    is not just SQL Server and Windows security you need to worry about. Got IIS? Linked
    servers? Check that those machines are properly secured so they cannot act as a
    back door into your system. Unattended PCs left logged in are a major security
    hole. Educate your users, enable passwords on screen savers or implement
    inactivity timeouts in your applications.

    The dark room

  • Security
    isn’t all about software – make sure that your server is in a locked cabinet,
    in a locked room, with proper access controls. Organised gangs make a living
    breaking into server rooms and stealing the kit. If somebody just wants your
    data, all they need to carry away is your nightly backup tape, or your
    hot-pluggable disks.

    »


    See All Articles by Columnist
    Neil Boyle

  • Neil Boyle
    Neil Boyle
    Neil Boyle left school at the age of sixteen thinking that computers were things that only existed in Star Trek. After failed careers as a Diesel Mechanic, Industrial Cleaner, Barman and Bulldozer Driver he went back to college to complete his education. Since graduating from North Staffs Poly he has worked up through the ranks from Trainee COBOL Programmer to SQL Server Consultant, a role in which he has specialised for the past seven years.

    Latest Articles