Voice of the DBA

Tag: security

  • Am I a sysadmin? (or other SQL Server role)

    How do you check if you are a sysadmin? It’s fairly easy to do in Management Studio. You can go to Security \ Server Roles \ Sysadmin, as shown here:

    sysadmin1

    You right click sysadmin and click properties to get a list of sysadmins. You can do this for any role, and that’s the easy way if you want to verify permissions.

    sysadmin2

    What if you have an open connection to the server, say in a Query window or Powershell session and want to verify your role. There’s a function to help you: Is_SrvrRoleMember().

    If you execute something like this: 

    SELECT IS_SRVROLEMEMBER('sysadmin');

    You’ll get a one back if you are a member of that role, and a 0 otherwise. That will allow you to easily determine your current permissions, or check permissions programatically and continue on with your work depending on the results.

  • Brian Kelley on Security at SQL Server Connections

    Random notes from Brian Kelley’s security talk. Brian spent a number of years working a a security guy for a bank in the Windows and infrastructure teams.

    Networking, the UDP port is 1434. This is the listener and browser for SQL.

    Default Tcp1433 is the default port for SQL. What about TCP 2433? In SQL Server 2000, the “hide my server” button moved the listener to 2433.

    If you are a worthwhile target, a hacker might take weeks to dig through the access points for your servers.

    Those hallways conversations matter. You never know when someone is actively listening to get information about your internal systems. Tom Clancy is a perfect example. “The Hunt for Red October” contained a lot of classified information about submarines, Mr. Clancy was questioned by the CIA to determine how he learned things. He did a lot of research, listened a lot around personnel and put various pieces of information together to understand how submarines work,

    These are hacking tools and you should use them with caution and with permission in your organization. use them carefully.
    Nmap is a port scanner.
    Quest’s Discovery Wizard – will find sql servers
    SQL Ping – similar to the Discovery Wizard
    Perl, Python, PowerShell scripting
    Nessus and other scanners

    What do you check? Blank sa or simple sa password. Using “sa”, “dba”, “password” or anything like this is a problem, once someone can get into one server, they often jump to another one,

    What was the last SQL server vulnerability? I didn’t remember, but Brian told us it was MS09-004, which affected SQL Server 2000 SP4 and SQL Server 2005 SP2. It allowed access through a replication extended stored procedure.

    People hop from server to server, so it’s a good reason to have separate service accounts for each instance.

    The standard hacker methodology is to find a weak point, attach, and then expand your attack. Move from machine to machine, or grow to more privileged accounts. This is why you need to provide a series of barriers, enforce auditing, and be diligent on vulnerabilities.

    Third party apps are a problem, often with weak passwords, sometimes stored on the file system, if someone can get that file, they can attack your sql server.

    Lots of scary stuff. It paints a picture that makes it seem like you cannot protect your serveres. After 45 minutes of scary ways to attack, Brian goes into a series of ways to protect your servers. Firewall your servers at the network layer, and remember to cover domain controllers, backup servers, dns servers, and other servers that your sql server might talk to.

    A intrusion detection system behind your firewall is a good idea.

    Most issues come from user workstations. Limit the accesss their systems have to the server, remove netbios, file shares, etc.

    Pen test your systems. Run real world tests. Watch out for maintenance crews, cleaning crews, weekend open doors, etc. Watch out for backup tapes. They are a source of vulnerability. Data center tours are an issue, if you have a large data center.

    Watch out for open sessions, remaining logged in, your password is in memory and if someone can get to the server, they can get logged in users’ passwords.

    SQL Server and Windows 2008 are much more secure. Many of the known vulnerabilities from the past will not work. A good reason to upgrade past 2003/2005.

    Overall i think that a good firewall protects most of your systems, but there is s lot of education needed among both administrators and non IT people to keep secrets secret, be aware of conversations away from your office, and dont take shortcuts, most security isn’t hard. It might be a pain, but often putting up with a little inconvenience can greatly increase your security.

  • Do You Need A Safe Word?

    Should we implement "safe words"?

    I read this Forbes piece on the hacker “Kayla”, which led me to this correspondence posted when she (or he) hacked HBGary Federal. The transcript of emails is rather amazing  and a little scary. She manages to get access to the servers through a clever bit of social engineering against a security specialist.

    The whole plot depends on access to a person’s email account, but that’s entirely possible. Imagine creating a distraction and then stealing a target’s smartphone. Done cleverly, a busy executive or IT worker might think they lost the phone and spend time trying to track it down. Meanwhile a hacker has access to their email, sending who knows what messages out.

    As system administrators, or anyone with privileged access to systems or data, should we assume that an email request from a person for a password reset, or an access request to additional privileges is genuine? My thought is that we might not want to “trust” these systems and instead, implement some other type of verification method along with this trusted access. Perhaps we ought to call the person and verify the request if we know their voice, or require them to present their request in person. At one place I worked, we used to require someone to personally come to the IT office unless we were sure we knew that person’s voice. That’s not a perfect solution, but it could help increase security.

    Maybe we need a safe word for each privileged account. It could be a word we request from the user when they ask, or maybe it is an uncommon word that has to be worked into the request to help verify the end user’s identity. Ideally we would just feed the message into some system that would authenticate it, rather than allowing a technician to manually verify the safe word from some store of words.

    Security is hard, but and none of my suggestions is perfect, but as there are more and more script kiddies, hackers, and social engineering professionals, perhaps a little paranoia is a good thing. Perhaps making it hard to gain addition access, especially privileged access, is a good idea.

    Steve Jones

    The Voice of the DBA Podcasts

  • DBA Concerns

    Security is everywhere, or is it?

    I ran across a survey of Oracle DBAs that examined their concerns about security that I found it very interesting. Considering how much data from companies and governments supposedly resides in Oracle databases, it’s shocking to see how large a percentage of the DBAs don’t know or understand what security measures are being used in some cases. It’s also surprising to see that so many of the threats these DBAs perceive are internal threats or problems with a lack of some sort of process.

    It makes me wonder if a survey of SQL Server DBAs would be any different.

    My guess is that it would not. SQL Server would have a good percentage of people that don’t encrypt data, a good percentage that don’t apply patches or don’t know what processes are being used to secure and protect data. I would expect that a lack of process, lack of priority from management, and other internal threats would be concerns as well.

    As DBAs, I think that the integrity of the data we deal with is paramount. We must comply to our own version of the ACID principles for databases by ensuring that data is secure, available, intact, and recoverable in the event of a disaster. If we cannot guarantee the safety of the data, we aren’t very good custodians.

    The best data professionals I know leave nothing to chance. They don’t depend on their own skills or memory. They implement a process everywhere they can, and automate these processes as much as possible to ensure they are followed. As you learn how your environment should function, use code to assure yourself that the processes are followed. Then use other tools available to verify those processes are actually running correctly.

    The best DBAs aren’t necessarily smarter, or more talented than anyone else. They’re often just more methodical and focused, ensuring that the work that needs to be done, gets done. Usually by coding the server to handle most things on its own.

    Steve Jones

    The Voice of the DBA Podcasts