List of software offerings in the Microsoft Office space

So I wonder sometimes is it possible to earn a living writing software for Excel. As a ongoing piece of market research here is a list of software offerings which I will amend as I chance upon such offerings.

Name	Standard Cost	Enterprise Cost	Pack of 4*
Templater	$199	$1,999
Planatech Solutions - XLL Plus	$1,095		$3,504
DI Management Services Store -CryptoSys API v5 Developer Licence	$99.00
Enthought pyxll	$250 down to $100 see link for discount schedule

Excel on the Server? No thanks, Xml ADO recordsets please

I have encountered a variety of what I would call "Excel on the server" technologies and these include Microsoft SharePoint Server but also there is an Apache (and thus open source) Java Apache-POI, I chanced upon the latter whilst looking at StackOverflow bounties. Mulling the use case of generating excel workbooks on a server I think that the majority use case is the creation of reports, and the best way to do this is pivot tables and charts based on those pivot table. But is the creation of pivot tables in an Excel workbook on a server a smart thing to do? If you look at some sample Apachi-POI code it would appear a bit clunky.

In this older post I show worksheet cell contents converted to Xml and then to an ActiveX Data Objects (hereafter ADO) recordset. Use of ADO recordsets as a means to marshalling data between a client desktop and a computer room server should not be underestimated. Indeed, in the era of Visual Basic 6 the N-tier architecture was Windows DNA and all these distributed architectures require some state container/vessel to marshal data from one tier to another. For Windows DNA an ADO recordset that the state marshalling container/vessel.

So I would recommend web services emitting a Xml version of an ADO recordset to an Excel workbook. The magic line of code that eliminates a ton of scripting is the CopyFromRecordset method, it is the penultimate line in the following VBA example. You'll need the Xml to be saved into a file (I have chosen c:\temp\xl_persists_2.xml)

<xml xmlns:x="urn:schemas-microsoft-com:office:excel" 
    xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
    xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
    xmlns:rs="urn:schemas-microsoft-com:rowset" 
    xmlns:z="#RowsetSchema">
<x:PivotCache>
<x:CacheIndex>1</x:CacheIndex>
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly">
<s:attribute type="Col1"/>
<s:attribute type="Col2"/>
<s:attribute type="Col3"/>
<s:extends type="rs:rowbase"/>
</s:ElementType>
<s:AttributeType name="Col1" rs:name="FirstName">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col2" rs:name="FamilyName">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col3" rs:name="Role">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
</s:Schema>
<rs:data>
<z:row Col1="John" Col2="Snow" Col3="President"/>
<z:row Col1="Ygritte" Col2="Wild" Col3="Vice-President"/>
</rs:data>
</x:PivotCache>
</xml>

For the VBA you'll need Tools->References to Microsoft ActiveX Data Object 6.1 Library (or similar) and Microsoft Xml, v6.0 (or similar)

Function RecordsetAsXml() As String
    '* in this example I'm loading from a file but it can be a webservice.
    
    RecordsetAsXml = VBA.CreateObject("Scripting.FileSystemObject").OpenTextFile("c:\temp\xl_persist_2.xml").ReadAll
End Function

Sub LoadXmlRecordset()

    'Tools->References:Microsoft ActiveX Data Object 6.1 Library
    Dim rs As ADODB.Recordset
    
    'Tools->References:Microsoft Xml, v6.0
    Dim domRecordsetAsXml As MSXML2.DOMDocument60
    Set domRecordsetAsXml = New MSXML2.DOMDocument60
    domRecordsetAsXml.LoadXML RecordsetAsXml
    Debug.Assert domRecordsetAsXml.parseError.ErrorCode = 0

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open domRecordsetAsXml
    
    '* placed a little under the original data for comparison
    Dim rngOrigin As Excel.Range
    Set rngOrigin = ThisWorkbook.Worksheets.Item(1).Cells(6, 1)
    
    Dim lFieldLoop As Long
    For lFieldLoop = 0 To rs.Fields.Count - 1
        rngOrigin.Offset(0, lFieldLoop).Value = rs.Fields(lFieldLoop).Name
    Next lFieldLoop
    
    rngOrigin.Offset(1).CopyFromRecordset rs

End Sub

From this point it is very easy to generate a pivot table and charts from the table of data zapped into the worksheet by CopyFromRecordSet. So, I prefer Xml ADO recordsets to Sharepoint or Apache POI generated workbooks.

ECDSA - a common curve between OpenSSL, C# and C++ Microsoft CNG

This article has been migrated to our sister C# blog.

Elliptic Curve Digital Signatures with C#

This article has been migrated to our sister C# blog.

RSA Digital Signatures with C#

This article has been migrated to our sister C# blog.

Use VBA to make a folder shareable

