Renaming an MSSQL instance (Invalid Urn filter on server level)

Unknown | 2015-03-03

If you rename a host on which MSSQL is running, the MSSQL server will not be renamed internally, causing the error message “Invalid Urn filter on server level” to pop up. What this means, in this case, is that the connected SQL server doesn’t match the name the client has for it.

You can check this by running the following query:

SELECT @@SERVERNAME AS 'Server Name';

To correct the server name, from (for the sake of example) “foo” to “bar”, run this query:

sp_dropserver 'foo';
GO
sp_addserver 'bar', local;
GO

Restart the server and run the SELECT query again to verify the change.

More information available here, archived here.

Restarting a VPN client on pfSense through the CLI (SSH)

Unknown | 2015-02-26

I recently had some issues with a flaky VPN service. I wanted to make a little script I could run on a different machine that would restart the VPN connection, should it break. This particular VPN connection included a DNS service for the remote .local-domain, which I decided to use for testing, but you could in theory use any valid connection test.

Either way, pulling the correct function names and options from /usr/local/www/status_services.php – following the reference to /etc/inc/service-utils.inc – I came up with this script:

#!/usr/bin/env bash
if ! nslookup -timeout=2 remote.server.local >/dev/null 2>&1; then
    echo "VPN not OK - restarting"
    ssh my.pfsense.ip /usr/local/bin/php -q <<-EOF
        <?php
            include('service-utils.inc');
            service_control_restart("openvpn", array('vpnmode' => 'client', 'id' => '3'));
        ?>
    EOF
fi

This will connect to my pfSense box using passwordless SSH login from a trusted machine, and restart the VPN connection.

The client ID was obtained from the restart link in the pfSense web interface:

vpnid

UPDATE

I ended up finding the browser VPN ID too hackish, and made a script that looks up the ID in the pfSense config instead. Might also be useful for adapting to future issues. Here: http://www.dhampir.no/stuff/bash/pfsense-vpnreset

Weakening Windows Server 2012

Unknown | 2015-02-03

Sometimes, when I’m just screwing around with some software, Windows security just gets in my way.

Here are the things I wanted gone this time, and where to find them:

Strong password enforcement and password aging:
Administrative tools => Local Security Policy (secpol.msc) => Account Policies => Password Policy

Ctrl+alt+del to login:
Administrative tools => Local Security Policy (secpol.msc) => Local Policies => Security Options => Interactive Login: Do not require CTRL+ALT+DEL

Disable the Shutdown Event Tracker:
Server Manager => Tools => Group Policy Editor (gpedit.msc) => Computer Configuration => Administrative Templates => System => Display Shutdown Event Tracker

Disable Internet Explorer Enhanced Security Configuration:
Server Manager => Local Server => IE Enhanced Security Configuration (it’s an option in the content view)
ieesc

Allow shadowing or full interaction with remote desktop sessions without the user’s consent
Server Manager => Tools => Group Policy Editor (gpedit.msc) => Computer Configuration => Administrative Templates => Windows Components => Remote Desktop Services => Remote Desktop Session Host => Connections => Set rules for remote control of Remote Desktop Services user sessions

Force a given wallpaper on everyone (mwahahah!):
Server Manager => Tools => Group Policy Editor (gpedit.msc) => User Configuration => Administrative Templates => Desktop => Desktop => Desktop Wallpaper

Screw around with everyone’s color settings (double mwahahah!):
Server Manager => Tools => Group Policy Editor (gpedit.msc) => Computer Configuration => Administrative Templates => Control Panel => Personalization => *

Finish by running gpupdate /force in a command prompt to update the settings.

To be continued…

Using a Windows 8 upgrade key for a clean install

Unknown | 2015-01-28

I recently faced reinstalling a Windows 8 machine, without knowing the key used for deployment on it was an upgrade key. Not wanting to wipe the drive (again), dig up a Windows 7 key, then installing Windows 8 again, I googled and found this advice at Lifehacker, which worked brilliantly:

If you do a clean install using the Windows 8 Upgrade Assistant, you should be fine, but if you’ve already formatted your drive or you’re moving to a new drive, you can’t do a “clean install” without installing an old version of Windows first. It’ll let you install Windows 8 cleanly, but when you go to activate, you get an error 0x8007007B, saying your product key can only be used for upgrading.

