MSSQL - [ODBC SQL Server Driver] invalid parameter number

This is the error when trying to load external data from external source using parameterized SQL into Excel.  To parameterize your SQL you have to use "From Microsoft Query."  This is the only external source that accepts parameters in Excel 2010.

In the example above, I setup a connection properties to call a stored procedure with 3 parameters and I keep getting the "Invalid parameter number" error.  As it turns out, the user doesn't have enough permission to execute the stored procedure but the error certainly misled me and wasted a lot of time on it.







Accessing Home PC - Part 6

This is a 6-part discussions designed to help maximize resources on your home PC.We will show you how to turn on home PC remotely, establish a secure tunnel, remote control home PC, or see your home webcam.Remember, there are always alternative, I’m here to share with you my experience.

How to remote control your home PC

Scenario:  I need to photo touch some pictures using the software on my home PC.  Any way I can operate my home PC remotely…..

Windows XP or Windows 7 provides a native remote control software called Remote Desktop.  With PuTTY service, we can redirect all Remote Desktop traffic to any remote PC through SSH protocol.  It is very secure and clean solution with no additional ports need to be opened on your router.

From command line shell enter the following command:

• -D 8080: proxy port for your browser as described in previous section
• -L 689:192.168.1.4:3389: route all traffics on local port 689 to a remote server 192.168.1.4 port 3389.
• -P 443: port on your router
• -ssh myhome.hoptp.org: ssh to your home PC using DNS

Port 3389 is the default Remote Desktop port and is used by your home PC to listen to any Remote Desktop requests. 

Once the SSH session is established, you can launch the Remote Desktop program from Start menu/Run and type in MSTSC.exe:

• Computer: enter 127.0.0.1:689

As you can see, the Remote Desktop is trying to connect a server resides on port 689 on 127.0.0.1.  This will be intercepted by PuTTY and redirected to your home PC and directed to the server 192.168.1.4 on your home network.

Alternatively, with GUI version of PuTTY, you can configure as follow:

• Source port: local port to be redirected
• Destination: home PC IP and port
• Local: check this box
• Auto: check this box

Click Add to add this new tunnels in your PuTTY and save the session.  This accomplishes the same way as explained in the command line version of PuTTY.  That’s all for remote controlling your home PC.

NOTE: Don’t enable any remote management on your router.  You can remote desktop into your home PC then browse into your router for any configuration changes.   This approach can limit any unnecessary port opening on your router.

Accessing Home PC - Part 5

This is a 6-part discussions designed to help maximize resources on your home PC.We will show you how to turn on home PC remotely, establish a secure tunnel, remote control home PC, or see your home webcam.Remember, there are always alternative, I’m here to share with you my experience.

Use SSH as a secure tunnel or proxy server

Scenario: I’m using a public wireless network, I’m worry about someone sniffs on my network packets.  Is there a way to secure my connection…..

Although this might sound too paranoid but I always surf only unimportant web sites when I’m connecting to a public network unless I have a secure tunnel or VPN (I don’t want to rely on https only).  With secure tunnel all packets between my laptop and my home PC will be encrypted.

This is a 1-tier setup and only needs to be done on your laptop.

Setting up Laptop

To establish a secure tunnel you can use the PuTTY.exe which you have downloaded from the previous lesson.  From command line shell you can issue a command:

• -D 8080: is the local port for your browser proxy service
• -P 443: is the SSH port on your home router
• -ssh myhome.hopto.org: specify using SSH and type in your DNS

A screen will pop up to establish an SSH session:

You will login with your Windows login and password to access your SSH service.  This will establish the connection with your home PC.  To shut down this PuTTY session simply type in “exit” in the PuTTY command window.

Then from your IE browser menu, go to Tools/Internet Options/Connections Tab and click on “LAN Settings” to enable proxy.

• Address: use the 127.0.0.1
• Port: use 8080 which is the port you enter with your PuTTY command

