Category Archives: Computers

Stripping String to Alphanumeric in Microsoft Dynamics Nav using Regex

Leave a Reply

Within Microsoft Dynamics NAV, there are a few times which an input string needs stripping back to alphanumeric characters.

For example:

  • Unique Identifiers
  • Shipping Tracking Numbers
  • Vehicle Registration Numbers
  • etc

This can be done directly in C/AL, or also using .NET.

Strip String to AlphaNumeric CALDirectly in C/AL:
Directly in CAL, you need to create a function as follows:
Name: StrinStringToAlphaNum
Accepts: Text - 250
Returns Text - 250
Local Variables:

  • tempString - Text
  • i - Integer


The code required is:

 IF STRLEN(StringToStrip)=0 THEN
   EXIT(StringToStrip);

 FOR i:= 1 TO STRLEN(StringToStrip) DO
   BEGIN
    IF StringToStrip[i]IN ['a'..'z','A'..'Z','0'..'9'] THEN
      BEGIN
          tempString:=tempString+FORMAT(StringToStrip[i]);
      END;
   END;
  EXIT(tempString);

Strip String to AlphaNumeric DOTNETUsing .NET:
Directly in .NET needs less code, but is only suitable for Nav 2009 onwards (NAV 2009, 2009 R2, 2013, 2013 R2 and 2015).

Local Variables:

  • RegEx - DotNet - System.Text.RegularExpressions.Regex.'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

The code required is:

"Output String" := RegEx.Replace("Input String",'[^A-Za-z0-9]','');

Comparison
The Regex method is less code, but only works on newer versions of NAV. Whereas the C/AL only method works on older versions. (If you use the “classic client”, it’s an old version and doesn’t support .NET)

I’ve not tested both methods for speed, but both are really quick and not noticeable to the user.

Regex could be used for other things too, such as providing validation, classic examples being:

  • E-Mail Address
  • Phone Numbers
  • Website Addresses
  • etc

The Regular Expression Library is a good starting point for these regular expressions.

Maybe at some point I’ll provide some details on how to do these.

Trojanized PuTTY Software

Leave a Reply

Putty is a very popular open source SSH/Telnet client which can be used to connect to other devices and machines, such as Linux plus also some firewalls and other networking devices. It runs on Windows and has an installed version plus also a portable client.

Some hackers have decided to exploit this by adding code which uploads the credential information to a 3rd party server.

There’s no mention in the code to suggest the uses of this stored information, but obviously it will not be used for legal uses.

Unfortunately this is very easy to do with open source projects, the hard thing is to get critical exposure. The difference here is that the hackers used a variety of SEO techniques to get the sites hosting the compromised version up the search engine listings.

This has been done before with FileZilla. The compromised software being nicknamed Stealzilla.

Further information plus how to identify if you are running a compromised version below:

Trojanized PuTTY Software

Windows XP support deal not renewed by government, leaves PCs open to attack

Leave a Reply

It’s been over a year since Windows XP was officially made end of life, but still approximately 28% of users still use Windows XP according to NetMarketShare.

Amazingly the UK Government is one of those who have not upgraded yet….

Departments still migrating despite having had a year to move away from ancient system

Source: Windows XP support deal not renewed by government, leaves PCs open to attack

Windows 10 IoT – With Raspberry Pi

Leave a Reply

Raspberry Pi 2It’s finally here… Microsoft have released the insider preview to Windows 10 IoT (Internet of Things)

The install for the Raspberry Pi 2 looks pretty easy, similar to installing Rasbian. Once installed, you use powershell to connect to and manage the device.

Note: It’s not compatible with the earlier Raspberry.

On the Windows 10 IoT, there’s detailed instructions for installing on:

On the IoT Website, there’s a load of examples to get started, with detailed instructions into building your first app.

Time to order mine in order to experiment!

Microsoft Virtual Academy – Free IT Training, Online Learning of Microsoft Technologies

Leave a Reply

Came across this website this evening while doing some research, wish I’d have found it earlier. Very useful in brushing up your skills in Microsoft products, including Server 2012, Azure, Office 365 etc. And best of all, it’s free…!

Looking for a simple, effective way to get training on Microsoft’s Cloud technologies? Microsoft Virtual Academy!

Source: Microsoft Virtual Academy – Free IT Training, Online Learning of Microsoft Technologies

Base64 Encoding and Decoding with SQL Server – Unicode Characters

Leave a Reply

With a SQL query I’ve had to write, I’ve had to encode some text in Base64.

The following functions where extremely useful, this is based on this solution, but has been adapted to handle Unicode characters.

Convert to Base64

CREATE FUNCTION [dbo].[fn_str_TO_BASE64]
(
    @STRING NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT
            CAST(N'' AS XML).value(
                  'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
                , 'NVARCHAR(MAX)'
            )   Base64Encoding
        FROM (
            SELECT CAST(@STRING AS VARBINARY(MAX)) AS bin
        ) AS bin_sql_server_temp
    )
END

Convert from Base64

CREATE FUNCTION [dbo].[fn_str_FROM_BASE64]
(
    @BASE64_STRING NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT 
            CAST(
                CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@BASE64_STRING"))', 'VARBINARY(MAX)') 
            AS NVARCHAR(MAX)
            )   UTF8Encoding
    )
END

Base64 Encoding ExampleHere’s how you run the SQL queries:

