Password Managers

Password managers. You want one. Let’s do this in Q&A format, shall we?

Q: WTF is a password manager and why do I want one?
A: Have you ever forgotten a password? Do you use the same password on every site? Is your password “Ihave2manypasswordsOMGWTF!” or something like that? 😉 If so, you want a password manager. A password manager is a secure place to store all your passwords.

Let’s break that down, shall we? (A: Yes!)

Q: How does this help me if I’ve forgotten a password?
A: If you’ve ever forgotten a password, you know how OMGANNOYING that is. It happens. A lot. And most sites have “password recovery” options, but if you’ve forgotten the password to your recovery email, that doesn’t help. If you have a password manager, it remembers your passwords for you and you can just fish it out and paste it in, or, in some cases, get it to autofill. (Of course, you need to put the password in the password manager before it can remember it for you.)

Q: Yes, I use the same password on every site. What’s wrong with that?
A: If hackers manage to compromise your password on one site, they go to other sites and enter your username and password on those sites, too, trying to hack them as well. (Go here and enter your email address. They’ll tell you if your email address is associated with any hacks. Then change the password in the pwned place and every other place you use the same password. UGH!)

Q: Okay, okay. I see what you’re talking about. Is this hard or annoying?
A: Not really. How you get started depends on what password manager you’re using.

Q: Uh. I have a choice? How do I pick?
A: It depends on several factors, including what operating system you use on your computer (Windows, Mac, linux, iPad) and how you use your computer.

Q: How I use my computer? What?
A: Yep. If you mostly use a web browser, you might want a password manager with browser plugins. If you use the computer lab at college a lot, you might want something portable. If you’re like me and hiding sql account recreation scripts and crap in your password manager, you probably want something that does attachments. If you want it to sync to your phone, or across devices, that’s something to consider. If you’re super-paranoid like me, that might be a factor in what you choose. But seriously, most well-known, reputable password managers are good (you probably don’t want to download Bill and Ted’s EXCELLENT Password Manager). LastPass, KeePass, 1Password, and Dashlane are probably the best known. I’ve also used SplashID because a former employer bought me a copy. They’re all fine.

Q: Whatever. Pick something for me?
A: Okay, the two I’m most familiar with are KeePass and LastPass. If you use other people’s computers a lot, or the college computer lab, or don’t have admin rights on your computer and/or aren’t allowed to install things on it, or are just a paranoid weirdo like me and/or enjoy tinkering, you want KeePass. Otherwise, you probably want LastPass. LastPass is a browser plugin that automatically syncs across your devices, including your phone. (You can sync KeePass manually using Dropbox, a thumb drive, or a cable to your phone, or even install third party browser plugins and the like, but it requires more human intervention. LastPass is dead simple.)

Q: How do I get started with LastPass?
A: Download and install the browser plugins. Come up with an amazing password. I recommend going here for something easy to remember, and then writing it down on a piece of paper (I know) and hiding it somewhere secure after you’ve memorized it. Then go to a bunch of sites and log in. If you’re using the same password everywhere, take this opportunity to change those passwords to a bunch of random character strings that LastPass will generate and remember for you. Install the browser plugins/software on all your devices. LastPass will sync all your passwords and back them up for you. Don’t forget your LastPass password! LastPass doesn’t know it. (That’s why I said write it down on a piece of paper and hide it somewhere secure.) There’s a recovery procedure, but you need access to a computer that was running LastPass before.

There are other security features, like two-factor authentication and lockout by country, which I highly recommend enabling. (By the way, for two-factor authentication I like Authy, which is available for Android and iPhone and more and backs up your secrets for you–give Authy a good password that your password manager generated for you.) But if you were using the same password for every site, you’re already a million times more secure than you were. Security is a continuum, not either/or.

Q: How do I get started with KeePass?
A: Download and install the software–the main site is Windows but there are ports for Mac and Linux, as well as Android and iPhone/iPad. (If you use school computers a lot, or other computers you don’t own, or if you’re not allowed to install software, or if you share your computer, use the portable version and a thumb drive. Otherwise, the full install is fine. They’re pretty much identical. If you have a Mac at home and use Windows at school/work, the various ports can read each other’s databases so that’s fine.) Come up with an amazing password. I recommend going here for something easy to remember, and then writing it down on a piece of paper (I know) and hiding it somewhere secure after you’ve memorized it. Launch the software and create a new database using that amazing password you just came up with. Then start entering passwords (boring, I know!), taking this opportunity to change them to random KeePass generated strings if you’re using the same password everywhere. My KeePass is super-organized in subgroups (because I imported entries from four different password databases, yikes, and also because I’m just like that) but you can just throw them all in there willy-nilly if you prefer. It’s up to you.