If you get that error, here’s how to fix it:

  1. Press the Windows key and type regedit. Press enter to open the Registry Editor.
  2. Navigate to HKEY_LOCAL_MACHINE/Software/Microsoft/Windows/CurrentVersion/Setup/OOBE/ and double-click on the MediabootInstall key in the right pane.
  3. Change the key’s value from 1 to 0.
  4. Exit the Registry Editor, press the Windows key again, and type cmd. Right-click on the Command Prompt icon and run it as an administrator.
  5. Type slmgr /rearm and press Enter.
  6. Reboot Windows.

When you get back into Windows, you should be able to run the Activation utility and activate Windows as normal, without getting an error. Obviously, you could use this trick for evil, but it has its legitimate place too—if, say, you’re upgrading your hard drive and want to do a fresh install on it, or if you formatted your drive before upgrading.

Writing a newline to separate commands in XChat

Unknown | 2015-01-14

Just learned something neat: In XChat, if you hold ctrl and shift, and then press “u”, followed by “a”, you get a weird character that signifies a line break. This can be used to put several commands on one line in the configuration, or to type several lines before spamming them all at once into a channel.

ctrl+shift+u+a

MSSQL Mass Copy

Unknown | 2015-01-14

To avoid locking a table for a damn long time, this is how Google told me to do mass copies of data in Microsoft SQL Server:

DECLARE @BatchSize INT = 1000
DECLARE @IdMax INT = 25179272
DECLARE @i INT = 0

WHILE @i <= @IdMax BEGIN
	INSERT INTO [mydata].[dbo].[ProjectEventValues] WITH (TABLOCK)
		([projectId]
		,[descId]
		,[index]
		,[controllerId]
		,[timestamp]
		,[value])
	SELECT
		s.projectId,
		s.descId,
		s.[index],
		s.controllerId,
		s.[timestamp],
		s.value
	FROM [mydata].[dbo].[ProjectEventValues_temp]s
	WHERE s.eventId >= @i AND s.eventId < (@i+@BatchSize)
	SET @i = @i + @BatchSize
	PRINT @i
END
GO

Here for future reference.

..and another one, for personal reference:

USE [mydata]
GO
DECLARE @PID uniqueidentifier
DECLARE @i int = 0

DECLARE cur CURSOR LOCAL FOR
SELECT projectId FROM Projects WHERE deleted IS NULL

OPEN cur

FETCH NEXT FROM CUR INTO @PID

WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @i
    PRINT @PID
    SET @i = @i + 1

    INSERT INTO [mydata].[dbo].[ProjectEventValues.new] WITH (TABLOCK)
        ([projectId]
        ,[descId]
        ,[index]
        ,[controllerId]
        ,[timestamp]
        ,[value]
        ,[aggregated])
    SELECT
        s.projectId,
        s.descId,
        s.[index],
        s.controllerId,
        s.[timestamp],
        s.value,
        0
    FROM [mydata].[dbo].[ProjectEventValues]s
    WHERE s.projectId = @PID
    AND s.timestamp >= '2015-03-04'
    AND s.timestamp < '2015-03-05'

    FETCH NEXT FROM CUR INTO @PID
END

Yeh.

SMPS Repair

Unknown | 2015-01-04

I recently had to repair the Switched Mode Power Supply for my RockWheel (a one-wheeled electric “vehicle”). Looking around for some general guidelines, since I hadn’t touched line voltage in a while, I found this guide over here useful. Archived here for archival purposes…

Moving Windows’ Offline File Cache away from C:\

Unknown | 2014-12-17

Got a small SSD as your C drive? So do I.
Storing your documents on a network drive? Yep.
Want to index that network drive, to include it in libraries, searches, etc? You bet.

You’ll want to move your offline cache.

  1. Create a folder for your offline file cache. Something like D:\Cache
  2. From an elevated command prompt type the following: takeown /r /f C:\Windows\CSC
  3. Open the Sync Center (typing “sync” into the start menu search field should do) and go to Manage Offline Files.
  4. Click Disable Offline Files and restart the machine.
  5. From an elevated command prompt issue the following commands:
    1. rd /s C:\Windows\CSC
    2. mklink /J C:\Windows\CSC "D:\Cache"
      (or whatever your folder name is, but be sure to use the quotes if you have space(s) in the name).
  6. Reopen the Manage Offline Files window and Enable Offline Files.
  7. Restart the machine.