BACKUP SERVICE MASTER KEY with date in file name

A service master key is created when SQL Server instance is run for the first time. And it's regeneratred every time you change service account or its password. You should protect it with backup.

Syntax for BACKUP SERVICE MASTER KEY is described here:
http://msdn.microsoft.com/en-us/library/ms190337.aspx

Important notice is it does not allow to overwrite previous file. So you have to delete it before executing statement or use unique file names for every day backup.

This is my simple script to have BACKUP SERVICE MASTER KEY done with date in file name (so it's unique per day). It's compatible with SQL Server 2008 and above (older releases does not allow to set value while declaring variable).

Feel free to use it!


DECLARE @tsql NVARCHAR(512)
DECLARE @holder NVARCHAR(16) = '{DATE}'
DECLARE @path_to_file NVARCHAR(256) = 'D:\sqlbackup\keys\service_master_key_{DATE}.bak'
DECLARE @password NVARCHAR(16) = 'mysecretpassword'
DECLARE @current_day_string NCHAR(8) = CONVERT(NCHAR(8),GETDATE(),112)

SET @path_to_file = REPLACE(@path_to_file, @holder, @current_day_string)
SET @tsql = 'BACKUP SERVICE MASTER KEY TO FILE = '''+@path_to_file+''' ENCRYPTION BY PASSWORD = '''+@password+''''

EXECUTE sp_executesql @tsql

"Could not obtain information about Windows NT group/user 'DOMAIN\user', error code 0x5" in SQL Agent

Problem:

You login into SQL Server instance using Windows Auth. You're sysadmin. You've created job in SQL Agent - you're the job owner - but you get an error every time you try to run it. You've noticed that changing owner to SQL login with SQL Server auth is workaround.

Details:

Job owner is SQL login with Windows authentication, created for windows domain user. This login is sysadmin (or has enough priviledges to run the job).

You get error:

Unable to determine if the owner of job SYSTEM has server access (reason: Could not obtain information about Windows NT group/user 'DOMAIN\user', error code 0x5. [SQLSTATE 42000] (Error 15404)).

Solution:

It seems something blocks SQL Server to query Active Directory controller for DOMAIN\user details.

Determine windows user who runs SQL Server instance - if you don't know it (you should!).

1. Run Sql Server Configuration Manager.
2. Display SQL Server Services section.
3. Highlight "SQL Server (Your Instance)" and read user name from "Log On As" column.

Open Active Directory Users And Computers app, find this user, open Properties.

I guess that "Password never expires" was not checked. Just check it and apply. Re-run your job.

Speed up paper forms processing with CODE39 barcodes and Symbol LS2208 barcode scanner

It's not a discovery that barcodes make life easier. Barcodes can speed up any process that needs to have some data read from paper or package - and this is done by human. But did you know that introducing barcodes in your project could be very easy?

Scenario

Imagine following scenario. There is a process which includes printing a paper document. Then paper document comes from hands to hands. And finally comes to an operator who needs to trigger some job in computer system related to that paper form. For example: the operator needs to open window, look up for data related to paper, complete form, save it.

Looking up for data could be time consuming and could be a place for human mistakes. This is the place where barcodes come in.

Where to start?

Barcodes are quite wide subject - type of codes, standards, generating, control codes, rights to use. This is not article about it. If you're totally new to barcodes, you should get know CODE39 for a beginning - the simplest one. This type of code is a very good point to start as it's very simple to understand, very simple to generate without 3rd-party libraries, recognized by every barcode reader and there are no restrictions to use it. For comparision: EAN13 that could be found on Coca Cola can it's much more complicated story.

All you need to get started with CODE39 is:

• download CODE39 fonts (ask uncle Google for free ones)
• get know CODE39 syntax which is very simple (please, read forward)

Code39 syntax and generating barcodes

Generally you can code into CODE39 short alphanumeric strings. Every digit or letter has its representation in exactly one bar code (one font). First and last char in code must be asterisk (*) which means start and stop.

So if you want to have 123456789 coded into CODE39, just add asterisks to get *123456789* and change font to CODE39 font. You may try it with WordPad or Microsoft Word. Now imagine 123456789 is for example document ID or some primary key and you want to add it to Crystal Report form. It simple as to change a font (from Times New Roman to Code39). Ok, deploying fonts into set of computers is another story but you may build an MSI package a deploy it with Group Policy in Windows Server.

Good source to get know CODE39 better is Wikipedia article: http://en.wikipedia.org/wiki/Code_39

Well, well - half of work is done. We have got barcodes with ID-s printed on paper forms. Now it time to read it and to trigger some action.

Scanning barcodes

Let me introduce Symbol LS2208. Symbol LS2208 is simple laser barcode reader. It's ready to work just after unboxing. It is very popular and it costs ca. 100 USD.

Symbol comes as RS232 or USB connected devices. In USB version is great plug and play device as it does not require any drivers. It acts as a second keyboard. This also mean you don't need any SDK nor to recompile your software solution to have it worked with Symbol.

First exercise: print some CODE39 codes from WordPad or Microsoft Word (as described earlier) onto the paper (laser reader cannot read from display). Plug Symbol into USB. Then open Notepad and scan your codes into it.

You will find that barcodes read your 123456789 easily in both directions (I mean you don't worry that someone scan your code upside down). You also find that asterisks (*) are omitted, so you won't need to parse them out. You will also notice that every next scan result is written down in the same line without any separation (123456789123456789123456789).

Symbol LS2208 configuration

Now it's time to tune things up. Best about Symbol is that you can reconfigure its behavior and you can also do it very easy. To do so - open printed manual (or print interesting pages to paper) and scan configuration codes with reader. If you fucked everything up - start again from defaults - just find "Set All Defaults" configuration code in manual and scan it.

Great news about configuring Symbol scanner is that configuration is stored in device flash memory - and not in computer. Once configured it may be plugged into another computer and behaves as expected.

What may be done with configuration codes? I suggest you to study Symbol manual by yourself to find interesting behaviors for your project. Most basic behaviors are: adding prefix and suffix to read code. For example - you want to have sent ENTER key along with your code to your app (so user don't have to touch keyboard after scanning). No problem!

With configuration codes you may filter out some unwanted codes. Let's say you have different type of codes on paper form and you don't want your users to scan improper ones.

Another great feature of Symbol LS2208 is continuous scan. Just place scanner in handler (included in the box) and Symbol starts to light constant red line on the table without need to pull the trigger. Just move your paper form (the barcode section) under the line. Now find in manual set of configuration codes to set some insensitivity to the same code if its scanned twice (when you move your paper back and forth). Do some practice with it to find best time of insensitivity.

Additional tips and sample configuration

In this article I've shown you how to have barcodes printed and read with minimal effort. Last tips: Add CODE39 fonts to your project repository. Prepare scanner configuration as a set of control codes - start with "Set All Defaults", save it as PDF document and also add to your project repository - you will be able to configure second device in seconds.

Zeroconf is your friend (real world examples incl. VMware ESXi and IBM DS3524)

Subnet 192.168.1.0/24 is probably the best recognized subnet in the world. Every piece of network hardware - no matter if it's for home use or advanced one for business use - comes with 192.168.1.x as its default IP. We're are very familiar with this subnet.

But sometimes, especially if there is some problems with network, your PC catches IP from some other subnet - like 169.254.x.y. What's this? Does it always mean problems? No. Let me introduce - 169.254.0.0/16 is your new friend.

Subnet 169.254.0.0/16 is:

• part of Zeroconf idea,
• is used for IPv4 Link-Local addressing,
• is not routable,
• is reserved by IANA,
• is described in document RFC 3927

The idea is simple and clever. After media is detected, network interfaces set random IP for itself from 169.254.0.0/16 subnet and it's ready to communicate with other devices within the subnet.

In practice, after media is detected (ethernet cable plugged or associated with WIFI access point), OS is broadcasting for new IP from any DHCP/BOOTP server, and if there is no response, after some timeout it sets new IP itself - from 169.254.0.0/16 subnet. Of course there is no broadcasting for new IP if it's set to static or you're using alternative NIC configuration feature of Windows 7 and above which sets static IP defined by your in case there is no DHCP/BOOTP nearby.

Some interesting part of RFC 3927:

2.1.  Link-Local Address Selection


   When a host wishes to configure an IPv4 Link-Local address, it
   selects an address using a pseudo-random number generator with a
   uniform distribution in the range from 169.254.1.0 to 169.254.254.255
   inclusive.

   The IPv4 prefix 169.254/16 is registered with the IANA for this
   purpose.  The first 256 and last 256 addresses in the 169.254/16
   prefix are reserved for future use and MUST NOT be selected by a host
   using this dynamic configuration mechanism.

   The pseudo-random number generation algorithm MUST be chosen so that
   different hosts do not generate the same sequence of numbers.  If the
   host has access to persistent information that is different for each
   host, such as its IEEE 802 MAC address, then the pseudo-random number
   generator SHOULD be seeded using a value derived from this
   information.

OK, I could have quite unique IP without DHCP - so what? Even cheapest internet router has got DHCP server built-in. Why would I want to work without DHCP? And I always can set any IP manually if there is no DHCP.

At this point I want to introduce some real world examples. In both cases there is no place for DHCP - and of course - you can always set IP manually but it's always an additional effort and source of possible problems if you forget to change IP to AUTO after your job is done.

Scenario A: VMware host in co-location centre

Company has got one 1U server in co-location centre for off-site backup purposes. Centre charges the company for every 1U space and electric power. So for the cost effective installation there is no monitor, there are no additional networking hardware - only server with VMware ESXi on it.

How to maintenance the machine being at co-location site if there is no display or local network infrastructure (accessible for you)? It's common that 1U servers can have two network interfaces (NIC) in standard. One can be connected with collocation LAN (the way it access Internet) and the second one may be for local access purposes.

And a pic how ESXI networking was configured:

Notice: on vmnic1 physical adapter there was management port configured in subnet 169.254.0.0/16. IP number shown (169.254.100.100) is static one (and easy to remember).

How it works? Just connect notebook with server directly using crossed ethernet cable (or straight if you believe in auto-sensing). Because there is no DHCP - after timeout - OS at notebook will set up its NIC with some IP from 169.254.0.0/16 subnet - the same subnet ESXi is in. And you're ready to connect ESXi with VMware Sphere Client.

Pros:

• You can easily access your machine in co-location site with just notebook and ethernet cable.
• You don't have to make VMware ESXi to act as DHCP server on ethernet port labeled "for management".
• You don't have to do any effort to set IP manually on your notebook - especially when you switch between some LAN segments with and without DHCP
• You don't have to remember to set IP to AUTO after work.

Cons:

• You have to wait for a timeout - OS waits for response from any DHCP first.

Scenario B: emergency access to IBM DS3524 storage system

This is also real world example. I have named system storage with its model number because this is more vendor specific solution. IBM DS3524 storage system has two redundant controllers (called Controller A and Controller B). Every controller has its cache, keeps configuration and so on - so storage system can continue work on one controller only. Both controllers have two network interfaces (so there are 4 NICs in total).

• Controller A Port 1
• Controller A Port 2
• Controller B Port 1
• Controller B Port 2

Manual says that Port 1 and Port 2 must be in separate subnets. So it's great occasion to develop solution like that:

• Controller A Port 1 - IP = 192.168.1.201
• Controller A Port 2 - IP = 169.254.111.111 (easy to remember)
• Controller B Port 1 - IP = 192.168.1.202
• Controller B Port 2 - IP = 169.254.222.222 (easy to remember)

You control DS3524 with software called  DS Storage Manager Client. DS Storage Manager has some availability to discover controllers in LAN or define it manually. Using zeroconf idea, in case of emergency, you can plug into DS3524 directly (using crossed ethernet cable or straight if you really believe in auto-sensing) or in-directly using switch (solution may include switch with some VLANs configured). By use of switch you can make connection with both controllers at one time - which is more preferred by IBM software when both controllers are online and in a good mood (healthy state I mean ;). And again: you don't have to configure IP in your notebook manually, so you can access device more quickly. Image scenario where your DHCP server is down because it relays on system storage.

Some pics..

Pros:

• You can easily access your system storage with just notebook and ethernet cable.
• Your access do not relay on DHCP server which could relay on managed system storage.
• Your access may not relay on any other networking devices.
• You don't have to do any effort to set IP manually on your notebook.
• You don't have to remember to set IP to AUTO after work.

Cons:

• You have to wait for a timeout - OS waits for response from any DHCP first.

Hide Results Pane and make some space for your SQL code (SSMS 2012)

Space on your screen has its value. This is productive tip for making some more space for your SQL code while building your query. After executing a query under SQL Server Management Studio (SSMS), a result pane appears and takes aprox. half of the screen. You can make it smaller by dragging the separator line down. But better idea is to just hide it.

You have 3 options:

• Go to Windows | Hide Results Pane
• Use CTRL+R shortcut (worth to remember - as it's worth to remember F5 shortcut is for Execute)
• Use Hide Results Pane button just from toolbar - but.. you have to add it onto toolbar first (and spend some minutes if you don't know where to find it - continue reading..)

My favorite is the third one. So, the number one step after running SSMS in new enviroment is to add Hide Results Pane button onto toolbar. And this is the post how to do it.

Some pics..

You're starting with some new query..

You're pressing F5 to execute and a half of screen is taken by results. No space for continuing a query..

You may find Hide Results Pane under Windows menu and get rid of it.

If you want to have Hide Results Pane button easy accessible you have to start with clicking left mouse button on a small down arrow at right side of choosen toolbar. Click Customize at the end.

On the Commands tab click Add Command button.

Next, select Windows in Category list (almost at the end) and then select Show Results Pane in Commands list (also almost at the end). Yes - it's Show Results Pane - do not look for Hide Results Pane.

New command was added at the begining of choosen toolbar. It's good idea to move it to the end with Move Down button. Tip: use auto-fire on your joystick ;)

Ready! Congratulations! Now you can hide and show again results pane fast with only one click.

Installing VMware Tools on Debian 6.0

I found that installing VMware Tools on pure Debian 6.0 installation generates some troubles.

After starting VMware Tools installation you may get error: path to gcc is not valid - even you've got gcc installed already.

After some tries - here is complete task list to install VMware tools.

Stuff used in this installation:

• Pure Debian 6.0.6 AMD64 installation (from image debian-6.0.6-amd64-netinst.iso)
• VMware ESXi 5.0.0 Build 469512 (free license)
• VMware Tools 8.6.0 Build 425874 (included in ESXi installation)

Preparations:

1. apt-get update
2. apt-get install linux-headers-`uname -r` make

VMware Tools installation:

Begin with inserting installation CD

Then..

1. mount /media/cdrom
2. cd /tmp
3. tar -zxf /media/cdrom/VMwareTools-8.6.0-425873.tar.gz
4. cd /tmp/vmware-tools-distrib
5. ./vmware-install.pl
6. Response with defaults by pressing [ENTER] to all question

Status of VMware Tools in vSphere Client will update to "Running" just after installation. No reboot required (or any service starting by you manually)!

Watch your behaviours while restoring DB under SSMS!

Today, I was just one step from making a mess.
I have both of Microsoft SQL Server Management Studio installed - 2008 R2 and 2012.

Notice this major difference on DB restoring dialog.

Old one:

New one:

Source and destination fields are switched. If you're acting to fast you may loose your source DB restoring "destination" DB into it.