Networking – Trevor's IT Blog

Connect to a Microsoft SQL Server from Linux

Feb 222011

Introduction

Sometimes, you may only be able to connect to a Microsoft SQL (MS-SQL, or MSSQL) server through its SQL port, so you cannot use RDP to access the Enterprise Manager or other graphical tools on the host. Other times, you may simply want to leverage the power of a Linux box. 😉 Regardless of the reason, if you want to connect to a MS-SQL server from a Linux box, read on… As in previous posts, any installation or configuration instructions pertain to Gentoo. Please adapt as necessary. … Also, these instrcutions were tested on MS-SQL Server 2000, so some instructions may need to be adapted depending on your version of MS-SQL.

Basics

FreeTDS offers an opensource command line client, tsql. This is comparable to using Microsoft’s OSQL command line interface, although the arguments to launch the client are different. Although it has various options, you launch tsql, like so:

tsql -S <sql_server_name> -U <user_name> [-P <password>]

If you are comfortable with OSQL, you will have no problem using this basic SQL CLI client.

Programmatic

Packagers are available for Perl, PHP, Python, and several other scripting languages, which provide an extensive, programmatic interface to the remote MS-SQL server.

References

http://members.cox.net/midian/howto/phpMSSQL.htm
http://coding.derkeiler.com/Archive/Perl/perl.dbi.users/2006-09/msg00108.html
http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html
http://www.easysoft.com/developer/interfaces/odbc/linux.html

Analyzing A Remote Microsoft SQL Database

Networking, SQL No Responses »

Feb 222011

Introduction

Suppose you have access to a remote MS-SQL database, and you need to analyze it. Why? Let’s pretend that you intend to migrate the contents to another server or database, like MySQL. 😉 Anyway, how do you discover various essentials about the database? … These instructions are for Microsoft SQL Server 2000. The following instructions and results will probably vary wildly for other versions of MS-SQL.

Connect with TSQL

First, let’s connect to the remote MS-SQL using the tsql command line client:

tsql -S <sql_server_name> -U <user_name>

You may have another favorite method to gain command line access to the SQL database, but this is my current favorite. 🙂

Listing All The Tables in the Database

In our open SQL CLI, one site posted doing something like:

EXEC sp_tables
GO

Unfortunately, this does not list all the tables for some unknown reason. I could see several more tables listed in MS SQL Enterprise Manager. Most of my “user” tables were not reported by the above method, although some were reported correctly.

Another site indicated listing special system table contents, which I think is actually specific to ORACLE and newer versions of MS-SQL. Maybe it will work for you?

SELECT * FROM sys.tables
GO

The same site also suggested the following, but it also returned partial results for some reason unknown to me.

SELECT * FROM INFORMATION_SCHEMA.TABLES
GO

Others recommend using “sp_help” with no arguments, like so:

sp_help
GO

However, I found that it returned far too much information, much more than just the list of all tables, although that did seem to be included…

Ultimately, I used this:

SELECT * FROM sysobjects WHERE TYPE='s' OR TYPE='u' ORDER BY NAME
GO

This returned a filtered list of system objects that had a type of either “system” or “user” table. Very nice! 😀

Listing Table Structure

The structure of a particular structure can be listed, like so:

sp_help &lt;table_name&gt;
GO

Listing All Columns

Try this:

SELECT name, object_name(id) FROM sysindexes

Determining DB Size

Try this:

EXEC sp_spaceused [tablename]
GO

How To Check MS-SQL Columns of Unknown Name for Values

Networking, SQL No Responses »

Feb 222011

Problem

Imagine you have an MS-SQL table that has LOTS of columns. Some of the column names are known, but others are dynamically generated, and their names are not known at run time. Now imagine that you suspect most of those unknown columns are empty. How do you determine if any rows contain values in the columns, whose names are unknown?

Incremental Solutions

You could always just show all the values for every column of every row:

SELECT * FROM myTable

However, if your table is large, this may return too much data. So, you could look for unique values, like so:

SELECT DISTINCT * FROM myTable

This works better. However, if the known columns contain lots of unique data, the above T-SQL command may still return too much data. Let’s try to focus on returning the unique values of just the unknown columns!