DO NOT FORGET YOUR KEEPASS PASSWORD. There is no higher power that can recover it for you. (Like I said, write it down, memorize it, hide the written down password somewhere secure.)

Now you need to worry about backing it up. (Because you don’t remember any of those random strings, yes.)

If you’re already backing up your computer, make sure your password database is in a location that’s being backed up and you’re done. If you’re not, consider backing up your computer. In the mean time, you might want to set up a thumb drive with the portable version, copy the database over, and use the synchronize feature to keep them the same. (Tip: You probably want one database, not a work database and a home database, because then you need to remember what’s stored where, and if you end up working from home or taking a break at work you might end up with the wrong database. KeePass has a “synchronize” feature that helps.)

There’s more here–this is an excellent guide. I personally don’t use Dropbox or other cloud providers to sync files because I’m a paranoid freakazoid I have super-secure scary ass shit in mine. But if the livelihoods of everyone employed at an entire company and the financial lives of many customers don’t depend on the security of your passwords, and/or something happening to your passwords wouldn’t be, as they say, a Resume-Generating Event, it’s probably not as big a deal. Security and convenience are enemies, and you need to find a balance that works for you so you’ll stick with it.

Q: What if I try one of the password managers and don’t like it?
A: Try a different one! (Import from LastPass to KeePass/Import from KeePass to LastPass, although if you have a very small number of passwords it might be easier to just install the plugins and log in.) Think about what, specifically, you don’t like and go from there. (If it’s ALL TOO HARD OMG try LastPass.)

Q: Security and convenience are enemies?
A: Yes. Enemies. They really hate each other. They’re not, like frenemies that have the occasional hatesex or anything, they want each other to die. (You probably don’t want one of them to die, alas.)

Leave a Comment

Filed under written for nontechnical friends

How to route all Opnsense traffic through your private VPN

This is for your home network, yes.  I tried several how-tos, and basically had to figure it out for myself.  Here’s how I did it.

Preface:  I have two LANs;  one for computers, tablets, and phones, and the other for IOT devices, including my Roku (which cannot use a VPN).  I’m using Opnsense 18.7, so menu item labels or locations might change in the future.

Step 1:  Get all your certificates and such from your VPN provider.  They might give you a single file with labeled sections or a series of files.  You need:

  • somename.ovpn  (If you open this file with Notepad or Wordpad, it contains all the below except for the username and password, but it might also contain other directives for your provider.)
  • CA.crt (Certificate Authority–it might be a section of your .ovpn file.)
  • TA.key (OpenVPN Static key–Some VPN providers will give you a username and password instead of a static key. It might be a section of your .ovpn file.)
  • User.crt (User Certificate–it might be a section of your .ovpn file.)
  • User.key (RSA Private Key–it might be a section of your .ovpn file.)

Step 2:  Log into Opnsense and navigate to System -> Trust -> Authorities.  Click Add.

  • Put anything (within reason) in “descriptive name.”  I suggest the name of your VPN provider.
  • Leave Method “Import an existing Certificate Authority”
  • Paste the contents of your CA.crt (Certificate Authority) file.  If you just have one big file, copy and paste the CA certificate including the “begin” and “end” bits.
  • Click Save.

This Certificate Authority should now show up in Opnsense on the Authorities page.  Opnsense should fill in information about your VPN provider here that it got from the certificate.

Step 3:  Navigate to System -> Trust -> Certificates.  Click Add.

  • Leave Method “Import an existing Certificate”
  • Put anything (within reason) in “descriptive name.”  I suggest the name of your VPN provider.
  • Paste the contents of User.crt into Certificate Data.  If you just have one big file, copy and paste the user certificate including the “begin” and “end” bits.
  • I think I pasted the contents of User.key into the private key area, but the How-To I followed left this blank, so apparently both work.
  • Click Save.

If you view Certificates, Opnsense will now have more information about your certificate (including email address of issuer, etc.).