So some poor guy got heavily downvoted asking, reasonably, how to use VBA to make a folder shareable. Here is the answer, I cannot post on SO because the question is on hold.

Option Explicit

Sub TestRajeshS()
    '* For Rajesh S
    '*
    '* How to make a folder shareable
    '* needs admin permissions!
    '* Answer to https://stackoverflow.com/questions/45525238/how-can-i-make-the-folder-sharable
    
    '* copyright
    '* based on https://blogs.msdn.microsoft.com/imayak/2008/12/05/vbscript-for-creating-and-sharing-a-folder/#
    '* Owner - Imayakumar J.   Date - December 5 2008
    '* end of copyright
    
    '----------------------------------------------------
    'Create folder
    '----------------------------------------------------
    
    Dim filesys As Object
    Set filesys = CreateObject("Scripting.FileSystemObject")
    
    Dim sFolderName As String
    sFolderName = "n:\ShareThis"
    
    If Not filesys.folderexists(sFolderName) Then
        filesys.createfolder sFolderName
    End If
    
    '---------------------------------------------------------
    ' Check if another shar with the same name exists
    '---------------------------------------------------------
    
    Dim strComputer As String
    strComputer = "."
    
    Dim objWMIService As Object
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
        
    Dim colShares As Object
    Set colShares = objWMIService.ExecQuery _
        ("Select * from Win32_Share Where Name = 'MYSHARENAME'")
    
    Dim objShare As Object
    For Each objShare In colShares
        objShare.Delete
    Next
    
    '-----------------------------------------------------
    ' Share the created folder
    '-----------------------------------------------------
    
    Const FILE_SHARE = 0
    Const MAXIMUM_CONNECTIONS = 25
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
        
    Dim objNewShare As Object
    Set objNewShare = objWMIService.Get("Win32_Share")
    
    Dim errReturn As Variant
    errReturn = objNewShare.Create _
        (sFolderName, "MYSHARENAME", FILE_SHARE, _
            MAXIMUM_CONNECTIONS, "Sample share created with Microsoft Scripting Runtime.")
    
    If errReturn = "0" Then
        Debug.Print "Success"
    Else
        '* did you forget to run as admin?
        Debug.Print "Task Failed - did you forget to run as admin"
    End If
    
    '---------------------------------------------
    ' Script End
    '-------------------------------———————
    

End Sub

Some notes on Ivan Ristic's OpenSSL Cookbook

So Ivan Ristić has donated some chapters of OpenSSL documentation free which is welcome and we thank him for this. From the point of view of an Excel developer, I'm interested in digital signatures, shipping a C++/C# component with a digitally signed licence file to prevent those who have not paid but who have acquired a copy of the executable from running the software.

So, I will give some notes and quotes.

Key Generation

Key algorithm

For SSL keys everyone uses RSA and ECDSA keys are yet to be widely supported by CAs

Key size

When generating a key use a longer length than the default. Bit for bit Eliptic Curve keys are stronger.

Today, 2048-bit RSA keys are considered secure, and that’s what you should use. Aim also to use 2048 bits for DSA keys and at least 224 bits for ECDSA

Passphrase

Using a passphrase with a key is optional, but strongly recommended ... passphrases should be viewed only as a mechanism for protecting private keys when they are not installed on production systems. In other words, it’s all right to keep passphrases on production systems, next to the keys.

Command Line Key Generation

RSA Keys

To generate private key use the following command, new file is fd.key. Don't forget your passphrase!

c:\OpenSSL-Win64\bin\openssl genrsa -aes128 -out fd.key 2048
Generating RSA private key, 2048 bit long modulus
.......+++
..........................................................................+++
e is 65537 (0x10001)
Enter pass phrase for fd.key:
Verifying - Enter pass phrase for fd.key:

To generate corresponding public key use the following command, new file is fd-public.key. Don't forget your passphrase!

c:\OpenSSL-Win64\bin\openssl rsa -in fd.key -pubout -out -fd-public.key
Enter pass phrase for fd.key:
writing RSA key

I skipped showing output because RSA keys are long hence the attraction of elliptic curve keys.

Elliptic Curve Keys

Here how to generate an elliptic curve key.

> openssl ecparam -genkey -name secp256r1 | openssl ec -out ec.key -aes128
using curve name prime256v1 instead of secp256r1
read EC key
writing EC key
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:

and to show its output