SELECT dbo.fn_str_TO_BASE64('Hello World')

SELECT [dbo].[fn_str_from_base64](dbo.fn_str_TO_BASE64('Hello World'))

Stripping all Non Alphanumeric Characters From String in SQL

Leave a Reply

With one of the projects I’m currently working on using Taskcentre, I required all non alphanumeric characters stripping from a string in a SQL query (in order to perform an inner join with another table).

With a bit of research I found a brilliant solution here, by Even Mein (Why re-invent the wheel!)

This solution involves creating a function as follows:

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END

The function can be called as part of the SQL query as follows:

Alphabetic only:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')

Numeric only:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9')

Alphanumeric only:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')

Non-alphanumeric:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')

Microsoft Dynamics NAV 2013 R2 and VPN

4 Replies

I’m a believer of VPN for remote workers. It’s really useful for workers to be able to remotely connect to Microsoft Dynamics Nav as if working in the office. Although there is the web client, nothing beats the full client for usability.

I’ve used OpenVPN for a few projects in the past. I do like OpenVPN as it’s very powerful and also is extremely secure due the advanced encryption used. The downside is that it needs a separate client installing on the PC and it can be quite fiddly to set up. Most importantly, OpenVPN works an absolute dream with Dynamics NAV.

For a recent project, instead of using OpenVPN, I implemented L2TP/IPSec with fallback to PPTP. Although not as secure as OpenVPN, L2TP/IPSec is still pretty secure and is good for the majority of uses. The only issue I’ve had with this is accessing Nav through the VPN in Windows 8.1.

Nav was throwing the following error:

The Service Principle Name (Delegation) configuration has been set incorrectly.
Server connect URL: "net.tcp://{servername}:7046/DynamicsNAV71/Service".
SPN Identity:"DynamicsNAV/{servername}:7046"
A call to SSPI failed, see inner exception.

Additionally sometimes the following error was being thrown:

The program could not create a connection to the server.
Do you want to try again?

Very useful…!

After a lot of research and trial and error, I was able to identify that this issue is caused by the Username/Password combination used for VPN. Once the VPN connects, the credentials for the VPN was being used for the Kerberos authentication. As the VPN credentials where not those in Active Directory, this was obviously causing the issue.

VPN Microsoft Dynamics NavIn order to fix this issue, luckily a simple change to a file is required. Unfortunately this cannot be amended in the GUI.

To find this file, first browse to the following location:

%appdata%\Microsoft\Network\Connections\Pbk

Right click on the file “rasphone.pbk”, open with Notepad and look for the following line:

UseRasCredentials=1

This will be just below the name of the VPN connection in square brackets. For example if your VPN is called “Test”, your looking for “[Test]”.

This line needs amending to the following:

UseRasCredentials=0

After saving the file and connecting to the VPN again, Microsoft Dynamics Nav was working correctly.

Plotting Customers on Map from Microsoft Dynamics Nav

Leave a Reply

In this blog post, I am going to cover plotting UK customers in a map from Microsoft Dynamics Nav using Taskcentre. In this post I’m using Nav 2013R2, with the Extended Demo Database. (This has a lot more customers and data compared to the standard demo database).

In order to plot the locations on the map, firstly we need to translate the postcodes into Latitude and Longitude. On this Website, there’s a download link to a SQL script to create and populate a table with postcodes within the UK plus the appropriate latitudes and longitudes.

SSIS Import PostcodesDue to the size of the file, I had problems running the SQL query on my Virtual Machine, so instead used an SSIS job (via the Import/Export data tool on SQL Server). Depending on the amount of RAM available on the SQL server, it should be possible to run the script directly.

SQL Query Customer Postcode Longitude LatitudeOnce the file has been imported into the Nav database, I write a SQL query to extract the customer information. Note that in the Postcode table there’s no space between the first half and the second half of the postcode. Therefore using SQL we need to remove the spaces from the postcode table as part of the join.

The join on the two tables is:

REPLACE("CRONUS EXT UK Ltd_$Customer"."Post Code",' ','') = postcodelatlng.postcode

This produces a data set as follows:
Customer Postcode Data

VBScript - Customer Location StringUsing this data, we can then create a HTML page in order to visualise the data. Firstly we need to use a VBScript step which cycles through each record from the SQL query, storing the data into a variable. This variable will later be used within Taskcentre when building up the Javascript.

VBScript - Customer Location Build HTMLAfter creating this string, this is then combined with two snippets of HTML (stored within variables for ease of editing) to produce a final, complete HTML document. This HTML document is then saved to disk and can be opened in a browser. Using the FTP tools, it could be uploaded to a website if required. The document consists of some JavaScript which uses the Google Maps Javascript API. The principle could be used for any API, such as the Bing Maps API, however personally I do prefer the Google API over Microsoft’s.

The Javascript also uses the “click” event listener. This then shows a dialog box with the customer details when the icon is clicked. This contains just the customer number and name in this example, but could contain information such as full address, contact information, sales order information etc.
Customer Map

Here’s the complete task within Taskcentre:
Customer Map - Task

Google Custom MarkersThis task is only a very basic task, but with some imagination and skill, it could be used as a basis for pretty much any use. Especially when you extend the task adding the ability to have custom markers.

Some examples could include:

  • Trend analysis
  • Deliveries for a courier
  • Customers/areas with bad debt/chargebacks