Step 4:  Navigate to VPN -> OpenVPN -> Clients.  Click Add.

This is where the How-To guides started to fall apart for me.  You might need to trial and error a little here.  You’ll also need to open that .ovpn file with Notepad or Wordpad (if you haven’t already).

  • Put anything (within reason) in “description.”  I suggest the name of your VPN provider.
  • Server Mode: Peer to Peer (SSL/TLS)
  • Protocol: (check your ovpn file)
  • Device Mode: (check your ovpn file)
  • Interface: (Your WAN interface–it’s probably literally called “WAN”)
  • Local port: (mine is blank, but check your ovpn file)
  • Server Host or Address: (check your ovpn file)
  • Server Port: (check your ovpn file)
  • Server host name resolution: Ticked

Cryptographic Settings:

  • TLS Authentication: Ticked unless you’re using username and password instead.  (If you’re using username and password, put them in the blanks.  If you’re using TLS, paste the data in your ta.key file here, AKA OpenVPN Static key.  If this option doesn’t appear, enter everything else, click save, and look again.)
  • Peer Certificate Authority: Select whatever you called it in step 2.
  • Client Certificate: Select whatever you called it in step 3.
  • Encryption: (check your ovpn file)
  • Auth Digest Algorithm: (check your ovpn file)
  • Disable IPV6: Ticked

Click Save.

The various How-Tos debate whether you need to paste the advanced settings from the ovpn file into the advanced settings on the Add Client page.  My tunnel didn’t work until I cleared all that data out.  Sigh.  In other words, try it without the advanced settings and see if it works first.

Step 5:  Check to see if your tunnel is working by navigating to VPN -> OpenVPN -> Connection Status.

You should see Status Up with a Virtual IP and all that good stuff.  If you don’t, navigate to VPN -> OpenVPN -> Log File and see if it says anything useful there.  You’ll probably then have to go back to Step 4 and tweak the settings to look more like your .ovpn file.  You might also need to contact your VPN provider if you’re really lost.

You cannot proceed further until you have a working tunnel.

Step 6:  This is where the other guides really went off the rails for me.  (Sorry, guys!)  This is what I did.  It may or may not be the best way to do it, but it worked.  Navigate to Firewall -> NAT -> Outbound.

  • Click “Hybrid outbound NAT rule generation (automatically generated rules are applied after manual rules) ,” then click Save.

Click Add.

  • Interface:  OpenVPN
  • Translation target:  “Interface Address”
  • Leave everything else any or default.

At this point, all of your traffic is going through your VPN.  YAY! except that Netflix, Hulu, etc. are now giving you an obnoxious “You appear to be using a proxy/unblocker, turn it off or no media for you!” message.  This is where my having the Roku on its own LAN makes things easy.  If you don’t have your network segregated like that, you can assign a static IP to your Roku or other streaming device and do the following steps with that static IP.

Step 7 (optional, unblocking Netflix on your Streaming Devices):  Navigate to Firewall -> Rules.

If your streaming devices have their own network card in Opnsense (mine do):

  • Navigate to Firewall -> Rules -> Opt1 (or whatever that network card is called).
  • Click Add
  • Leave everything default except changing the Gateway to WAN_DHCP (or whatever it’s called).
  • Click Save.

If your streaming devices have static IPs on your regular LAN:

  • Navigate to Firewall -> Rules -> LAN.
  • Click Add
  • Leave everything default except changing the source to your device’s static IP and the Gateway to WAN_DHCP (or whatever it’s called).  (If you have more than one, create an Alias first in Firewall -> Aliases -> View, click Add.  Type host, add however many lines for however many streaming devices you have, click save, and use that Alias as the source.)
  • Click Save.

If you want to get super-fancy and still access Netflix from any computer while sending all other traffic through the VPN, you probably need to create an alias with this information and route all traffic to that destination through WAN_DHCP.  That’s, like, more than I really wanted to get into for this article, though.  You can also turn off the VPN, or create a rule that sends your laptop straight through WAN_DHCP and turn it off or on, but those both suck (IMHO).

Happy tunneling!

Leave a Comment

Filed under geekiness

What Happens When You Request a Webpage

