Voice of the DBA

Tag: security

  • Code I Can’t Live Without–T-SQL Tuesday #104

    tsqltuesdayBert Wagner has a good invitation this month, a T-SQL Tuesday question about code, specifically code you can’t live without. I’ve got my thoughts below.

    This is a monthly blog party, and you can participate. Write a post on this topic, and publish it on your blog. Drop a comment on Bert’s post. You can read the rules on his invitation, but you can really post anytime. It’s fun and good for your brand.

    The Critical Code

    I’ve managed lots of instances in my career. Some mission critical, some just important, some not important to me or the business, but important to someone.

    One thing I’ve found is that there are plenty of common things I do on most systems. I’ve had lots of code that I’ve written to manage all aspects of DBA work, from backups to maintenance to monitoring. I’ve had routines that handled all sorts of security or auditing.

    The code that I can’t live without, isn’t my code, but I’ve used it on probably every instance I’ve managed at some point. The code is actually from Microsoft, and it’s indispensable.

    The code is sp_helprevlogin from this support article.

    While I tend to be distrustful of keeping passwords the same for too long, and I usually don’t attempt to recover them in any way, just reset them. There are cases, however, when we’re moving an app, failing over to DR, or recovering some system and we need to keep the password the same.

    At least in the short term. When systems are down, I need them back up, and I can argue about a password reset later.

    This has been the most useful piece of code for me, and one that I think most of you could use as well.

  • Setting Up Pi-Hole.Net

    I saw a tweet a short time ago from Drew Furgiuele about pi-hole.net. He noted this was a near use of a Raspberry Pi, and I wanted to give it a try. I got one awhile ago, and used it for a few things. We started with a media streaming device, but when I got a Fire Stick, this wasn’t needed or used. Then I tried a Bluetooth audio bridge to the stereo. It wasn’t as reliable as I’d like, and it added complexity to turning this on, letting it boot, etc.

    As a result, it’s been sitting a bit as I thought of some other project. The pi-hole seemed like something to try, so I decided to give it a go. It was pretty easy, and it blocks some ads. Not all of them, but

    First, I grabbed the latest release, Stretch, of Raspbian. I chose the lite version, since this will be headless. I downloaded that and also grabbed Etcher to flash the card. This took a bit, but I let it run in the background as I did other things.

    From there, I used Explorer to connect to the drive and create an empty “ssh” file. I read this tutorial, and ended up just creating a new text file in Windows, and removing the extension.

    Once this was done, I took the device up and plugged it into the router, both the Ethernet and the USB cables. This booted and connected the device. Back at my machine, I used Putty to connect. I did have to upgrade Putty to get the proper encryption level, but if you get a new copy, you’ll be fine.

    2018-06-14 14_00_42-pi@raspberrypi_ ~

    A few people pointed out ssh is in Windows now. Great, that works, I like Putty.

    Once connected, I ran the curl command to get pi-hole installed. It was fairly simple, and once installed, walks you through some basic config and networking. I use the 192.168.1.0/24 network that most people do, but my DHCP is set to give out addresses from 2 to 199. 200 and above are statics for me, so I configured this to use one.

    After that, I got a password for the device and I went to my static IP/admin to see the Pi-hole dashboard. I logged in and I could see traffic. This is from the next day.

    2018-06-15 09_11_16-Pi-hole Admin Console

    It’s an interesting project and I’ll see how this goes. I could add a other software to this device, and I may. We’ll see.

    I’m not against advertising, but there are some shady sites and some data gathering. Right now, a lot of blocks are for some telemetry service, which is fine. I can’t get info on it, so I’m guessing it’s not legitimate.

    In case you are wondering, there are lots of queries to telemetry.servers.getgo.com that are being blocked.

    If you want to try it, this is a cheap experiment, and you can also play with Linux, some hardware, and SSH.

  • Republish: The Opportunistic Hacker

    Still in Ireland: The Opportunistic Hacker.

  • Getting All Users

    I saw someone that wanted to get all users from all databases on their instance. Seems like that ought to be simple, right?

    The user wanted to use sp_MSforeachdb to query users, but wanted a single result set for all databases.  Why you need this, I’m not sure. I guess some auditing report. Or maybe looking to clean up and remove unnecessary users?

    In any case, this turned out to be easy with a caveat. It’s not in one single statement..

    DECLARE @MyUsers TABLE ( dbname VARCHAR(200), principalname VARCHAR(200), principalSID VARBINARY(MAX))
      INSERT @MyUsers
    
EXEC sp_MSforeachdb 'select ''?'', name, sid from [?].sys.sysusers'

    SELECT top 1000
      *
      FROM @myusers

    Use the ? in the query to get the users from every db. Then store this in a table that you can pull back all the data.

    This worked for me, though since I have some database names with a hypen (-) in them, I needed the brackets to get this to work.