Click OK and save the settings.  What is happening from now on is all your IE traffics will be directed to a local port 8080 which then picked up by PuTTY.  PuTTY will direct all packets to your home PC and packets get routed to Internet from there.  The response packets travel the reversed path back to your browser.  With this approach, the communications between your laptop and home PC are encrypted by SSH protocol.

Alternatively, you can use the GUI version of PuTTY.exe to save a configured session as such:

 

• Host Name: your DNS – myhome.hopto.org
• Port: 443 is your SSH port
• Connection type: SSH

Then click SSH/Tunnels to configure a dynamic routing for browser traffics:

 

• Source port: use 8080 for your local port
• Dynamic: routing for browser traffics
• Set Auto

Click Add to add this configuration and leave other settings blank then go back to previous screen to save this session.  Next time you can launch PuTTY and double click Home session to connect to your home PC.

NOTE: This secure tunneling service also works as a proxy service which you can use to pass through a blocked network.  Since all browser traffics are directed through port 443 and most of the corporate network filtering will not block port 443.

Next we are off to the setup of remote controlling your home PC.

Accessing Home PC - Part 4

This is a 6-part discussions designed to help maximize resources on your home PC.We will show you how to turn on home PC remotely, establish a secure tunnel, remote control home PC, or see your home webcam.Remember, there are always alternative, I’m here to share with you my experience.

How to access resources on your home PC

Scenario: Now my computer is up but how I can access files….

So far we only talked about how to wake up your home PC.  I’m going to show you how to share the resources on your home PC.  We are going to install server software called OpenSSH for Windows on the home PC.  This software uses SSH protocol and provides you secure tunneling, proxy service, remote control, etc.  With this software installed on your home PC many things can be accomplished over this service so you can avoid open up other ports on your router for each service.

This is a 3-tier setup process that you need to run on your home PC, configure router, and setup client laptop to complete the setup.

Setting up home PC

Download the file setupssh381-20040709.zip and install the software on your home PC.  Once installed, you will need to create SSH accounts from current Windows user database.  Open a command line shell and go to C:\Program Files\OpenSSH\bin folder and type in the 2 commands:

• mkgroup –l > ..\etc\group: will create a group file in the C:\Program Files\OpenSSH\etc folder.  This file contains all the local groups in your Windows system.
• mkpasswd – l > ..\etc\passwd: will create a passwd file in the C:\Program Files\OpenSSH\etc folder.  This file contains all local user accounts and passwords information.

OpenSSH service used this information to validate login when you are accessing the SSH server.  You need to rerun these 2 commands when you want to add or remove user accounts.

Now you need to restart SSH service.  Go to Control Panel/Administrative Tools/Services, click the line “OpenSSH Server,” click stop then start buttons from the menu bar to restart the service.

Last step on PC setup is to enable the Windows firewall to allow external access to this computer.  This is accomplished by going to your Control Panel/Windows Firewall to add a new entry in the Exceptions tab. 

• Name: a meaningful name
• Port number: this is OpenSSH listening port.  Since we didn’t make a change so it is port 22 as default.

Click OK and save this entry.   Your home PC will accept any incoming SSH request through port 22 now.  You can try this from within your local home network by using the local IP.

Configure router

Now you need to enable a port forwarding rule on your router.  Login to your Actiontec MI424WR router and go to “Firewall Settings” menu and add a new rule. 

• Local Host: enter the local IP of your home PC.  Incoming SSH packets will be forwarded to this computer.
• Protocol: you should use TCP and, for security reason, select a random port instead of default SSH port 22.  I used 443 since it is not normally blocked by a corporate network.
• Forward to Port: 22 since we used the default OpenSSH service port.

Your new ‘Port Forwarding” table should look like this now:

Setting up Laptop

To connect to your home PC through SSH protocol you can download the client tool putty-0.60-installer.exe.  This client software has many utilities which allow you to connect to your home SSH service.

After completing the installation, to copy files, you can use PSCP command:

• -P 443: is the SSH port# on your router
• myaccount: is the Windows login account you sued to login to SSH server
• myhome.hopto.org: is the DNS from your DDNS provider
• c:/temp/tt.txt: is your source file on your home PC
• c:/temp: is your destination folder on your local computer