Okay, with all the securing all the things posts, it occurs to me that I haven’t actually explained this. (I did, however, explain this in depth for an interview at Large Internet Company, so it’s probably higher-level than I thought.) One of my first jobs included writing CGIs (web-based programs) that logged into and/or proxied other sites for our authenticated users. (That’s legit; it was a college that had paid for an online database service and wanted to provide access to their students. There are now products (google EZProxy, for example) that do this, but this was back in the wild wild west frontier. But I digress.)

Okay. You fire up your computer from a dead sleep. You launch a web browser and point it at http://www.google.com. What happens then?

The first thing that happens is your computer asks your DNS (Domain Name Server) for the IP address of www.google.com. Because these names are easy for humans to remember but computers still use the IP address. (You know what an IP address is, right? It’s a number that looks like 123.122.12.123–four numbers between 1 and 255 inclusive separated by dots. These numbers allow your computer to actually find the remote server on the internet. I’ll just leave that there for you to take as written unless someone really cares, LOL.) Unless you’re using something like Open DNS or Google’s DNS–basically, unless you change what your ISP gave you when they gave you your IP address–that DNS server is probably run by your ISP.

Your DNS server doesn’t actually store any IP addresses permanently (unless they’re Authoritative for a particular domain, like your ISP, and they might/probably actually separate those out between authoritative DNS servers and client query DNS servers). If someone else has requested www.google.com recently, however, it stores the answer for however long Google’s DNS servers told it it could cache the answer (this is called TTL, or Time To Live). If no one has requested www.google.com recently, or if the old answer has expired, it goes out to the servers that are authoritative for the .com domains and says, “Who’s authoritative for google.com?” Those domain servers give your local DNS server that IP, and your local DNS server goes out to that IP and says, “What’s the IP for www.google.com?” Google’s DNS servers give them an IP address and how long they can store that answer. When your local DNS server has an answer it likes, it gives that answer back to you.

Your web browser than opens a connection to that IP address on port 80 (the port for unencrypted web browsing traffic) and requests the main page (“GET /”, perhaps with a status number and header information about your browser and what kind of media you’re willing to accept and such, but the “GET /” is all that’s required. If you have telnet, you can actually do this manually and see what you get back). The www.google.com web server sends you back an unencrypted HTML file and closes the connection. If there are embedded images, your browser requests each of them in turn while it loads the page.

If you have a fast connection, this all typically happens in milliseconds. Well. Unless the page is full of ginormous images, hahaha.

If you instead request https://www.google.com, the DNS dance is the same but there are some additional moves on the actual file requests. Your browser gets the web site’s public key from the web server and uses that to request a symmetric key to encrypt the traffic, and then moves on to the file requests and deliveries. Oh, and this happens on 443 instead of 80, because that’s the default port. Unless your site has numbers on the end of the URL, like https://my401kprovider.com:8124 or something.

Aside: I previously mentioned a “man in the middle” attack; that’s where someone steps in between you and the web server and sends you a fake key and does the encrypt and decrypt dance between you, proxying the content. This is part of why you should pay attention to whether or not that padlock is green in your URL window, yes. There’s a whole system of who certifies whether the certificate is good; there are certificate authorities and your browser has some public keys for the authorities stored. (Some malware messes with those, too. Your antivirus might also be man-in-the-middling you to scan encrypted content for viruses. There’s some debate as to whether or not this is benign, and whether the advantages outweigh the risks–I think it depends on the user. You can tell both in your AV settings and by clicking on the padlock and asking for more info. Some AV companies also sell certs, so if you have Symantec or Comodo and the cert is from Symantec or Comodo that doesn’t prove anything, but if it says Avast, yeah, that’s their HTTPS scanning feature at work.)

The email client dance is similar; There’s the DNS dance of “who is mail.yourmailserver.com?” followed by really short requests sent by the software to authenticate you (your username and password) and get the content, and a similar encryption dance if you’re using encryption (which I recommend, yes). You can use telnet to send mail from the command line, which someone did in a previous embedded video.

Leave a Comment

Filed under written for nontechnical friends

Why Public Wifi is So Insecure

Here’s a short demo of sniffing.

They [TM] can possibly do this to you at that nice free public wifi spot you’re using.

They need to be on your network. This is geographically limited, by which I mean that if you’re in Kansas and visiting a server in Kansas, someone in England or Japan can’t see this stuff unless:

  • They’ve compromised something on your network,
  • They’ve compromised something between you and the site you’re visiting, or
  • They’ve compromised something near the host you’re talking to.