Final Solution

In our problem, all of the unknown columns begin with a known prefix, “UDA_”. So, we need to get a list of the column names compiled in a comma separated list, suitable for a second SELECT statement. We can do this, like so:

DECLARE @myColumnNames NVARCHAR(MAX)
 
SELECT @myColumnNames=COALESCE(@myColumnNames + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME='myTable') AND (COLUMN_NAME LIKE 'UDA_%')
 
SELECT @myColumnNames

These statements initialize a local variable, @myColumnNames, and then COALESCE, or compile all the column names from the myTable, which begin with the prefix, “UDA_”, into a comma separated list. The last statement prints the variable value for debugging purposes, which might look like:

Finally, a simple select statement can be used to return the distinct values of these columns from our table. The only problem is that you cannot substitute a variable directly into a T-SQL statement, so you have to build the necessary statement string and execute it, like so:

EXEC('SELECT DISTINCT ' + @myColumnNames + ' FROM myTable')

Putting It All Together

DECLARE @myColumnNames NVARCHAR(MAX)
 
SELECT @myColumnNames=COALESCE(@myColumnNames + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (TABLE_NAME='myTable') AND (COLUMN_NAME LIKE 'UDA_%')
 
EXEC('SELECT DISTINCT ' + @myColumnNames + ' FROM myTable')

If the above statements yield a single row with nothing but NULLs, then you know those columns are all empty! Anything else indicates that at least one of the table rows, for at least one of your columns of unknown name, contains a value!

Use Windows XP on Gentoo Linux with KVM

Gentoo, Networking, virtualization 2 Responses »

Dec 052008

Introduction

Regrettably, there are some software applications that just run better on Windows, specifically, Windows XP. Of course, Windows runs better on Linux, so I guess we can still hold to our axiom that “All things run better on Linux”. 😯 Ok, not really. 😀

In my case, I would like to run the full Office 2003 suite on Linux. Using Wine is an option, but it can be a bit buggy. CrossOver is a better option, but it costs money, and I am cheapskate. Plus, it’s like “double-taxation”. I have to pay somebody a tax, so I can pay my Microsoft tax. That ain’t right! Well, I am not really a Linux purist – I am just a practical cheapskate. And, I would like to learn about OS virtualization, and apparently, so do you! Otherwise, you would not be reading this. 😉

Installing KVM on Gentoo

KVM is just one of many possible virtualization methods, which is a way to run one OS inside of another OS. (Imagine a “window” that is running Windows XP inside, and it “thinks” it is the entire computer. It does not realize that it is running inside of another “computer”.)

Note: These instruction are for:

Host:  Gentoo 2008.0
RAM:  >= 1.5GB
Kernel:  2.6.27
KVM:  v79
Guest:  Windows XP Pro

Of course, there these instructions may have to be varied slightly to accommodate your exact application.

Here are my modified instructions, based on the Gentoo Wiki:

1. Update the kernel with IN-KERNEL KVM (no need for module mayhem):

$ cd /usr/src/linux
$ make menuconfig

[*] Virtualization --->
        --- Virtualization
        <*> Kernel-based Virtual Machine (KVM) support
        <*>   KVM for Intel processors support
        < >   KVM for AMD processors support
        <*>   PCI driver for virtio devices (EXPERIMENTAL)
        <*>   Virtio balloon driver (EXPERIMENTAL)

If you want to be able to do networking, you should also enable VLAN bridging and tapping, while you are here:

Device Drivers --->
    [*] Network device support --->
            <M> Universal TUN/TAP device driver support

Networking --->
    Networking options --->
        <*> 802.1d Ethernet Bridging
        <*> 802.1Q VLAN Support

Copy new kernel into place. Update grub.conf. Reboot using new kernel. … You know the drill. 🙂

2. Ensure the latest version of KVM:

$ echo 'app-emulation/kvm ~amd64' >> /etc/portage/package.keywords

3. Activate useful USE flags:

$ echo 'app-emulation/kvm gnutls sdl' >> /etc/portage/package.use

4. Check emerge for weirdness and install:

$ emerge -pvt kvm usbutils bridge-utils usermode-utilities

These are the packages that would be merged, in reverse order:

Calculating dependencies... done!
[ebuild   R   ] sys-apps/usermode-utilities-20040406-r1  0 kB
[ebuild   R   ] net-misc/bridge-utils-1.4  0 kB
[ebuild   R   ] sys-apps/usbutils-0.73  USE="zlib -network-cron" 0 kB
[ebuild   R   ] app-emulation/kvm-79  USE="alsa esd gnutls modules ncurses sdl -havekernel -pulseaudio -test -vde" 0 kB

Looks ok to me. Does it look ok to you? Let’s go:

emerge -pvt kvm usbutils bridge-utils usermode-utilities

5. Setup access for non-root users:

For each non-root user, add them to the KVM group:

gpasswd -a <non-root-userid> kvm

Launching Guest for First Time and Installing Windows:

First, you need to create an “image” file, which will contain the entire Windows XP guest OS (think C:\ drive). Here’s the default way:

kvm-img create winxp_raw.img 30G

This will create a RAW image format that is 30 GB in size. This is the simplest and most portable image format. However, it is not the coolest!

kvm-img create -f qcow2 winxp.img 30G

This does the same thing, but it uses the latest QEMU format, which enables additional features, like image overlays.

Second, if you use ALSA for your host’s sound, then you can enable it like so:

export QEMU_AUDIO_DRV=alsa

Third, install Windows XP into image. Here’s the simplest method:

kvm -hda winxp.img -cdrom /dev/cdrom1 -boot d

This will do the same thing but it will use a local image of the install ISO (faster?), use 1GB of RAM (default is much less), use host’s local clock (helps Windows see the right time), emulate better VGA card (more colors and resolution), and allow access to 2 processors:

kvm -hda winxp.img -cdrom /winxp/ISO/WINXPSP2.ISO -m 1024 -localtime -vga std -smp 2 -boot d

Using The Virtualized Guest Windows XP

The emulated “box” will reboot once as part of the Windows XP installation process. After it comes back up, you should be good to go! You can now download programs, install programs, update the install, etc., just like you would with a regular Windows XP installation. Of course, there will be some limitations, because the emulated hardware is not exactly feature-rich.

At some point, you will “shut down” the emulated Windows XP machine. To restart it, use the a similar command – with the exception of not booting from the install disk (or ISO):

kvm -hda winxp.img -cdrom /dev/cdrom1 -m 1024 -localtime -vga std -smp 2

Accessing Host Drives

To access a local partition, first ensure that samba is installed – not running – just installed.

Then, simply add the path to the mounted partition, like so:

kvm -hda winxp.img -cdrom /dev/cdrom1 -m 1024 -localtime -vga std -smb /path/to/dir

Otherwise, you can add the share name, if you have samba already running and properly configured, like so:

kvm -hda winxp.img -cdrom /dev/cdrom1 -m 1024 -localtime -vga std -smb <share_name>

Inside the Windows guest OS, the mounted share is available at:

\\10.0.2.4\qemu

Also, from inside the guest OS, you can SSH, SCP, SFTP, FTP, or telnet to the host, depending on running host services, using this IP:

10.0.2.2

Other options are listed on the Arch Linux Wiki.

Using Overlays

I have found that this process is not entirely stable. Some combinations of host hardware, host OS, emulated hardware, and guest OS, work better than others. If I tried to emulate too much hardware, the Windows XP installation would crash, so I typically had to install using the most modest, simplest emulation.

Also, I found that this process could be slow during “boot-up” and “installation”. Maybe disabling ACPI emulation would help?

Anyway, you can quickly make a wrong turn and wreck your “virtual machine”, basically ruining your created image, in which you spent so much time setting up and installing. Fortunately, there are 2 techniques to help mitigate this annoyance.

One, with the emulator shut-down, simply copy the image file to another location or file name to back it up, like so:

cp -fp winxp.img winxp_orig_install.img

Then you can always copy the good install back over a broken install, like so:

cp -fp winxp_orig_install.img winxp.img

Ta-Da! Of course, the downside of this approach is rampant disk-usage. You need double the disk space, possibly more, depending on how many backups you make.

Another technique is using “overlays”. You can create an “overlay” of a good image like so:

kvm-img create -b winxp.img -f qcow2 winxp_20081225.ovl

Then you can boot from the overlay, just like you would any other image, like so:

kvm -hda winxp.ovl -cdrom /dev/cdrom1 -m 1024 -localtime -vga std -smp 2

The overlay contains a “diff” of the new state and the original image, so it is much smaller, since it only contains what changed. If the overlay gets corrupted, you can simply delete the overlay, create another, and go again!

You can also stack overlays, but I think this can waste diskspace too, and it requires that you keep the whole “stack” in place. Pull out one overlay in the stack, or just move it, and the whole thing tumbles down! 🙁

Based on a tip from Bryan Murdock’s blog for resizing image files, you can combine an overlay stack into a new, single, independent image file, like so:

# create a new image file, which will be the consolidated image
kvm-img create -f qcow2 winxp_new.img 30G

Download the latest clone-zilla LIve-CD (or DVD) ISO.

http://www.clonezilla.org

In a KVM session, boot from the downloaded ISO, and include your original overlay as HDA and your new image as HDB, like so:

kvm -cdrom clonezilla-live-1.2.1-17.iso -hda winxp.ovl -hdb winxp_new.img -m 1024 -vga std -boot d

Generally, you should accept the defaults, unless you know what you are doing, and of course, you do. 😉 The key is to choose the option for a “device-device disk/partition to disk/partition” clone, or something to that effect. (I don’t remember the exact wording.) Make sure you copy the complete contents, including the MBR. Your source is HDA, and your target is HDB. … The cloning takes a while. After it finishes, be sure to halt, and then start up a new KVM session, using the new image file:

kvm -hda winxp_new.img -cdrom /dev/cdrom1 -m 1024 -localtime -vga std

Try hiding the original image and overlay files to see if it works. It should!

Other Things

The default network setup is good for surfing the web, downloading stuff, and checking email. However, if you want other devices on your LAN to “see” the guest OS as another machine, you will have to create a bridge and tap. This gets a little more complicated. See the references below for more details.

If the installation or something crashes, try restarting the machine – but, don’t boot from the installation disk. Many times the install process completed “good enough” before crashing. 😮 Yeah, I know. It smells funny to me too, but it works. 🙄 Just be sure to keep lots of backup copies of your images or overlays.

References

Configure NetVanta 3120 for HP C7280 beyond VPN LAN

Networking No Responses »

Nov 122008

Problem Introduction

I frequently use a workstation that sits behind an Adtran NetVanta 3120. The NV3120 is powerful little box. It provides secure VPN access back to corporate headquarters, but it also provides a 4-port switch, a highly configurable firewall, and generally more bells and whistles than you could ever want.

Recently, I added a Hewlett-Packard Photosmart C7280 to the network. However, it sits beyond the NV3120’s LAN, so other workstations on the greater LAN can use it, like my Gentoo laptop.

The default printer configuration went great! I was printing in no time from my workstation behind the NV3120. However, scanning was another issue.

Apparently, when used in scan mode, the HP C7280 originates traffic on a non-established port, so it becomes blocked or is otherwise lost. I knew everything else was working fine, because I could bypass the NV3120 and scanning would work great! But, that was not going to be acceptable for frequent use.

Network Topology

Here is an ASCII representation of the relevant network subsection:

                                            Incoming Line
                                                  |
                                         [ Wireless Router ]
                                            192.168.1.1
                      /                           |                        \
               192.168.1.100                192.168.1.101              192.168.1.102
            [ NetVanta 3120 ]    [ HP C7280 Printer-Scanner-Fax ]    [ Workstation #3 ]
                10.10.0.110                                               Laptop
             /             \
    10.10.0.99           10.10.0.100
[ Workstation #1 ]   [ Workstation #2 ]
  Windows XP Pro           Linux

The critical path is highlighted in red.

Solution

Eventually, I called the Adtran tech support. I was pleasantly surprised to receive a call back from a support engineer in short order. He understood my problem very quickly, and he knew immediately what to do! What follows are my scribbled notes for the steps he proscribed: (Of course, your policy names and IP numbers may vary.)

Backup NV3120 configuration, in case something goes wrong. 😉
Configure NV3120 to grab static IP, not DHCP-based IP from wireless router:
Click on: System -> Public Interface -> IP SettingsComplete as follows:IP: 192.168.1.100
MASK: 255.255.255.0
DEF GW: 192.168.1.1
Add UDP relay for NetBios broadcast by HP C7280 printer (192.168.1.101) to be encapsulated and relayed through NV3120 (192.168.1.100) to its LAN (10.10.0.X) and vice-versa:Click on: Data -> UDP Relay -> IP Helper AddressAdd following addresses:
10.10.0.99 – Public (eth0)
10.10.0.100 – Public (eth0)
192.168.1.101 – vlan1
UDP Forward Protocol: netbios (port 137) [Press “Add”]
Allow traffic between 10.10.0.X subdomain and and 192.168.1.X subdomain:Click on: Data -> Firewall -> Security Zones -> Edit Security Zones -> Public
Add Policy to Zone “Public”
Type: Allow
Description: Allow 192.168.1.X to 10.10.0.X
Stateless Processing: OFF
Destination Security Zone: <Any Security Zone>
Source – Specified: 192.168.1.0 / 255.255.255.0
Destination – Specified: 10.10.0.96 / 255.255.255.240
Protocol: any

Use “arrows” to move new policy right below “VPN Selector” and before everything else.
Allow traffic between 192.168.1.X subdomain and 10.10.0.X subdomain:Click on: Data -> Firewall -> Security Zones -> Edit Security Zones -> Private
Add Policy to Zone “Private”
Type: Allow
Description: Allow 10.10.0.X to 192.168.1.X
Stateless Processing: OFF
Destination Security Zone: <Any Security Zone>
Source – Specified: 10.10.0.96 / 255.255.255.240
Destination – Specified: 192.168.1.0 / 255.255.255.0
Protocol: any

Use “arrows” to move new policy right above “NAT list wizard-ics” and below everything else.
Create policy for UDP Relay:Click on: Data -> Firewall -> Security Zones -> Edit Security Zones -> Public
Add Policy to Zone “Public”
Type: Advanced
Description: Relay netbios
Policy Action: Allow
Destination Security Zone: <Self Bound>
Stateless Processing: OFF

– Add New Traffic Selector –
Type: Permit
Protocol: UDP
Source: Any, Any
Destination: Any host, Port: “Well Known” : 137 – netbios-ns

Use “arrows” to move second from top, below “VPN selector”, but above recent “Allow 192.168.1.X to 10.10.0.X” policy.
Reassign VPN Crypto Map – It occasionally gets lost during the above changes:Click on: Data -> VPN -> VPN Peers -> Advanced VPN Policies -> Assign Crypto Maps to Interfaces:
Public VPN
vlan1 none
Save configuration changes and reboot NV3120 unit. Backup configuration again, in case something goes wrong in the future. 😉
On wireless router, add a “static route”, so traffic intended for the VPN subdomain (10.10.0.X) that leaves the printer (192.168.1.X) can find its way back to VPN subdomain and not onto global internet:
On wireless router’s configuration page (not NV3120), click on: Advanced -> Static Routes -> Add (Or, similar depending on brand and model):

Name: NV3120-VPN
Private: Off
Active: On
Destination IP: 10.10.0.96
Gateway IP: 192.168.1.100
Metric: 2Beyond the destination and gateway IP’s, the exact settings and menu navigation path will vary depending on router’s brand and model.

Explanation

Admittedly, the solution is a bit complex, but the problem is a bit complex too. Part of the complication comes from the fact that the printer broadcast various netbios-ns UDP packets to find computers on its domain. However, the computer used in this case does not exist on that domain. It exists on a private, VPN domain. So, we have to not only configure the firewall to allow traffic, but we must also relay UDP broadcasts between the two domains.

Many thanks to the Adtran support engineer, who guided me through the above steps, including configuring the 3rd party router!