Category Archives: Tech of Interest

PowerShell SQL Server Agent Uses Version 2

Just as an FYI so there is no confusion: If you intend to use the PowerShell module packaged with SQL Server Agent 8, 12, 14, or 16, you must be aware it uses its own version of PowerShell regardless of the version you have installed! This can be very confusing if you use Cmdlets which require PS Version 3 and above.

Take a look at the two images below. The first sets up a PowerShell “type” job which only includes the command to display the current PowerShell version (i.e. $PsVersionTable.PsVersion). The second shows the job result form Job History. You can clearly see the PowerShell version used I version 2. Yet, I do have PowerShell version 5 installed on my PC . The version of SSMS I am using is 2016. The SQL Server I am referencing is 2008r2. However, you get the same result with SQL Server 2008 through 2016.

SQL Agent POSH

SQL Agent POSH V 2

Canon PIXMA MX870 Print Drivers Not Installing on Windows 10

This is for anyone having a problem installing the print drivers for a Canon Pixma MX870 printer. I have two machines, one is new and came with Windows 10 installed and the other had Windows 10 installed as an upgrade after restoring it to the OEM Windows 7 Home.

The newer downloaded drivers from Canon installed with no problem on the new machine but aborted with an error on the upgraded machine. The error reported was “Canon IJ Driver Installer Has Stopped Working”. After re-downloading, uninstalling, re-installing, restarting, googling, etc. I contacted Canon support. They were very responsive and after several attempts, we finally got the install to complete without error and the printer and software are working great.

The final resolution is listed in this excerpt from a Canon Support email:

  1. From the Start menu, click Settings, Devices, Printers & Scanners.
  2. Towards the bottom of the Printers & Scanners area, turn off Let Windows manage my default printer.
  3. Once this has been done, re-install the drivers again.

You may also have to delete any extra icons of the printer in the Printers & Scanners menu.

SQL Server 2012 Uses PowerShell V2 Even If It Is On Windows Server 2012

Wow! PS version 3 is great! A host of beneficial changes. If you’re like me, you probably wanted to start using it right away to take advantage of some of the new and wonderful changes.

But, HOLD ON! If you are wanting to implement any of those new and upgraded cmdlets in SQL Server 2012, you can’t. Even if you are running on Windows Server 2012, you still can’t use PS 3 cmdlets. Yes, that’s right. The reason is SQL 2012 uses PS Version 2 internally. Check it out. Here is a screen shot of the $PSVersionTable command in SQL Server 2012 on Windows Server 2012 (after right-clicking a database and selecting Start PowerShell):
SNAGHTML4c1c86

Here’s a screen shot of $PSVersionTable in Powershell on the same server
SNAGHTML4ee1c7

So, If you want to run PS 3 for any job steps on SQL 2012, you will need to choose the command type as Operating System (CmdExec) and not PowerShell.
SNAGHTML5aaf05

Powershell Impersonation – the easy way

Have you ever had a script which runs great against multiple SQL instances but then you find it failing on a new instance?

I have a script which backs up all my SQL Server databases and then copies the local backups to warehouse storage. The script runs as a Windows Scheduled task on our storage server for each SQL Instance.

Usually when I get a new SQL Server, I just add a new Windows Scheduled Task on our storage server, adjust the param to point to the new server, and the script backs up all the databases on the server\instance. Great! However, I recently found a job in which the backups were failing consistently. Not good! . It was a fairly new “test” server so I probably did not pay as close attention to it as I should have when I first set it up.

The bottom line causing the problem The only reason I knew of the failures was because of having I set up the script to write and event log for any error. However,, it was one of those “catch-all” errors with little significant information. If you don’t know already, debugging a Powershell script executed via a scheduled task is no easy task. Error codes are not easy to get. Traces are not easy to get. All in all, this type of debugging is a real PITA.

What made this even more confusing is the script ran perfectly when run from the console. No problems! BTW, I have at least 40 of these tasks running each evening without error. After a few hours of putting in numerous traps and write event logs I finally found the reason it would only fail when run as a Windows Task. The real cause of the problem was the “Log on as” user for the scheduled task did not have access to the SQL Instance! My normal testing used MY Windows credentials which has complete access rights.

Say hello to my little friend, RUNAS!

When starting a Powershell session, the run-time system sets up the environment with your Windows credentials and uses them for access to all resources. In order to have an environment whereby you don’t have to login to Windows as another user, you can use the RUNAS command (in most circumstances). Using RUNAS, I was able to start a PowerGUI session in which my credentials were the same as those being used as the logon for the Windows Task. Now, I was able to thoroughly debug the script using breaks, traces, etc. I now have a desktop Icon which starts up PowerGUI as that user for whenever I need it. You can find out more about RUNAS here, and the command I am using looks similar to this:

C:\Windows\System32\runas.exe /user:Domain\Login "C:\Program Files (x86)\PowerGUI\ScriptEditor.exe"

Configure D-Link DIR 625 for Cisco VPN

connection.Up until recently, the only way I could get my Cisco VPN to work was to place my desktop in the DMZ. I really did not like having to do that. After considerable Internet searching, I could not find any information specific to my D-Link model (DIR 625) for allowing access using a Cisco VPN. Most of the information I was able to locate referenced other models and most made reference to making sure ‘VPN pass-through’ is enabled. Well, AFAIK, the DIR 625 does not have a ‘pass-through’ setting. Other information referred to forwarding port 10000 . However, those failed to mention port 10000 is only used when you configure the VPN to use TCP rather than UDP. My configuration was given to me by our network admin and it uses IPSec/UDP. When I tried to change it, the connection no longer worked even while in the DMZ. I could only conclude the target would only accept UDP.

After a couple of hours of experimentation, I was finally able to establish a connection without being in the DMZ. So that others who may happen to fall into the same situation have a solution, I am providing it here.

The very first thing you must do is to open your D-Link Admin screen, click the Advanced tab on top, and open the Inbound Filter on the left. You’ll need to create a new filter using the IP address where your VPN connects to. You can easily find this by looking at your D-Link log right after an unsuccessful  connection attempt (you’ll see the blocked attempt). An example of an Inbound Filter is given here but with my IP partially blocked. My source range is for one IP address. Pay attention to the ‘Name’ given, it will be used again.

image

After saving your inbound filter, you now need to open the Port Forwarding item on the left of your screen. You will then add a new rule to forward UDP ports 500 through mach zehnder modulator5000 65536 to your local machine using the filter created in the previous step. Its actually pretty easy because the filter name will be in the drop-down list for the Inbound Filter item. Take a look at this example screen shot:

imageдивани

Save your settings and you should now be able to use your Cisco VPN connection without resorting to using the DMZ.