C:\OpenSSL-Win64\bin> openssl ec -in ec.key -text -noout
read EC key
Enter PEM pass phrase:
Private-Key: (256 bit)
priv:
00:a8:bc:2b:6e:9a:15:98:b5:5b:66:56:4e:8c:54:
ab:1a:df:85:25:60:d4:39:6e:b0:88:4f:ee:ea:fd:
e9:f5:93
pub:
04:8e:8b:a6:6f:97:8b:a7:30:59:72:7d:e1:f2:2e:
bd:7e:7e:ee:61:29:3e:a9:3d:41:2f:da:d0:71:67:
30:63:f8:86:dd:42:bd:0a:7b:67:7b:a1:93:12:61:
d0:aa:14:4e:c4:5e:97:64:7d:ae:75:97:c4:66:42:
87:14:08:d6:01
ASN1 OID: prime256v1
NIST CURVE: P-256

Referencing David DeRosa for extracting public key only

C:\OpenSSL-Win64\bin> openssl ec -in ec.key -pubout -out ec-pub.key
read EC key
Enter PEM pass phrase:
writing EC key

And to show the output of this reduced file use this

C:\OpenSSL-Win64\bin>openssl ec -in ec-pub.key -pubin -text -noout read EC key
Private-Key: (256 bit)
pub:
04:8e:8b:a6:6f:97:8b:a7:30:59:72:7d:e1:f2:2e:
bd:7e:7e:ee:61:29:3e:a9:3d:41:2f:da:d0:71:67:
30:63:f8:86:dd:42:bd:0a:7b:67:7b:a1:93:12:61:
d0:aa:14:4e:c4:5e:97:64:7d:ae:75:97:c4:66:42:
87:14:08:d6:01
ASN1 OID: prime256v1
NIST CURVE: P-256

Obviously keep the file with the private key secret [and not on a blog :) ]. You distribute the public key because that is what your client side digital signature validation code will use.

DSA Keys

I skipped the DSA section as I'm not interested in it.

Signing a licence file

So we give an example licence file which articulates the hardware fingerprint (unique to each machine), customer and the software components sold (or being evaluated). And we have chosen JSON as our file format.

{  
   "hardwareFingerprint":{ 
      "volC":"4715-932C"
   },
   "customer":{
      "name":"W Churchill"
   },
   "softwareLicences":[  
      {  
         "name":"spellChecker",
         "licenceType":"90 day evaluation",
         "expiryDate":"2017-Nov-05"
      },
      {  
         "name":"Chess",
         "licenceType":"paid",
         "expiryDate":"Never"
      }
   ]
}

Links

OpenSSL Cookbook

Cryptography - VBA code for Wikipedia's RSA example

So, following on from studying some cryptography I can give some VBA code which implements RSA or at least the example given on the RSA Wikipedia article. So the two primes p and q are given; calculating n is easy (multiply); we can use worksheet function lcm for the totient;e is given;I hunted around for some logic for "module multiplicative inverse" and finally we have the components for the private and public keys.

Also shown is some code to encrypt a message (very short one character "A" represented by ASCII 65) and decrypt it back to the original. To encrypt a larger message needs an arithmetic vehicle larger that that available in VBA, you'll see I'm declaring variables as Currency to get as many bits as possible but even this has limits when raising a number to a very high power exponentional.

Anyway, simple version that allows VBA programmer to step through the wikipedia example is given here ...

Option Explicit
Option Private Module

Private Type udtPublicKey
    n As Currency
    e As Currency
End Type

Private Type udtPrivateKey
    n As Currency
    d As Currency
End Type

'***************************************************
'               .__
'  _____ _____  |__| ____
' /     \\__  \ |  |/    \
'|  Y Y  \/ __ \|  |   |  \
'|__|_|  (____  /__|___|  /
'      \/     \/        \/
'***************************************************

Private Sub Main()

    Dim p As Currency
    Dim q As Currency
    Dim n As Currency
    Dim lambda_n As Currency
    Dim e As Currency
    Dim d As Currency


    p = 61
    q = 53
    n = p * q
    lambda_n = Application.Lcm(p - 1, q - 1)
    e = 17
    Debug.Assert IsCoPrime(e, lambda_n)
    
    d = ModularMultiplicativeInverse(e, lambda_n)
    Debug.Assert e <> d

    Dim uPrivate As udtPrivateKey
    uPrivate.d = d
    uPrivate.n = n
    
    Dim uPublic As udtPublicKey
    uPublic.e = e
    uPublic.n = n
        
    '* m is the message to encrypt, it needs to be a number
    '* 65 is ASCII for "A"
    Dim m As Currency
    m = 65
    
    '* c is the encrypted message
    Dim c As Currency
    c = Encrypt(m, uPublic)
    
    '* m2 is the decrypted message
    Dim m2 As Currency
    m2 = Decrypt(c, uPrivate)
    
    '* and the decrypted message should match the original
    Debug.Assert m2 = m
     
End Sub