However, that shady person at the coffee shop’s free wifi could see this stuff just fine. These attacks typically occur somewhere near you.
They cannot see anything going over SSL, like web pages using https, unless they’re man-in-the-middling you, which is a whole different thing. Er. That’s more than I wanted to get into, but… it’s unlikely, unless the free wifi itself is messing with you.

Anyway. You can fire up Wireshark right now and possibly see your housemates’ passwords if they’re using insecure connections. (I once showed my ex his passwords over Wireshark.  Heh.)

What I’m getting at is:

  • Use SSL (https, secure connections on your email client, etc.) wherever you can.
  • Public wifi is super, super insecure. You probably want to use a VPN on public wifi.

Leave a Comment

Filed under written for nontechnical friends

Email Encryption

I’ve been trying to get people to encrypt their email, and send me encrypted email, since 1995 or so.  Here’s why:  email is cleartext.

What does “sent cleartext” mean?

Well, if your ISP is having you send your email out through port 25, you can do this.  (I’ve done it myself to test my own mail server at work.)

That’s basically what happens, automated, when you send your email out through a client. So unless your ISP has you send your mail out over SSL (they’ll probably give you another port to use, like 465 or 587), anyone sitting between you and your ISP’s outgoing mail server can potentially sniff that mail. (Like, that malicious person at the free wifi hotspot you’re using.)

So, you have an encrypted connection to your outgoing server, or you’re using https to access webmail. YAY! People sitting between you and that server can’t sniff it.

(Note: It’s even worse if you aren’t encrypting the connection to your incoming mail server in a mail client. Your username and password are sent cleartext over the wires.)

Don’t relax yet. 😉

What happens next is that your outgoing mail, or SMTP, server goes through the same process with your recipient’s SMTP server. This connection may or may not be encrypted and you have no way of knowing this unless your ISP refuses to talk to other servers that don’t encrypt. (Posteo.de says they do that.) Then the mail is stored on a server disk unencrypted until you or the recipient access it.

This is where encrypting the contents come in. If I send someone an encrypted email (*waves at [personal profile] cedara*), my outgoing mail server and their incoming mail server know that:

the mail came from my email address
the mail is addressed to their email address
there is a cleartext subject line.

The contents, however, look like:

—–BEGIN PGP MESSAGE—–
Version: GnuPG v2

hQIMA7RxPzPiyyNTAQ/+INv2GhVp9fCdib7JkzmEis8jw2Qtoazrp5mXDxwRG9Qv
evYhcnY8GIcwkMvYRTMlFfmcS4D/iZTmvPfj9+fZ0piHUWC3zYUgXDIm77821edr
Hfs158zPmmrAbX2ZtsG3GfNnhkCZHQceKuD4pKCAuoa2tlkvNwYTCuWH6g741YyY
Dllci00n5WB1dlR0lhUMhx42jpLW5q2Gco42axqZ1sAPDBSII4+I24LSL5cYr98E
DAJq98T2SgSF2jtGCwBg+xmRFOt2UPPP5fhxz8UnmHbdiFdVXbF0Wb/FiAydXCOl
OqkEVhGraPG9kPs9otBkP469fHAXlnBifBdioHVIArzmrsvXQyeT4kfGgd5aLoAr
pAKuSQmaaEjRmu0rUyKB5NnfNzd4djyDeJA+iVfexSDz7xY8rSRAXWJjFcQmGEg/
+7xdpr/qK/VD9m8htF0Th6kLs2EV+3tlmjNwjC4Jlt3l9wpSHwno9rIFLZaka4Br
GgJ6sKbPC/4mzKhbJV+I4pMGvCnEbzeEwlGFWlAoPE1Vg4JO2oNVrjTAS6Y+qKLP
rDGRcwBqS9FbM94KLKx9WUgpV1FBXMG7uHDKfxIT75yfQXeOJ26GhCxNYjJuvvUn
vlQzGrVmn9XdpgnGngjXMMLyyPnPGzDSCTAMeIjlHO7s73iFc7Bzho3ucMNQJH7S
ZQGr4l0YcjlYu7b1Z12BFW+9v6wjVBJsha2KwwqK9ukFt4GK//Lymd3n8sVkCfot
JWOW+u0ArTiX/I1OAlGM7+1Qe8XUzuSADTOmSMbUYpY+W7o6V7dhNE3lTfEyChNQ
8V2kgbjn
=l7cD
—–END PGP MESSAGE—–