With this command you can copy files from or to your home PC.  You can type PSCP /? from the command line shell to see the available command line options.  Once you have the SSH server installed, there are plenty of user-friendly utilities out there to accomplish anything you want.  This is an extremely handy service to have.

Next, we are going to show you how to use SSH service to establish a secure tunnel and proxy out from your web browser.

Accessing Home PC - Part 3

This is a 6-part discussions designed to help maximize resources on your home PC.We will show you how to turn on home PC remotely, establish a secure tunnel, remote control home PC, or see your home webcam.Remember, there are always alternative, I’m here to share with you my experience.

Wake up home PC without knowing your home IP

Scenario: You have done 2 previous steps and feel good about it but, all of a sudden, you can’t turn on the home PC any more.  Oh, my ISP changed my assigned IP again….

Luckily, there are plenty of ways solving this issue from paying ISP a fixed IP address, install an IP updating client tool on one of your home PCs and leave it on all the time, or use the dynamic DNS on your router.  Please Google “dynamic DNS” to read some background about this (Wikipedia).  

We are going to talk about the 3rd option here.  This is a 3-tier setup process that you need to register a DDNS account, configure router, and setup client laptop to complete the setup.

Register a DDNS account

Your Actiontec MI424WR router supports dynamic DNS natively.  All you need is to sign up a free account with the provider such as http://dyndns.org or http://no-ip.com (Actiontec MI424WR router shows you a list of providers the router supports).   Write down the DNS name (never changed) you applied during the registration process.  You will also need the account name and password to configure in your router.

Configure your router

Login to your Actiontec MI424WR router and go to Advances/Dynamic DNS screen to add a new entry.  

• Host Name: is the DNS you applied with your DDNS provider
• Provider: I used no-ip.com as an example
• User Name: your account name with the DDNS provider
• Password: your password with the DDNS provider

That’s it.  From now on, a new IP is updated by your ISP the router will send an update request to your DDNS provider so you will always be up to date with your IP address.  You will be using the host name myhome.hopto.org instead of the static IP address.

Setting up Laptop

Now you can issue PING to find out your home IP using your DNS and use it in your WOLCMD.

• Ping myhome.hopto.org: to ping your DNS and return the IP address
• WOLCMD: use the IP in this command to wake up your home PC

Unfortunately the WOLCMD tool only takes IP address instead of a DNS so we have to PING the PC first to find out the IP address.   However, you can create a command line batch and name it like WOL.BAT on your desktop with a batch command script like:

ECHO OFF
SET MyIP=
FOR /f "delims=[] tokens=1-3" %%i in ('ping -n 1 myhome.hopto.org') DO (
  IF "Pinging myhome.hopto.org " EQU "%%i" SET MyIP=%%j
)
ECHO %MyIP%
WOLCMD 00:xx:xx:xx:xx:xx %MyIP% 255.255.255.255 10099

Save this script and you can just double click the icon on your desktop to wake up your home PC from now on.  This is why I like about the command line version of WOLCMD over the GUI version.

Next we will talk about how to copy files between your home PC and your other computers.

Accessing Home PC - Part 2

This is a 6-part discussions designed to help maximize resources on your home PC. We will show you how to turn on home PC remotely, establish a secure tunnel, remote control home PC, or see your home webcam. Remember, there are always alternative, I’m here to share with you my experience.

Turn on home PC remotely

Scenario: You are sitting at a Starbucks with a laptop and would like to show some pictures saved in your home PC to your friends…..

In this section we are going to show you how to turn on computer through the home router.  As you can expect it, the hurdle is to setup your router correctly.  This demo will focus on how to configure a Verizon FIOS router (Actiontec MI424WR).  Please Google your router for the setup information.

This is a 2-tier setup process that you need to configure your router and setup a client laptop to complete the setup.

Configure your home router