Private Function Encrypt(ByVal m As Currency, _
                    ByRef uPublic As udtPublicKey) As Currency
    If m > uPublic.n Then Err.Raise vbObjectError, , _
            "#text is bigger than modulus, no way to decipher!"
    
    Dim lLoop As Long
    Dim lResult As Currency
    lResult = 1
    For lLoop = 1 To uPublic.e
    
        lResult = ((lResult Mod uPublic.n) * (m Mod uPublic.n)) Mod uPublic.n
    Next lLoop
    Encrypt = lResult
End Function

Private Function Decrypt(ByVal c As Currency, _
                    ByRef uPrivate As udtPrivateKey) As Currency
    If c > uPrivate.n Then Err.Raise vbObjectError, , _
            "#text is bigger than modulus, no way to decipher!"
    Dim lLoop As Long
    Dim lResult As Currency
    lResult = 1
    For lLoop = 1 To uPrivate.d
        lResult = ((lResult Mod uPrivate.n) * (c Mod uPrivate.n)) Mod uPrivate.n
    Next lLoop

    
    Decrypt = lResult
End Function

Private Function IsCoPrime(ByVal a As Currency, ByVal b As Currency) As Boolean
    IsCoPrime = (Application.Gcd(a, b) = 1)
End Function

Private Function ModularMultiplicativeInverse(ByVal e As Currency, _
                    ByVal lambda_n As Currency)
    Dim lLoop As Currency
    For lLoop = 1 To lambda_n
        If lLoop <> e Then
            Dim lComp As Currency
            lComp = lLoop * e Mod lambda_n
            If lComp = 1 Then
                ModularMultiplicativeInverse = lLoop
                Exit Function
            End If
        End If
    Next
SingleExit:
End Function

Cryptography - Glossary

Cryptography is a massive subject and there is tons of stuff on the Internet. However, I'd love a little notebook of terms. So here I will begin my own personal list. I'll use HTML anchors to each entry for extras URL granularity.

AD CS abbr. Active Directory Certificate Services, provides customizable services for creating and managing public key certificates used in software security systems employing public key technologies.

AES abbr. Advanced Encryption Standard, also referenced as Rijndael .

affine cipher n. a type of monoalphabetic substitution cipher. Each letter is enciphered with the function (ax + b) mod 26, where b is the magnitude of the shift.

alphabet n. a collection of symbols, also referred to as characters.

ANSI abbr. American National Standards Institute is a private non-profit organization that oversees the development of voluntary consensus standards for products, services, processes, systems, and personnel in the United States.

assymetric adj. describes cryptosystems where the encryption key differs from decryption key.

BF abbr. Blowfish, is a symmetric block cipher. (USA)

bijection n. In mathematics, a bijection, bijective function or one-to-one correspondence is a function between the elements of two sets, where each element of one set is paired with exactly one element of the other set, and each element of the other set is paired with exactly one element of the first set. There are no unpaired elements.

bit n. a character 0 or 1 of the binary alphabet.

block cipher n. a cipher which acts on the plaintext in blocks of symbols.

CA abbr. certification authority.

Camellia n. Camellia (cipher), is a symmetric key block cipher.

Cast n. CAST-128, is a symmetric key block cipher.

CBC abbr. cipher block chaining is a mode of operation for a block cipher.

certification authority n. a certificate authority or certification authority (CA) is an entity that issues digital certificates.

Caesar cipher n. a translation cipher for which b=3, used by Julius Caesar.

CFB abbr. Ciphertext Feedback is a mode of operation for a block cipher.

character n. an element of an alphabet.

cipher n. 1. a map from a space of plaintext to a space of ciphertext.
               2. a pair of algorithms, one for encryption and one for decryption.

ciphertext n. the disguised message.

CMVP abbr. cryptographic module validation program.

CMS abbr. cryptographic message syntax is the IETF's standard for cryptographically protected messages.

CNG abbr. Windows Cryptography API: Next Generation (CNG) replaces CryptoAPI 1.0 as the recommended Windows API cryptographic suite.

coprime adj. No common factors other than 1. Also called "relatively prime" or "mutually prime".

CRL abbr. certificate revocation list.

cryptanalysis n. the science (complementary to cryptography) concerned with the methods to defeat cryptographic techniques.

cryptographic hash function n. a special class of hash function that has certain properties which make it suitable for use in cryptography.

cryptographic nonce n. In cryptography, a nonce is an arbitrary number that may only be used once.

cryptography n. the study of mathematical techniques for all aspects of information security.

cryptology n. the study of cryptography and cryptanaylsis.

CSP abbr.Cryptographic Service Providers

CTR abbr. Counter-mode encryption, a block cipher mode of operation that uses incrementing IV counter for the key stream source.

cryptosystem n.

1. a suite of cryptographic algorithms needed to implement a particular security service, most commonly for achieving confidentiality (encryption). 2. (Mathematics) can formally be defined by a collection of sets, "plaintext space","ciphertext space","key space","set of encryption functions","set of decryption functions". See Cryptosystem.