By the way, even though I’ve posted that to my blog, only the person who has the private key can read that.

This isn’t new technology. It’s actually old. The reason everyone isn’t using it is because it’s not set up by default, and because the people you email are probably not using it already and you can’t if they don’t. 🙁

If you’re a webmail user, there’s Mailvelope, which I haven’t used. For everyone else, Enigmail for Thunderbird is awesome. GPG4Win used to be kind of buggy but it’s gotten really good–I particularly like the “encrypt/decrypt clipboard” feature, which I’m using instead of Mailvelope for webmail.

Then, all you need is a recipient’s public key. (You always encrypt to the recipient’s public key.) The way it works, in short, is that each person has two keys: one public, one private. They give the public key to you and keep the private one a secret. Only the private key can decrypt something encrypted with its public key. So only one person can read what you’ve sent, and that’s the person who’s got the private key. (Here’s more.)

If you want to set it up and test it, I’ll email with you. My username @nym.hush.com will reach me. We can exchange public keys and go from there.

Leave a Comment

Filed under written for nontechnical friends

The Best Thing Windows Users Can Do To Prevent Malware

If you’re a Windows user, you probably want to do your daily web surfing as a non-administrative user. This is because any process you launch (on any operating system) runs with your account’s permissions. That means that if your browser runs into an exploit, that exploit basically runs as you.

My sister is a Mac user, so I know that when she goes to make an administrative change to her Mac, it asks her to confirm her username and password. I used to use Ubuntu on my primary system and can tell you that something similar happens there.

<tech>This makes sense, because they’re both based on unix and that credential prompt is based on sudo.</tech>

This isn’t how it works in Windows.

By default, the first user you set up in Windows is an Administrator, and subsequent users aren’t. (You can add or remove privileges later.) When you go to make an administrative change, since Vista Windows will ask you to click the much mocked “Are you sure?” button if you’re an administrator, or prompt for credentials if you aren’t. (Some people even turn the “Are you sure?” prompt off completely. Don’t!)

<tech>The “Are you sure?” button is technically called “UAC,” or User Access Control.</tech>

By using an unprivileged, non-administrative account, you force Windows to ask for account credentials. This limits the damage a browser exploit can do. It also means you have to remember two usernames and two passwords, but there you are. This also means that if the malicious process somehow manages to break out of UAC prison and bypass the “Are you sure?” prompt, it’s running as an account that’s not allowed to make those changes, anyway, and is out of luck.

Leave a Comment

Filed under written for nontechnical friends

Generate a Change Script to Move System Databases

I set this up to be modular and reusable, because I like reusing things.  This doesn’t actually move your system databases.  It just outputs a change script and a revert script.  You still have to run the generated script, then log on to your server and stop the services and move the files yourself.  In my example, I’m moving TempDB from C (ew) onto better storage.
declare @dbname sysname,
@oldpath varchar(255),
@newpath varchar(255)

set @dbname='tempdb'
set @oldpath='C:\Program Files\Microsoft SQL Server\MSSQLSERVER\MSSQL\DATA' --no trailing slash
set @newpath='H:\HappyStorage' --no trailing slash

/*  Generates your change script */
SELECT 'alter database ' + @dbname + ' modify file (name=' + name + ', filename = ''' + replace(physical_name,@oldpath,@newpath) + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);

/* Generates a revert script */
SELECT 'alter database ' + @dbname + ' modify file (name=' + name + ', filename = ''' + physical_name + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);

/* Tells you where the files are now */
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);
GO

Sample output would  include, in the top window:

alter database tempdb modify file (name=tempdev, filename = 'H:\HappyStorage\Data\tempdb.mdf');
alter database tempdb modify file (name=templog, filename = 'H:\HappyStorage\Log\templog.ldf');

Copy and paste that into a new window, and run it if you’re sure it’s right.

You can, of course, use this for MSDB or any other database you want to move.

Leave a Comment

Filed under Uncategorized

De-duplicating files in batch

