## 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
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
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.

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.

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.

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

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

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.

check_virtualdisk = cmd /c echo scripts\\vdiskcheck.ps1; exit($lastexitcode) | powershell.exe -command - And lines like this to command.cfg in Nagios: define command { command_name check_physicaldisk command_line /usr/lib/nagios/plugins/check_nrpe -H$HOSTADDRESS$-c check_physicaldisk } define command { command_name check_CRaid command_line /usr/lib/nagios/plugins/check_nrpe -H$HOSTADDRESS$-c check_virtualdisk } Leave a Comment Filed under Uncategorized ## Katherine’s Excellent Log-Shipping Adventure Or, Log-Shipping over 1200 databases automagically. Leave a Comment Filed under monitoring, powershell, sql ## Log-Shipping SQL Express with PowerShell Someone on Server Fault asked a question about which versions of SQL Server support log shipping. He uses Express. Log shipping uses SQL Agent, and Express doesn’t come with SQL Agent, but you can still manually log ship with PowerShell and Task Scheduler. Use the log backup script of your choice (I like Ola Hallengren’s, which can also be used with Task Scheduler), either back up to a share or copy the files to a share (perhaps with robocopy /mir?), and then run this or something like this (for a read-only copy):$restoreserver=”YourServer”
$logbackpath=”C:\path\to\logs”$standby=”C:\path\to\ROLLBACK_UNDO_YourDB.BAK”
$dbname=”YourDB”$dbsearch = “SomeSearchString*”

$logbacks = Get-ChildItem$logbackpath | Where-Object {$_.Name -like$dbsearch} | Sort-Object LastAccessTime

foreach ($logback in$logbacks)
{
$query = “RESTORE LOG [” +$dbname + “] FROM  DISK = ‘” + $logback.FullName + “‘ WITH FILE = 1, STANDBY = N'” +$standby + “‘,  NOUNLOAD,  STATS = 10”
sqlcmd -E -S $restoreserver -Q$query
}

Or this (for an offline warm standby):

$restoreserver=”YourServer”$logbackpath=”C:\path\to\logs”
$dbname=”YourDB”$dbsearch = “SomeSearchString*”

$logbacks = Get-ChildItem$logbackpath | Where-Object {$_.Name -like$dbsearch} | Sort-Object LastAccessTime

foreach ($logback in$logbacks)
{
$query = “RESTORE LOG [” +$dbname + “] FROM  DISK = ‘” + $logback.FullName + “‘ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10” sqlcmd -E -S$restoreserver -Q \$query
}

(If you’re copying and pasting, don’t forget to fix the “smart quotes,” which aren’t smart.)