decipher v.tr. to convert ciphertext into plaintext.

decode v.tr. to convert the encoded message back to its original alphabet and original form.

DRBG abbr. Deterministic Random Bit Generators

DES abbr. Data Encryption Standard is a symmetric-key block cipher published by the NIST.

digital certificate n. A digital certificate uses public-key cryptography to sign data and to verify the integrity of the certificate itself. Public key cryptography is a system based on pairs of keys called public key and private key.

DH abbr. Diffie Hellman.

DSA abbr. Digital Signature Algorithm.

ECB abbr. Electronic Code Book is a mode of operation for a block cipher.

ECC abbr. elliptic curve cryptography.

ECDH abbr. elliptic curve Diffie–Hellman , an ECC algorithm endorsed by NIST for key exchange.

ECDSA abbr. Elliptic Curve Digital Signature Algorithm , an ECC algorithm endorsed by NIST for digital signature.

EDE abbr. encrypt decrypt encrypt.

EFS abbr. Encrypting File System on Microsoft Windows is a feature introduced in version 3.0 of NTFS[1] that provides filesystem-level encryption.

elliptic curve cryptography n. is an approach to public-key cryptography based on the algebraic structure of elliptic curves over finite fields. ECC requires smaller keys compared to non-ECC cryptography (based on plain Galois fields) to provide equivalent security.

encipher v.tr. to convert plaintext into ciphertext.

encode v.tr. to convert a message into a representation in a standard alphabet, such as to the alphabet {A, . . . , Z} or to numerical alphabet.

encryption n. the process of disguising a message so as to hide the information it contains; this process can include both encoding and enciphering .

exhaustive key search n. Exhaustive key search, or brute-force search, is the basic technique of trying every possible key in turn until the correct key is identified.

FIPS abbr. Federal Information Processing Standard.

FIPS 140-2 Publication 140-2, (FIPS PUB 140-2),[1][2] is a U.S. government computer security standard used to approve cryptographic modules. The title is Security Requirements for Cryptographic Modules.

GCM abbr. Galois Counter Mode is an efficient and performant mode of operation for symmetric key cryptographic block ciphers.

GMAC abbr. Galois Message Authentication Code, see GCM.

GCD n. greatest common divisor.

hash function n. any function that can be used to map data of arbitrary size to data of fixed size.

HMAC n. the Keyed-Hash Message Authentication Code (HMAC)

IDEA n. International Data Encryption Algorithm is a symmetric-key block cipher

IETF abbr. Internet Engineering Task Force.

injection n. (Mathematics) a one-to-one mapping.

injective adj. (Mathematics) of the nature of or relating to an injection or one-to-one mapping. See Injective function.

IPSec abbr. Internet Protocol security (IPSec) is a framework of open standards for helping to ensure private, secure communications over Internet Protocol (IP) networks through the use of cryptographic security services.

IV abbr. Initialization vector, also call "salt","nonce" (number used once), used to ensure uniqueness of the key stream.

KAT abbr. Known Answer Test.

Kerboros n. is a computer network authentication protocol that builds on symmetric key cryptography and optionally may use public-key cryptography during certain phases of authentication.

keystream n. a stream of random or pseudorandom characters that are combined with a plaintext message to produce an encrypted message (the ciphertext).

MAC abbr. Message Authentication Code sometimes known as a tag, is a short piece of information used to authenticate a message—in other words, to confirm that the message came from the stated sender (its authenticity) and has not been changed.

message digest n. a cryptographic hash function containing a string of digits created by a one-way hashing formula.

MD5 abbr. a message digest. MD5 is considered (by some) essentially "cryptographically broken and unsuitable for further use".

MIC abbr. Message Integrity Code, substitute for MAC and sometimes message digest.

NIST abbr. National Institute of Standards and Technology (USA)

NSA abbr. National Security Agency. (USA)

OAEP abbr. Optimal asymmetric encryption padding is a padding scheme often used together with RSA encryption.

OCSP abbr. Online Certificate Status Protocol

OFB abbr. output feedback is a mode of operation for a block cipher.

OpenSSL OpenSSL is a general purpose cryptography library that provides an open source implementation of the Secure Sockets Layer (SSL) and Transport Layer Security (TLS) protocols.

Passphrase n. A passphrase is a sequence of words or other text used to control access to a computer system, program or data. A passphrase is similar to a password in usage, but is generally longer for added security.

PEM abbr. Privacy-enhanced Electronic Mail. PEM is a de facto file format for storing and sending cryptography keys, certificates, and other data, based on a set of 1993 IETF standards defining "privacy-enhanced mail.".

permute v. submit to a process of alteration, rearrangement, or permutation.

PKCS abbr. Public-Key Cryptography Standards