So, I have a drive full of files.  Sometimes, I get a new one–okay, sometimes I get over 1000 new ones–and want to keep the latest and pitch the duplicates without throwing away old files that don’t have a duplicate.  These files are of the form DATABASENAME_Attach_number_7777mmdd_hhmmss.sqb, but they could be anything, really.

(Why batch?  Because I had most of it lying around already and I’m lazy.)

set destination="i:\directory\with\old\files"
set source="f:\directory\with\new\files"
cd %destination%
mkdir old
mkdir new
move *.sqb old
robocopy %source% %destination%\new /mt:2 /mov
del early.txt
del files.txt
del names.txt
for %%F in ("old\*.sqb") do echo %%F >> early.txt
for /f "tokens=2 delims=\" %%A in (early.txt) do (
echo %%A >> files.txt
)
for /f "tokens=1-3 delims=_" %%A in (files.txt) do (
echo %%A_%%B_%%C >> names.txt
)
for /f %%F in (names.txt) do @if exist new\%%F* del old\%%F_FULL*
del early.txt
del files.txt
move names.txt dedupe
cd old
move * ..
cd ..\new
move * ..
cd ..
rd new
rd old

Leave a Comment

Filed under scripting

Pull the restore chain from MSDB

What files do I need to restore this database to a point in time?

You might know this off the top of your head, especially if you set up the backups or if you don’t have a lot of backups, but just in case you’re panicking, ask MSDB.  MSDB knows all… unless that data has been purged.  (Sorry.)

declare @dbname varchar(80),
@lastfull datetime,
@lastdiff datetime,
@fullback varchar(1024),
@diffback varchar(1024),
@RowsToProcess int,
@CurrentRow int,
@restorediff varchar(max),
@logback varchar(1024),
@restoreday datetime

set @dbname = ‘YourDB’
set @restoreday = ‘1/1/2016 8:05am’ –Make sure the time zone is your server’s time zone.

select @lastfull = max(backup_finish_date)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = ‘D’
WHERE d.database_id NOT IN (2, 3) and d.name=@dbname
and description like ‘Backup on%’
and backup_finish_date < @restoreday

SELECT    @fullback = m.physical_device_name
FROM         msdb.dbo.backupmediafamily AS m INNER JOIN
msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id
and b.type=’D’ and b.database_name=@dbname
AND b.backup_finish_date=@lastfull

print @fullback

SELECT @lastdiff = b.backup_finish_date FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’I’ and b.database_name=@dbname AND b.backup_finish_date>@lastfull AND b.backup_finish_date < @restoreday

If @@ROWCOUNT = 0
begin
set @lastdiff = @lastfull
end

select @diffback = m.physical_device_name FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’I’ and b.database_name=@dbname AND b.backup_finish_date=@lastdiff

print @diffback

CREATE TABLE #logs (RowID int not null primary key identity(1,1), logback nvarchar(255),finishdate datetime)
insert into #logs SELECT distinct m.physical_device_name, b.backup_finish_date FROM msdb.dbo.backupmediafamily AS m INNER JOIN msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id and b.type=’L’ and b.database_name=@dbname AND b.backup_finish_date>@lastdiff AND b.backup_finish_date<@restoreday order by b.backup_finish_date

SELECT @RowsToProcess=COUNT(logback) from #logs

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@logback=logback
FROM #logs
WHERE RowID=@CurrentRow
print @logback
END

drop table #logs

Leave a Comment

Filed under Uncategorized

Windows Internal Database/SQL Express backups and maintenance

Google informs me that my post on Windows Internal Database Maintenance is popular.  It’s not what I’m currently using, however.

I’ve switched over to Ola Hallengren’s scripts.  If you download and run the installer script, it creates a series of agent jobs, which, well.  Neither Windows Internal Database nor SQL Express has SQL Server Agent.  However, the jobs run as cmdexec scripts that look like

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'f:\sql_backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 672, @CheckSum = 'Y', @LogToTable = 'Y'" -b

It’s easy to run these through Task Scheduler.  You’ll want to replace “$(ESCAPE_SQUOTE(SRVR))” with your actual server.

(The connection string for the version of Windows Internal Database based on 2008 is \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query.  The connection string for the version of Windows Internal Database based on 2012 is \\.\pipe\MICROSOFT##WID\tsql\query.  SQLExpress on your current host is .\SQLExpress by default.)

Continue reading

Leave a Comment

Filed under scripting, sql