Voice of the DBA

Tag: security

  • Am I a sysadmin?–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I was doing some security testing and wondered if I was a sysadmin. There are a few ways to check this, but I thought there should be a function to tell me.

    There’s this code, of course:

    SELECT
    ServerRole = rp.name,
    PrincipalName = SP.name
    FROM sys.server_role_members rm
    Inner JOIN sys.server_principals rp
    ON rm.role_principal_id = rp.principal_id
    Inner JOIN sys.server_principals SP
    ON rm.member_principal_id = SP.principal_id
    where sp.name = SUSER_SNAME()
    and rp.name = ‘sysadmin’

    That lets me know if my login is a sysadmin. However, there is a function that you can use. IS_SRVROLEMEMBER() is a function that you can use, passing in a server role as a parameter. The code I’d use to check on sysadmin membership is this:

    SELECT IS_SRVROLEMEMBER(‘sysadmin’);

    If I run this, I get a 1 if I’m a member, or a 0 if I’m not.

    2016-04-12 11_38_34-Settings

    Using this function in your code allows you to make decisions based on role membership for the users involved, and perhaps alert them of needs for certain rights.

    SQLNewBlogger

    This was a quick one, really about 10 minutes to organize and write. Most of the time was writing the code to join system tables. If you tackle this subject, talk about how you  might use this, or where this type of check could come in handy in your code (maybe before taking some action).

  • The Pen Test

    We need something like the Air Force project seeking network vulnerabilities for our database systems. While there are tools that can help from the outside, but we need SQL Server specific tools. Why don’t we have a Best Practices Analyzer for SQL Server 2014 (or 2016)? It seems as though this is one of those tools that we should have available for checking against instances and determining if there are obvious vulnerabilities. Being able to add custom extensions for particular needs would also be nice.

    I guess if this is important to your organization, you’ll pay for a tool (or a pen test), but the problem is far, far too many organizations just don’t take security seriously. It seems that despite the huge costs incurred for not properly securing computer systems, the penalties aren’t nearly enough to get every company to patch their systems and write secure code. Most just hope they’re never attacked, or if they are, no one finds out.

    I’d really like to see some tools, or maybe just a best practice process that would allow system administrators to test their own systems. I found this post from PenTestLab, and I think it’s great. The article is a good start for what to look for and what issues might exist. However even better than this might be a series of automated items we can run that showcase to management, developers, and DBAs when they’ve made a mistake that potentially opens a system to attack. Maybe best of all would be getting software vendors to actually run these types of tools against their applications and provide some proof they’ve built secure code.

    I don’t hold out hope that security will improve anytime soon, but I do hope that more system administrators learn about what issues potentially exist and check their systems for potential vulnerabilities before someone else discovers the issues exist. Lastly, I hope they all share what they learn.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Changing the sa Password with SQLCMD

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

    I wrote about using SSMS, but that’s not always convenient. If you need to change this remotely, perhaps in a hurry, SQLCMD is a quick way to do this.

    SQLCMD is a command line tool, so open a command prompt.

    2016-04-06 12_47_33-Photos

    Run SQLCMD and connect to your instance as a sysadmin. If you have any doubt, you can enter the query from my previous post to check your connection.

    Once you’ve connected, you can issue this code:

    ALTER LOGIN [sa] with PASSWORD = N‘Sup#rAmaz!ngP@$$w0rd’

    This is the code that will change the password for the login specified, even if I’ve logged in with a different account.

    Once I’ve done this, test the sa login from a new session and verify it works.

    SQLNewBlogger

    Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

    References

    SQLCMD – https://msdn.microsoft.com/en-us/library/ms162773.aspx

    ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx

  • Crack that Encrypted Data

    Ransomware appears to be gaining some traction as a new trend. For awhile in my career, it was virus programs designed to send to all your contacts. Then it was infections to use your computer as part of a bot net. Now it’s encrypting your files and demanding a payment to get the password.

    I’m starting to think that a)I need to ensure I have solid, better backups on all devices, and b) I should pay attention and be aware of decryption programs. I’d love to say that I could build a decryption program, like someone did, but as much as I’m interesting in encryption and study it, that’s a little out of my skillset wheelhouse.

    I’m actually starting to think that this might be a way that people in communities, like the SQL Server community, can help each other. We can be aware of potential ransomware threats, like the one that hit this hospital, and potentially share ways to recover from the incident, or even decrypt the drives. In fact, I suspect it might be worth keeping a system handy to practice decryption techniques, if you can determine the attack vector.

    I’m sure many organizations wouldn’t want to share details of attacks and infections, but this is exactly the type of information that we, as data professionals, should be sharing. It’s incredibly difficult to keep up with all the threats and attacks, not to mention the techniques to recover systems. I’d urge all of you to ask your employers if you can at least help others, even if you can’t disclose how or where you gained the knowledge. If nothing else, the information needs to be shared more publicly to allow us to better protect our systems and be effective custodians of data.

    Steve Jones

    The Voice of the DBA Podcast

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