PKI abbr. Public Key Infrastructure (PKI) is a set of roles, policies, and procedures needed to create, manage, distribute, use, store, and revoke digital certificates and manage public-key encryption.

plaintext n. the message to be transmitted or stored.

post-quantum cryptography n. refers to cryptographic algorithms (usually public-key algorithms) that are thought to be secure against an attack by a quantum computer.

protocol n. an algorithm, defined by a sequence of steps, precisely specifying the actions of multiple parties in order to achieve an objective.

public key n. A public key is created in public key encryption cryptography that uses asymmetric-key encryption algorithms. Public keys are used to convert a message into an unreadable format. Decryption is carried out using a different, but matching, private key. Public and private keys are paired to enable secure communication.

RC2 n. RC2 (from Ron's Code) is a symmetric-key block cipher.

Rijndael n. Portmanteau of Belgian cryptographers Vincent Rijmen and Joan Daemen and a synonym for AES.

RIPEMD abbr. RACE Integrity Primitives Evaluation Message Digest.

RSA abbr. RSA is one of the first practical public-key cryptosystems and is widely used for secure data transmission. RSA is made of the initial letters of the surnames of Ron Rivest, Adi Shamir, and Leonard Adleman.

Salt n. In cryptography, a salt is random data that is used as an additional input to a one-way function that "hashes" a password or passphrase. Salts are closely related to the concept of nonce. The primary function of salts is to defend against dictionary attacks or against its hashed equivalent, a pre-computed rainbow table attack.

SEED abbr. SEED is a block cipher developed by South Korea.

SHA abbr. secure hash algorithm (SHA) .

S/MIME abbr. Secure/Multipurpose Internet Mail Extensions (S/MIME).

SSL abbr. SSL (Secure Sockets Layer) is the standard security technology for establishing an encrypted link between a web server and a browser. This link ensures that all data passed between the web server and browsers remain private and integral.

symmetric adj. describes cryptosystems where the encryption key is the same as the decryption key.

stream cipher n. a cipher which acts on the plaintext one symbol at a time.

string n. a finite sequence of characters in some alphabet.

substitution cipher n. a stream cipher which acts on the plaintext by making a substitution of the characters with elements of a new alphabet or by a permutation of the characters in the plaintext alphabet.

suite B n. is a set of cryptographic algorithms promulgated by the National Security Agency as part of its Cryptographic Modernization Program.

TLS abbr. TLS (Transport Layer Security) is a successor to Secure Sockets Layer protocol, or SSL. TLS provides secure communications on the Internet for such things as e-mail, Internet faxing, and other data transfers. There are slight differences between SSL 3.0 and TLS 1.0, but the protocol remains substantially the same.

TPM abbr. Trusted Platform Module is an international standard for a secure cryptoprocessor.

translation cipher n. an affine cipher for which a=1.

transposition cipher n. a block cipher which acts on the plaintext by permuting the positions of the characters in the plaintext.

Triple DES n. a symmetric-key block cipher, which applies the Data Encryption Standard (DES) cipher algorithm three times to each data block.

TRNG abbr. True Random Number Generator, based on a pure source of entropy ("noise").

X.509 abbr. X.509 is a standard that defines the format of public key certificates.

Links

Introduction to Cryptography

Handbook of Applied Cryptography (HAC)

Maths is fun - injective-surjective-bijective

RSA Laboritories : WHAT IS EXHAUSTIVE KEY SEARCH?

Data Encryption Standard

OpenSSL Cookbook Ivan Ristić Free Chapters

NIST Digital Signatures

Basic Blockchain Programming - keys as property

CodeGuru: Windows Cryptography API: Next Generation (CNG)

Windows Dev Center: Cryptography

Windows Dev Center: Cryptography API: Next Generation

MSDN: .NET Framework: System.Security.Cryptography

TechTarget SearchSecurity

How to choose an AES encryption mode (CBC ECB CTR OCB CFB)?

crackstation.net

Sharepoint Excel Services for server-side Excel calculations

For many years Excel was limited to client-side, i.e. desktop installation, operation. However, I did work on many projects where we launched an instance of Excel on a server as a calculation agent despite Microsoft recommending against because this option is fraught with unforeseen consequences. For example, message boxes get thrown but on a server there is no user to see and dismiss them. Microsoft invented Sharepoint for clients who really want to run Excel on the server.

The downsides of Sharepoint are cost, it is not cheap in itself and also it requires a Windows Server licence. If your employer is pro-Unix then this will be a deal-breaker. The other downside of Sharepoint is that your VBA code will not run and will have to be converted to C# or some other managed .NET language.

Nevertheless, the Sharepoint 'market share' of the Excel solution space will grow and a blog on Excel Development should address it.

I've yet to reach recommendations for this technology so this first blog post will solely be a collection of links for more reading.

Wikipedia is always good place to start, here is article on Excel Services

Office Support: Getting Started with Excel Services and Excel Web Access

Book: Wrox - Professional Excel Services

Technet: Overview of Excel Services in SharePoint Server 2013

Technet: Administer Excel Services in SharePoint Server 2013

safaribooksonline: Chapter 1. An Introduction to Excel Services

Technet Forums: VBA won't work for workbooks rendered in a browser from a Sharepoint server (Excel Services)

Office Dev Center: Understanding Excel Services UDFs

Office Dev Center: Walkthrough: Developing a Managed-Code UDF

MSDN: Creating Custom Solutions with Excel Services

Some sample C# code for a server-side Excel Services UDF.

// From https://dev.office.com/sharepoint/docs/general-development/step-2-creating-a-managed-code-udf
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Excel.Server.Udf;

namespace SampleUdf
{
    [UdfClass]
    public class Class1
    {
        [UdfMethod]
        public double MyDouble(double d)
        {
            return d * 9;
        }  

        [UdfMethod(IsVolatile = true)]
        public DateTime ReturnDateTimeToday()
        {
            return (DateTime.Today);
        }
    }
}

However, if you thought getting a UDF to run on both the client and the server in a unified code base would be a breeze then check out this web page which shows how to do it. It seems a major undertaking requiring C#, C++/CLI (formerly Managed C++), creating code to handle a managed Add-in and a custom shim. I expect many to be dissuaded from using SharePoint because of the complexity there but I also expect Microsoft to unify the client side and server interfaces in the future.

Whilst I have yet to reach recommendations on Sharepoint and Excel Services one has to wonder why rendering a workbook in a browser which requires a Windows Server, Sharepoint Server and Internet Information Server is better than keeping a workbook of a network file server (running on Linux) and opening in Excel. I guess the question boils down to client licences versus server licences.

Digital Signatures - a succinct maths formula/symbolic description

I cam across this beautifully succinct description of how digital signatures work written in maths formula/symbolic terms. Original thread is here.

1. Alice publishes her public key PK and keeps her private key K safe.
2. Alice produces an original bitstring S.
3. Alice computes S'=f(S, K), which is a fixed-length bitstring.
4. Alice publishes S+S'.
5. If Carol wants to know if Alice is in fact the author of S, all she needs to do is compute g(S, S', PK), which returns true if S is the bitstring that was signed with S' and if it was K that was used to produce the signature. Assuming that only Alice knows K, this is enough to prove Alice's authorship of S.
6. If Bob wants to impersonate Alice, stealing S' is useless because S' can only be used to authenticate S. Stealing PK is also useless because it can only be used to authenticate, not to sign. Altering S or S' doesn't work because it will cause the authentication to fail. Bob's only option is to steal or attempt to crack K.

If one were to licence software how would one do it?

So, typically we write code for an employer who pays us a salary. Sometimes as a VBA programmer one considers how to get paid for software that we write on an independent software vendor (ISV) basis. The tragic news is that VBA code is easy to break into. I should imagine that is the reason why there is very little information within the VBA 'blogosphere' as to how to protect software. It is inevitable that as VBA programmers we must learn another language, one that compiles preferably to machine code and second preference to virtual machine code. But even after learning such a language what would the code protection mechanism look like?

In short, I guess that one writes code that is callable but that there is an initialisation routine at which point one checks a licence file and if licence not valid then refuse to operate so the callable parts refuse to operate. Refusal to operate should be easy enough to implement. But how to check validity of a licence file?

Using our beloved Excel as an example, a quick Google turns up the following article from accountingweb.co.uk Microsoft Excel Licensing - what you can and can't do.... It shows the limits of install backdrops in that it limits virtual PC installs as well as forbidding Server installs. However, for the purposes of this article is it essentially a per device licence. if in doubt consult his article, Microsoft Office 2013 license limits ONE install per computer. So how is a per device enforced? It must read details of the host device and encrypt them somehow and prevent tampering

I sort of knew this already, i.e. that one need to tie a licence to a piece of hardware but which piece of hardware becomes the question.

Should I use Network Interface Card (NIC) as a licence key point?

I had heard that tieing to a NIC is one way to do this, so each NIC has a unique serial number etc. etc. etc. and if you want to enumerate your network interfaces then this Superuser article would be of use Where can I find my NIC’s GUID? or perhaps this. But questions have to be asked about whether a NIC is the right basis for a 'hardware fingerprint', after all NICs do fail and then they get swapped out, should a user have to buy another licence because their network component failed?

Unique Hardware ID / Hardware Fingerprint

If not NICs then what, NICs in a combination of other devices? This question has been tossed around on stackoverflow.com at Reliable way of generating unique hardware ID and here. One Q&A even discusses interrogation code of a hardware fingerprint. CodeProject.com gives us some sample code as to how a licencing scheme might work here, here, here and here. A C# implementation is given here.

I would have though am motherboard id is better than a NIC id. If a NIC fails then a computer room operator swaps out the NIC but if a motherboard fails then they'd need to rebuild the whole computer, IMHO.

Parse JSON in Excel VBA

Advert: Come to the Foo Bar! See a whole working application based on Excel VBA, JSON, JavaScript, Angular with help of a beauty of C# COM Addin. The Foo Bar where the beers are easily replenished with Angular.

So I did quite a lot of work on StackOverflow answering my own questions about JSON parsing. I really ought to deposit some key code here on my blog as well.

Whilst I have praised Tim Hall's excellent JSON parsing code the following leverages Microsoft's own parser. Tim Hall's is better when you want to amend the object, i.e. read-write. If you are happy with read-only then the Microsoft parser could be a better option but there is one caveat in that Microsoft's parser will execute any Javascript embedded in the JSON and this is a security breach. But if you know the provenance of the JSON and know it to be free from Javascript then the following code will be of use to you.

Use ScriptControl.Eval to parse a JSON object in Excel VBA

In following code, I have given the canonical example from http://json.org/example.html. Paste this code into VBA, add Tools->References->Microsoft Script Control 1.0

Function JsonLiteral() As String
    Const sJSON As String = "{" _
        & " ""glossary"": {" _
        & " ""title"": ""example glossary""," _
        & " ""GlossDiv"": {" _
        & " ""title"": ""S""," _
        & " ""GlossList"": {" _
        & " ""GlossEntry"": {" _
        & " ""ID"": ""SGML""," _
        & " ""SortAs"": ""SGML""," _
        & " ""GlossTerm"": ""Standard Generalized Markup Language""," _
        & " ""Acronym"": ""SGML""," _
        & " ""Abbrev"": ""ISO 8879:1986""," _
        & " ""GlossDef"": {" _
        & " ""para"": ""A meta-markup language, used to create markup languages such as DocBook.""," _
        & " ""GlossSeeAlso"": [""GML"", ""XML""]" _
        & " }," _
        & " ""GlossSee"": ""markup""" _
        & " } } } } }"

    JsonLiteral = sJSON

    '* Requires Tools->References to Microsoft Script Control 1.0
    Dim oSC As ScriptControl
    Set oSC = New ScriptControl
    
    oSC.Language = "JScript"
    Dim obj As Object
    Set obj = oSC.Eval("(" + sJSON + ")")
    
    Stop
    'Look at the Locals window to see the structure of the JSON
End Function

You'll see once evaluated then the Locals object allows you to navigate the structure.

Use VBA.CallByName and hasOwnProperty to navigate parse structure

Some more examples show how you can use CallByName to get sub-objects and also elements of an array. Also shown here is hasOwnProperty which can be used for defensive programming.

Private Sub CallByNameAndHasOwnProperty()

    Dim oScriptEngine As ScriptControl
    Set oScriptEngine = New ScriptControl
    oScriptEngine.Language = "JScript"

    Dim sJsonString(0 To 1) As String
    sJsonString(0) = "{'key1': 'value1'  ,'key2': { 'key3': 'value3' } }"
    sJsonString(1) = "[ 1234, 2345, 3456, 4567, 5678, 6789 ]"



    Dim objJSON(0 To 1) As Object
    Set objJSON(0) = oScriptEngine.Eval("(" + sJsonString(0) + ")")
    Set objJSON(1) = oScriptEngine.Eval("(" + sJsonString(1) + ")")

    Debug.Assert objJSON(0).hasOwnProperty("key1")
    Debug.Assert objJSON(0).hasOwnProperty("key2")

    Debug.Assert CallByName(objJSON(1), "length", VbGet) = 6
    Debug.Assert CallByName(objJSON(1), "0", VbGet) = "1234"
End Sub

Caveat- Don't Overuse ScriptControl!

In addition to the warning about buried JavaScript in JSON I have another warning regarding Microsoft ScriptControl. Although it may look like a brilliant keyhole interface into the world of Javascript execution in VBA it is stuck in a time warp. I have established that Script Control is limited to Javascript EcmaScript v.3. Whilst current is version 6. Don't think you can code up some Javascript in say Microsoft Visual Studio Code and then paste into the ScriptControl with AddCode because chances are you will hit an incompatibility. Also with the ScriptControl you cannot debug code.

I experimented with ScriptControl for some time and tried to push its boundaries, initially it was exciting but it can be frustrating. Search this blog for JSON posts for any updates.

UPDATES and FURTHER READING

Update: Safer JSON parsing with Json.Parse (still via ScriptControl)