How to create a self-signed root certificate and configure a Point-to-Site Azure VPN connection

The following example is for a computer running Windows 10 or Windows Server 2016.

This tutorial has 5 main steps:

  1. Create the Root and Child certificates
  2. Export the Root and Child certificates
  3. Edit the Root certificate
  4. Enter the Root certificate into the Azure Point-to-Site configuration
  5. Connect via the Azure VPN client

Following these instructions should allow you to connect to Azure using the Azure VPN client.

Create the Root and Child Certificates

1. Open the following:

> Windows Start
> (type) run (enter)
> (type) certmgr.msc (enter)
> Personal
> Certificates

This is where your certificates will appear once created so keep the certmgr.msc window open.

2. Open a Windows PowerShell console with elevated privileges.

> Windows Start
> (type) Windows Powershell (right click: Run as Administrator)

3. Copy and paste the following example into Powershell to create the self-signed root certificate and child certificate. The example creates a self-signed root certificate named “PS2RootCert” and “PS2ChildCert” that is automatically installed in “Certificates-Current User\Personal\Certificates” (i.e. step 1).

$cert = New-SelfSignedCertificate -Type Custom -KeySpec Signature `
-Subject "CN=PS2RootCert" -KeyExportPolicy Exportable `
-HashAlgorithm sha256 -KeyLength 2048 `
-CertStoreLocation "Cert:\CurrentUser\My" -KeyUsageProperty Sign -KeyUsage CertSign

New-SelfSignedCertificate -Type Custom -DnsName P2SChildCert -KeySpec Signature `
-Subject "CN=PS2ChildCert" -KeyExportPolicy Exportable `
-HashAlgorithm sha256 -KeyLength 2048 `
-CertStoreLocation "Cert:\CurrentUser\My" `
-Signer $cert -TextExtension @("2.5.29.37={text}1.3.6.1.5.5.7.3.2")

Export the Root and Child certificates

1. Once PS2RootCert has been created and is visible in certmgr right-click on it. Click All Tasks, and then click Export. This opens the Certificate Export Wizard.

2. In the Wizard, click Next
> Select No, do not export the private key, and then click Next
> On the Export File Format page, select Base-64 encoded X.509 (.CER)., and then click Next
> For File to ExportBrowse to the location to which you want to export the certificate. For File name, name the certificate file “PS2RootCert”. Then, click Next
> Click Finish to export the certificate

You should then see: “The export was successful”

2. Now export the PS2ChildCert which has slightly different steps to follow than above. In certmgr right-click on PS2ChildCert. Click All Tasks, and then click Export. This opens the Certificate Export Wizard.

> In the Certificate Export Wizard, click Next to continue
> Select Yes, export the private key, and then click Next
> On the Export File Format page, leave the defaults selected. Make sure that Include all certificates in the certification path if possible is selected. This setting additionally exports the root certificate information that is required for successful client authentication. Without it, client authentication fails because the client doesn’t have the trusted root certificate. Then, click Next
> On the Security page, you must protect the private key. If you select to use a password, make sure to record or remember the password that you set for this certificate. Then, click Next
> On the File to ExportBrowse to the location to which you want to export the certificate. For File name, name the certificate “PS2ChildCert” Then, click Next.
> Click Finish to export the certificate.

You should then see: “The export was successful”

Edit the Root certificate

1. Open the PS2RootCert certificate with a text editor, such as Notepad++.

2. Copy only the following section (excluding the header and footer between the dashes and the dashes themselves, e.g. —)

Certificate data

3. Paste the copied text into a new Notepad++ window and edit the pasted text to make sure that the text is one continuous line without carriage returns or line feeds. You may need to modify your view in the text editor to ‘Show Symbol/Show all characters’ to see the carriage returns and line feeds. Once edited copy only the text as one continuous line.

Enter the Root certificate into the Azure Point-to-Site configuration

1. Log into the Azure web portal.

2. Navigate to Virtual Network Gateways > Point-to-site configuration.

3. Paste the certificate data into the Public Certificate Data field.
Name the certificate “PS2RootCert”, or if that name is already in use name the certificate “PS2RootCert_YourUserName”, and then select Save. You can add up to 20 trusted root certificates.

Paste certificate data

4. Select Save at the top of the page to save all of the configuration settings.

Save configuration

5. Once this is done download the VPN client. This will be the configuration information needed to set up an Azure VPN.

Connect via the Azure VPN client

1. Once the VPN client file has been downloaded unzip it.
You should see 3 folders, one being AzureVPN. Inside AzureVPN is a configuration file called “azurevpnconfig”.

2. Launch the Azure VPN client. At the bottom of the screen to the left there should be a plus icon. Click it and it will give you the options of “Add” or “Import”. Click import.

3. Navigate to the AzureVPN folder and import the file azurevpnconfig. This should populate the VPN Client options with all the relevant information bar authentication type.

4. Under the Client Authentication heading, for Authentication Type choose Certificate and then for Certificate Information choose PS2ChildCert.
(If these options are showing blank then change Certificate Information to “DigiCert Global Root CA”)

Click Save.

You should now have access to Azure via the Azure VPN.

If you found this post helpful please like/share/subscribe.

How to fix Azure Error Message: “The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Cannot find the object because it does not exist or you do not have permissions.”

There can be a number of reasons for this error but below steps through a potential scenario and fix for the error as referenced in the post title.

(TL;DR it’s probably down to permissions. Code example below.)

If you are running a Data Factory pipeline that calls a Store Procedure the process may fail throwing the error “The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Cannot find the object because it does not exist or you do not have permissions.”

The first part of that error is boilerplate stuff i.e. the query cannot do what it has to do so it is rolling back.

The second part of the error is what needs to be addressed. Either the object does not exist or the credentials the process is using does not have the required permission to do what the query has specified.

If you can confirm the object exits i.e. the table, then it can only be a matter of credential permissions.

The question becomes what are you trying to do with the object?

SELECT, DELETE, TRUNCATE all require that the credentials i.e. the user, has the permissions to carry out those statements.

If your query needs to TRUNCATE a table and the credentials used by Data Factory do not have permission to perform a TRUNCATE on a table then Azure will throw the aforementioned error.

In order to resolve that problem specifically, i.e. for a user to TRUNCATE a table, the user needs ALTER permissions. Below is an example of how to apply ALTER permissions on the object TestTable in the schema dbo to the user TestUser. Just update the schema, object and user to fit your needs.

GRANT ALTER ON OBJECT::dbo.TestTable TO TestUser;
GO 

The above conforms to applying the principle of least privilege as the ability to alter objects only applies to that object specifically. In closing if the object exists look at what the query does to object and ensure the user has the required permissions to do it.

How to fix an audio echo problem with bluetooth headphones on Windows 10

I recently got shipped a new bluetooth headset for work. The headset in question was the MPOW HC5 headset. They are very comfortable and have good audio quality but when they first arrived there was a problem. The audio had a persistent echo.

The echo was isolated to the headset as there was no echo when the audio was coming through the laptop speakers.

The echo was also isolated to Windows 10 as there was no echo when the headset was plugged into an Android device.

At first I thought the problem was caused by the Windows 10 audio enhancements settings. These settings include an option for echo. However in this instance this was not the cause of the problem.

To rule out Windows 10 audio enhancements as the cause of the problem do the following:

Control Panel > Hardware and Sound > Sound > Left click on Speakers (or another output device of your choosing) > Properties > Enhancements > Check “Disable all sound effects”.

In this particular case the echo problem for my headset was that playback was happening twice for the same device albeit slightly out of sync thus creating an echo. The problem was identifiable as when I traversed to the option path below I could see that the headset was registered with the OS twice under two slightly different names.

Options path:

Control Panel > Hardware and Sound > Sound > Playback

I tested playback for both device names, by right clicking the device and clicking test, and found that one of the devices had a audio glitch. When this instance of the device was disabled the headphones worked without any echo.

If you found this post helpful please like/share/subscribe.

An icon depicting a calendar and clock

How to format SQL Server datetime as dd/mm/yyyy hh:mm:ss

If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.

The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.

Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-us')

If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.

Icon for Raspberry Pi

How to remote into a Raspberry Pi running Raspbian OS from Windows

There are a lot of tutorials out there explaining how to remote into a Raspberry Pi. Unfortunately a lot of them ignore that this functionality comes baked into even the slimmed downed version of Raspbian. Worse still a lot of them just plain do not work! This article demonstrates how to actually remote into Raspian from Windows and you do not need to write a single line of code as everything can be done through the UI. That is of course assuming you are not a masochist and actually installed the UI.

(FYI these instructions are for connecting over the same network)

Virtual Network Computing

You will gain remote access and control of the Pi using technology called VNC. In computing, Virtual Network Computing is a graphical desktop-sharing system that uses the Remote Frame Buffer protocol to remotely control another computer.

To do this you will need to install a VNC viewer software on your Windows 10 PC. Microsoft’s own Remote Desktop Connection software can be a bit temperamental so it is recommended you use the free and very light software “VNC Viewer”. You will not need to install a VNC Server on the Pi as it is already preinstalled.

Setting up the Windows PC

VNC Viewer can be downloaded for free from RealVNC at the following link, or you can search for it in your web browser of choice if you would prefer. Once you have it installed you will need to set up the Pi to receive VNC connections.

Setting up the Pi

To enable the VNC connections follow these steps.

On the Pi go to the Application Menu, the Raspberry Icon to the top left of Home screen.

Preferences > Raspberry Pi Configuration > Interfaces > then enable the VNC option.

Next you will need to enable the VNC Server to display in the Application Menu which you can do by follow these steps.

From Preferences in the Application Menu go:

Preferences > Main Menu Editor > Other > then enable VNC Server.

Then click okay to apply the setting.

Now when you go to the Application Menu (Raspberry Icon on Home screen) you will be able to access the VNC Server application via the category “Other”.

Remoting into the Pi from Windows

After opening the application you should see under “Connectivity” the IP address that can be used for other computers on the same network to connect to the Raspberry Pi. (If an IP address is not displayed make sure your WiFi is on)

Jump over to your Windows 10 PC and open the VNC Viewer app.

Type the IP address displayed by the VNC Server into the Address bar.

When prompted enter the username and password you use to access the Raspberry Pi (possibly pi and raspberry). You should now have remote access to the Pi.

The best part is on reboot the VNC Server should start automatically.

If you found this post helpful please like/share/subscribe.

An icon of a headset with a spanner meaning sound settings

How to adjust your Windows 10 volume settings so virtual meeting participants are at the same volume level

You have probably experienced a virtual meeting or online lecture were you could barely hear a person when they spoke. It might have been down to their audio setup, e.g. they were not using a headset and were relying on their laptop built-in microphone, or they might just be especially quiet when they talk. So in an effort to hear them better you maxed out the volume on your PC only for someone else in the chat to chime in with an excessively loud question or comment. If you were wearing a headset in that situation you might have ended up with burst eardrums. Thankfully there is something you can do on your end to normalize the volume of the meeting.

(FYI it is not recommended you test this for the first time during an important business meeting. Sound card drivers etc. can be a bit unpredictable and can even behave differently depending on what other applications you may have open at the time.)

What is Audio Normalization

Audio normalization is a process that increases the audio level by a constant amount so that it reaches a target or norm. Normalization applies the same level increase for the entire duration of the audio stream.

You are probably thinking at this point that if normalization brings up the volume of the quiet person wouldn’t it also make the loud person louder?

That is not the case. It averages out the loudness by leveling the audio output. So when the louder person interjects they should be at a similar volume to the person speaking quietly.

Normalizing the meeting volume

This is achieved through Windows 10 Sound settings and a feature called “Loudness equalization” and there are two ways to turn this feature on.

The first way is via the search bar at the bottom of your display.

Type “Sound settings”

Click on the option that appears.

When the Sound settings window opens look for “Device Properties” under “Output”, i.e. speakers/headphones etc.

In “Device Properties” click on “Additional device properties” to the right hand side of the window.

Open the “Enhancements” tab.

Make sure “Immediate mode” is ticked then scroll down to “Loudness equalization”.

Once the “Loudness equalization” box has been ticked you should hear that the volume of people speaking quietly has been increased. This means you will be able to lower down the overall system volume and when other people speak you should be able to hear them also without it being deafening.

The other way to get to this feature is the old fashion way via Control Panel.

Control Panel > Hardware and Sound > Sound > Left click on Speakers (or another output device of your choosing) > Properties > Enhancements > Check “Loudness equalization”.

If you found this post helpful please like/share/subscribe.

An icon of a form

How to set up a free WordPress contact form without plugins

Summary

This solution uses Google Forms and a tiny bit of scripting. The end result is a form that will be neatly embedded in your website that will send an email notification, to any email address (or addresses) you desire, when the form has been filled in and submitted. This solution is very versatile and can be applied to numerous use cases, for instance it could be any website not just a WordPress site and it could be any type of form. The JavaScript is very straight forward and can be easily customised to fit different needs.

Prerequisite

You will need a Google (Gmail) Account in order to follow these instructions. This Gmail account will not be visible to the user who submits the form but it is recommended you do not use your personal Gmail account and instead create a new one.

Instructions

Firstly you will need to create a Google Form.

Sign into the Google Account you intend to associate this form to.

Search for Google Forms in your search engine of choice.

Click on the default Contact form and edit as required, i.e. fields, theme etc.

It is recommended for the purposes of these instructions you create a contact form with the fields Name, Email, Website and Comment as these are the fields the script will look for. Once you understand the whole process you can customise the solution to fit your specific needs.

Once done go to the form settings (Gear icon).

The setting “Requires sign in” is set to limit to 1 response by default.

Remove this requirement or a nasty popup will appear on the contact page of your website requiring the sites visitors to log into Google to fill out the contact form.

(Obviously you can leave this restriction in place if that is the behavior you desire.)

At this point you can test the form.

Testing the Form

Go to the Send button and click it and this will bring up the various share options.

In this instance chose the second option, i.e. not the email option but the url (chain icon) option.

Copy the url and paste it into a new browser window, this will show you the form from a users perspective.

Fill in the form with mock data and submit it.

Return to the form editing page and you will see “1 Response” above the form input boxes.

Clicking on this button brings up the responses and response statistics.

At this point your form should be ready to use.

Form Submission Email Notifications

On the form editing page go to the More options (the three vertical dots) and among the options there will be the “Script Editor” option.

Click on it and the Script Editor window will appear.

Go to File > New > Script file and “Enter new file name” as “FormContact”.

Delete all of the text in the text editor window i.e. “function myFunction() {}”

Then copy and paste the text below into the editor window.

function onFormSubmit(e) {
	//If you run this script from the script editor it will throw an error as the code is not being passed values from an active form
	//To test this script you should have a Contact form prepared with the fields Name, Email, Website and Comment
    //You can then submit the Contact form after populating the fields
	//To run logged tests uncomment the code below that starts with "Logger.log", or simply submit forms and review the received emails
	//You can view the log by going to View > Stackdriver Loggins > Apps Script Dashboard	
  
//Email Address that will receive the notification
  var emailTarget = "c.kent@dailyplanet.com"
//To send notifications to multiple email addresses uncomment the line below and delete the line above
//var emailTarget = "c.kent@dailyplanet.com, b.wayne@waynecorp.com" 
  
//Capture the form input values as variables
  var frm = FormApp.getActiveForm().getItems();
  var nameGiven = e.response.getResponseForItem(frm[0]).getResponse();
  var emailAddress = e.response.getResponseForItem(frm[1]).getResponse();
  var websiteUrl = e.response.getResponseForItem(frm[2]).getResponse();
  var commentGiven = e.response.getResponseForItem(frm[3]).getResponse();
  
//Create the variable htmlPage that will store a basic HTML page including the style specifications for a simple HTML table
  var htmlPage = `
