Monday, 26 January 2009

Transparent Data Encryption

I know allot of you are looking to Microsoft SQL 2008 and thinking about the new features such as resource governor and policy's but in my mind the best feature is transparent data encryption or TDE for short.

Encryption on SQL 2005 databases and earlier was something of a nightmare, both hard to enable and losing features such as indexing on encrypted tables made databases slow.

All to often programmers and administrators would leave it off because of this and make data vulnerable data theft, TDE is a big improvement over previous versions, choosing to encrypt the database so that all the processes that access the database need to have the correct encryption key to access it.

This isn't just better for the security it also means that once the system has the encryption key it can then use it to do all the normal services such as indexing query's etc, without programmers changing a thing with the database.

Not only does this mean that you can now encrypt any database you chose well almost any you can't encrypt system databases (tempdb encrypts after you turn TDE on in case you use temp tables) but it also puts the encryption back in the hands of Database Administrator.

Now here is a quick demo on how to turn TDE on.


-- TDE transparent data encryption
-- first you need a master key to safe guard
-- your certificates I like a nice long 40 digits
Use Master
create master key encryption by password = 'zOLLaJyZxKoFGG5jUK4pfoYzW2eSOW6VGAvH4RvJ'


-- create the certificate and give it a name
Create certificate server_one_certificate with subject = 'My company certificate'

-- pick a database to encrypt
Use adventureworks

-- create encryption key AES_128 AES_192
-- or AES_256 based of your certificate
create database encryption key with algorithm = AES_256 encripton by server certificate server_one_certificate
GO


-- now turn the database encryption on
-- this might take some time depending on
-- size of database
alter database adventureworks set encryption on
GO


-- backup certificate public and private keys and
-- again pick a nice password
-- because if you forget it you'll need to create
-- another backup of the certificates
use master
backup certificate server_one_certificate to file = 'c:\backup\mybackupcert.cer
with private key ( file = 'c:\backup\mybackupcert.pfx', encryption by password = 'KHHiWUM8HpH1UqpLGGlvswzRZuVPmiMWeiM7RUOD' )


-- check the status of the encryption to see if its turn on or not
select * from sys.dm_database_encryption_keys


Now there are somethings to remember once you've turn it on.
First your backs of this database are now encrypted so if you want to restore to the same server its fine but if you want to restore to another server you must first import the certificates.

Second thing is that you should expect 5% over head on the CPU but the benifts of having your data secure are worth the CPU load, but remember to check you can take the extra load before turning it on.

Last thing to remember is that certificates can be used on a one to one or one to many so create as many certificates as you need but remember you'll have to back each one up.

Now I'll give you an example on how to import your certificates to another server for restoring your backups too.

-- to restore a certificate first you need to
-- create a master key if you don't have one already
-- doesn't have to be the same as the first server as you can see
use master
create master key encription by password = 'q3j0JFSsoqm2Ptf6LAPvdpxeMM3L9RKKZxD90AaA'


-- import your backed up keys, as you see
-- the certificate name doesn't have to be the same ether.
create certificate server_two_certificate from file = 'c:\backup\mybackupcert.cer' with private key (file =
'c:\backup\mybackupcert.pfx', decryption by password = 'KHHiWUM8HpH1UqpLGGlvswzRZuVPmiMWeiM7RUOD')


Now you can restore your database from the first server without problem as the certificate has been imported even if it does have another name, I didn't have to change the name I just did so to prove you don't have to have the same name.

One last thing if you are going to create this from script and there is nothing wrong with that but make sure to put the script in a safe place if you leave the password in, I found one the other day on a system that the DBA had forgotten to move after executing it.

Saturday, 10 January 2009

RIP offset

Using a simple RIP you can set a preferred route out of two or more interfaces, in this example we have two external interface serial0/0 and serial0/1 and one internal interface fastethernet0/0

The IP set up on the interface as follows.
serial0/0 - 172.16.12.1
serial0/1 - 172.16.8.1
fastethernet0/0 - 192.168.0.1

In this example serial0/1 is the faster link and serial0/0 is the slower so we want to set a offset to the serial0/0 so that the preferred link to use is serial0/1

to do this we must first define the traffic we want to offset this is done by using access-list

access-list 1 permit 0.0.0.0
access-list 2 permit 192.168.0.0

now the traffic is defined we need to define the RIP networks and the access lists that are used

router rip
network 172.16.12.0
network 172.16.8.0
network 192.168.0.0
offset-list 1 in 3 serial0/0
offset-list 2 out 3 serial0/0


To better understand the offset here is some of the options that are available

offset-list {access-list-number | access-list-name} {in | out} [interface-type interface-number]

This however is of no use in a fail over if the line protocol is still up.

Tuesday, 6 January 2009

Using PSExec to Remotely install software

Believe it or not, I got asked what can I use to deploy software, and I said well the list is endless but the question is really are you doing it as a one-off or as a weekly thing?

turns out the guy just wanted to patch some server after finding there was a problem with one HBA card.

So I said what version of windows or Linux he said typically windows, so first thing came to mind Sysinternals tool I love psexec works for me so well I try to use it daily, what I like so much about its that its simple command line no GUI or dependency services.

So for those of you in a lost as to what I'm talking about here its is.
goto microsoft.com or google and type in Sysinternals you'll end up on a TechNet page where the Sysinternals software has been relocated to now since Microsoft took it over, they have some lovely tools but the one I like best is psexec it lets you run remote command like a remote command line.

Example
for /f %i in (c:\serverlist1.txt) do psexec -c -d \\%i Win2003SP3.exe /quiet /norestart /overwriteoem

in this example, the list has just a basic text "serverlist1" with a server on each line or IP the psexec to start the process and then -c says to copy the file to the machine, and -d so it doesn't wait for the program to complete.

another example is the one I use for patching SQL
for /f %i in (c:\list1.txt) do psexec -c -d \\%i c:\SQLSERVER2005SP2-KB921896-x86-ENU.exe /allinstances /quiet

but you can use this with many patches as well example, I created an install.bat file and ran that to deploy many patches to each server the content of the batch file is as follows.

net use X: \\server\share\
@echo off
setlocal
set PATHTOFIXES=x:\update

%PATHTOFIXES%\WindowsXP-KB######-x86-LLL.exe /quiet /norestart
%PATHTOFIXES%\WindowsXP-KB######-x86-LLL.exe /quiet /norestart
%PATHTOFIXES%\WindowsXP-KB######-x86-LLL.exe /quiet /norestart

net use x: /d


Then I execute this batch file on many servers.
for /f %i in (c:\list1.txt) do psexec -c -d \\%i c:\install.bat

This is how I have install application and service packs on many servers.

I now continue this one my later post. Link