If you Google “Wake on LAN Verizon router,” you will find plenty of articles describing the technical reasons behind all these work (example).  The basic idea is to tell your router when a magic packet is arrived at the router on a specific external port, it should be routed/broadcasted within the internal LAN.  This step is very tricky and very much router specific so Google it if you are not using Actiontec MI424WR router.

First, login to your router and enable your local telnet session from the Advanced menu if it is not already enabled:

Check the “Using Primary Telnet Port (23) and click Apply

Next telnet into your router from a command line shell as such:

C:\>telnet 192.168.1.1

After login with your Actiontec MI424WR admin account and password then enter the highlighted commands as shown below:

• System shell: to enter shell command mode
• arp -s 192.168.1.254 FF:FF:FF:FF:FF:FF : always enter as this. 

The 2nd command will add a semi-permanent entry into the ARP table to tell the router how to broadcast a magic packet.  However, the information will be wiped out if you ever lost the power to your router.  You will need to redo this step again.

Now, you should see a line in your ARP table like this:

Next you need to setup a port forwarding rule.  From your Actiontec MI424WR “Firewall Settings” menu to add a new port rule like this:

• Local Host: this must be 192.168.1.254 for broadcast IP
• Protocol: use UDP and specify an external port.  I used 10099 here.  You can use “User Defined” to add a new item like it is shown here.
• WAN Connection Type: leave this to “All Broadband Devices”
• Forward to Port: this could be any port#.  9 is used here as default.

Click Apply to complete the setup.  Once completed you will see a port rule in the Port Forwarding table as shown below:

Setting up Laptop

Lastly, for testing, you will need a client tool to wake up the home PC.  MC-WOL which used in the previous discussion doesn’t allow you to specify port so we will need a new tool.  You can download the command line or GUIversion from Depicus.com. 

Before you run the command, find out your external IP that your ISP assigned to you.  You can do so by looking at the status on your router or type in http://www.whatismyip.com from your browser to llok up.

Then open up a command line shell and enter the command:

• 00:xx:xx:xx:xx:xx: is the MAC address of your home PC  
• 250.123.200.121: is your external IP for your home network  
• 255.255.255.255: always use this one for your mask
• 10099: is the external port# you used when you created the Port Forwarding rule earlier.

That’s it.  This should enable you to turn on your home PC any time and any where you want.  Next, we are going to show you how to get your home IP even without paying a static IP address in the.

Accessing Home PC - Part 1

This is a 6-part discussions designed to help maximize resources on your home PC.  We will show you how to turn on home PC remotely, establish a secure tunnel, remote control home PC, or see your home webcam.  Remember, there are always alternative, I’m here to share with you my experience.


How to wake up computer within your home network

Scenario: You are sitting at home watching football with your laptop but you need to access a file from another computer upstair which is off and you don’t want to get up…..

The technique to turn on remote PC is called Wake on LAN.  There are plenty of articles about WOL such as Wikipedia so I’m diving right into the technical part.  

This is a 2-tier setup process that you need to configure your home PC and setup a client laptop to complete.

Setting up home PC

First of all, you need to prepare your home PC and configure BIOS or Windows to accept the magic packets.  A very good article can be found at LifeHacker.  The main purpose of this step is to enable your network card to accept a magic packet in order to wake up your PC.

Once you are done with the basic PC preparation, you will need to find out the MAC address of your home PC.  Open up a DOS command shell and type the IPCONFIG command as such:

You will see a dump of information for all your network cards if you have more than one.  Find the one that directly connects to your router or broadband modem and the MAC address will be the “Physical Address.”  Write down the MAC address because you need this to wake up the home PC from your laptop.  WOL only works with wire connected network not wireless. 

Setting up laptop

Now, you need a client software on your laptop to send the magic packets.  The software basically broadcast magic packets to your network with the MAC address embedded.  You can download a good command line utility called MC-WOL.  Issue the command as such:

·         00:xx:xx:xx:xx:xx is the MAC address you found earlier

Make sure your home PC is in sleep, hibernate, or shutdown mode before you test this.  With this command your computer should be turned right back on.

Congratulation!  You have done the first step.  We will proceed to wake up your home PC remotely.