<!DOCTYPE html>
<html>
<head>
<style> table {
  font-family: arial, sans-serif;
  border: 1px solid black;
  border-collapse: collapse;
  width: 100%;
}
table td {
  border: 1px solid black;
  padding: 10px;
}
</style>
</head>
<body>
`

//Add a HTML Table inside the htmlPage variable that will display the captured form values via email
  htmlPage += '<div><table>' 
  +'<tr><td>Name</td><td>' + nameGiven + '</td></tr>' 
  +'<tr><td>Email</td><td>' + emailAddress + '</td></tr>' 
  +'<tr><td>Website</td><td>' + websiteUrl + '</td></tr>' 
  +'<tr><td>Comment</td><td>' + commentGiven + '</td></tr>' 
  + '</table></div></body></html>'

  //Logger.log("Name: " + nameGiven + "Email Address: " + emailAddress + "Website: " + websiteUrl + "Comment: " + commentGiven);
  
//Send the notification email via the Gmail account to any email address provided as the first option    
   GmailApp.sendEmail(emailTarget, 'New Contact Form Submitted', '', {htmlBody: htmlPage});
}

That is all the JavaScript code needed to capture the form variable values and send them wrapped in a simple html table via email.

By default the code is set to send emails to “c.kent@dailyplanet.com” solely.

You will need to update this email address (keep the quotes!) to the email address you want to receive form emails or else you will really annoy Superman.

Similarly you can change “c.kent@dailyplanet.com, b.wayne@waynecorp.com” if you want multiple email address to receive form emails. Just put a comma between email addresses with either end of the string of email addresses wrapped in quotes.

All of the code is now in place but a trigger needs to be set up to run the JavaScript code.

Go to Edit and click on “Current Project’s triggers”.

Add a new Trigger.

For “Choose which function to run” choose “OnFormSubmit”.

For “Select event source” choose “from Form”.

For “Select event type” choose “On Form Submit”.

For “Failure notification settings” choose whatever frequency suits your use case.

Now when the form is submitted it will call the OnFormSubmit function which will run the JavaScript code you entered.

Now that you know how to set up a contact form and can see how the variable values are captured in the JavaScript code you probably now have a good understanding of how to edit both the form and code to fit your specific needs.

Keep in mind the form variable values are captured in order of appearance in the form.

Adding the Contact Form to the WordPress site

This next part covers specific instructions for adding the form to a WordPress site but if you have any web development experience you will see how easy this process is to incorporate into any website HTML page.

On the Google form editing page click the Send button.

For the send via options choose the third option, embedded HTML, symbolised as angled brackets < >.

Copy the code that appears below the send via options.

(If you just needed the form for a website then take that code and embed it into your website page and you are done. If you need the form for a WordPress site keep reading.)

Log into your WordPress site.

Under My Site go to Pages and then select the page you want to use the form in or create a new page.

In the body section of the page click on the plus (+) block to add a new block.

Search for HTML and choose Custom HTML.

Paste the code from the Google Form into the block.

You are done.

Conclusion

Now when you publish the page you will have a new contact form that will email new form submissions to email addresses of your choosing and it didn’t cost you a dime.

If you liked this post please leave a like and share.

Icon for Raspberry Pi

How to find a program’s directory in Raspbian OS

For Linux distros most programs are stored in the /usr directory. There is no “Programs Files” directory like for Windows. The executables are typically stored in /usr/bin with additional stuff in /usr/share and libraries in usr/lib etc.

There is also usr/local where stuff gets put when you do the compiling yourself. With /bin tending to be command line tools and /sbin being the directory for command utils only for root.

The quickest way to find the actual directory a program resides in is through terminal using the command “which”.

Here are some examples:

which nano

which gpicview

which chromium-browser

The programs referenced are the preinstalled text editor, image viewer and web browser. All of these examples will return the /usr/bin/ directory.

Note chromium is referred to as “chromium-browser” as typing “which chromium” will return no result as that is the incorrect name for the program. If you are unsure of a program name, run the program and then look for it in task manager to confirm.

An Icon of a keyboard

How to use an Android device as a keyboard and trackpad for a Raspberry Pi

Anyone who has set up a Raspberry Pi Zero W will know it is a bit limited by IO, such is the trade off for such a small form factor. I recently went through a set up that was especially awkward as there was no WiFi available. I tried to use an Android hotspot but unfortunately the Pi could not see the Android device at all. The only option I had was to tether the Pi to the Android via USB. This worked. The Pi had access to the mobile data of the Android device however it seems that the power draw from the Android device meant there was not enough power left to power the wireless USB receiver for the keyboard and mouse combo. So I was left with mutually exclusive options of either access to the internet or the ability to use a keyboard and mouse. Luckily there is always a plan C.

Prerequisites:

You will need a mouse that can connect to the Pi either by USB or Bluetooth. The OS used was Raspbian but this solution should work with other Distros.

Solution:

The Raspberry Pi Zero W also comes with Bluetooth built in so there was the option to make the Pi discoverable and connect a Bluetooth keyboard and mouse. I do not have a physical Bluetooth keyboard or mouse but thankfully there is an App for that, multiple ones actually.

The App I used was the “Serverless Bluetooth Keyboard & Mouse for PC/Phone” from Google Play, available here.

It is free (with ads) and very easy to set up. In terms of performance it provided me with a usable keyboard (like Gboard) with half of the device screen acting as a very responsive track pad. I certainly would not want to compose a thesis with this setup but for typing a few words and clicking a few links it is perfectly serviceable.

I experienced what maybe a slight bug during set up however but I resolved the problem in a minute or two.

Problem and Fix:

Firstly you will need to make the Pi discoverable via Bluetooth. This is the only time I needed to make use of a physical mouse. The option to turn on Bluetooth and make the device discoverable is to the top right of the Raspbian Home screen.

When I tried to connect the Android and Pi together through the App it would not work. The Pi was not discoverable by the App despite the functionality to discover devices being built in to the App.

To connect the devices I first had to connect the Android device and Pi together via their respective operating systems. This threw an error on the Pi but the Android device was visible to it. I then removed the Android Bluetooth connection from the Pi and again tried connecting the Pi via the App. This worked.

If you found this post helpful please like/share/subscribe.