You are not logged in.
Pages: 1
We are investigating the possibilities of accessing data in our server application from within Excel, especially using PowerQuery.
We did manage to use normal authentication scheme from Mormot by writing VBA functions to handle the comminication and the calculation the session_signature parameter. Sadly, there is no way from PowerQuery to call VBA functions. Probably has to do with the possibility to store theses sheets on the cloud, where powerQueries can still be executed, but VBA methods cannot.
Main problem is the fact that (with the same REST command) the actual URI will be different every time because it must contain the (ever increasing) timestamp.
A widely spread alternative way to access data via REST is to use an ApiKey as a kind of special password. We want to implement this using a special call to ask the server for a ApiKey for a specific user (stored in the TSQLAuthUser table), which will timeout when not used for some time. As long as the timeout has not expired, the ApiKey can be used as a parameter in the URI and provide authentication.
http://localhost:8080/API/GetApiKey?User=<username>&password=<sha256encodedpassword>
This call will return a valid ApiKey (when the credentials check out of course). With this Apikey all other available REST command should now be available (without any additional registration of published methods or interfaces) by calling them like this:
http://localhost:8080/API/SampleRecord?ApiKey=<ApiKey>
http://localhost:8080/API/SampleRecord/1?ApiKey=<ApiKey>
http://localhost:8080/API/Sum?ApiKey=<ApiKey>&a=23&b=12
What would be the best/easiest/most robust way to implement this?
Note: To provide extra security use of https would be advised of course, but that is irrelevant to the concept
Last edited by Bascy (2014-01-17 09:52:47)
Offline
This is very close to the native URI-based-signature authentication scheme already implemented in mORMot.
But I do not like this pattern, which is not secure: it is very sensitive to MIM (man in the middle) or Replay attacks.
You can define your own authentication class if you want to implement this scheme.
The SAD 1.18 pdf and the source code is available.
Offline
We did manage to use normal authentication scheme from Mormot by writing VBA functions to handle the comminication and the calculation the session_signature parameter.
I am just struggling to figure out how to connect mORMot and Excel through VBA to pull the content
into a Excel sheet. My biggest problem is I can pull the JSON content into a Excel sheet but I
cannot update cells on the fly (I need to close Excel and connect to service again to update).
I found the VBJSON library which has helped me a bit. Can you share some VBA
that handle to mORMot authentication?
Offline
Can you share some VBA that handle to mORMot authentication?
Unfortunately, I'm no VBA expert (never used it on production) and I do not know any mORMot user which did access via a VBA client.
It should be feasible, of course, especially with weak authentication.
Could not the VBA execute a Delphi dll to access the remote data?
See http://msdn.microsoft.com/en-us/library/office/bb687915
Offline
I have managed to create a VBA method that authenticates to a REST server, it stores the resulting PrivateHash, SessionID etc in global vars:
Option Explicit
Dim gSessionIDasHex As String
Dim gPrivateSaltHash As Long
Dim gPreviousTix As Long
'Format number as a 4 Byte hex
Private Function To4ByteHex(aNr As Long) As String
Dim AsHex As String
AsHex = Hex(aNr)
To4ByteHex = Right("00000000" + AsHex, 8)
End Function
Private Function Authenticate(ByVal aServer, aModel As String, ByVal aUsername, aPassword As String) As Boolean
Dim MyRequest As WinHttpRequest
Dim Response As String
Dim ServerNonce As String
Dim PasswordHashHexa As String
Dim SessionPassWordSha256 As String
Dim ClientNonce As String
Dim s, i As Integer
Set MyRequest = New WinHttpRequest
gSessionID = 0
gSessionIDasHex = ""
gPrivateSaltHash = 0
'First request ServerNonce for username
MyRequest.Open "GET", aServer + "/" + aModel + "/Auth?username=" + aUsername
MyRequest.Send
If MyRequest.Status <> 200 Then
Debug.Print MyRequest.ResponseText
Authenticate = False
Else
Response = MyRequest.ResponseText
'Authenticate and create session
ServerNonce = Mid(Response, InStr(Response, ":") + 2, 64)
ClientNonce = SHA256("TestingCLient")
PasswordHashHexa = SHA256("salt" + aPassword)
SessionPassWordSha256 = SHA256(aModel + ServerNonce + ClientNonce + aUsername + PasswordHashHexa)
MyRequest.Open "GET", aServer + "/" + aModel + "/auth?userName=" + aUsername + "&PassWord=" + SessionPassWordSha256 + "&ClientNonce=" + ClientNonce
MyRequest.Send
If MyRequest.Status <> 200 Then
Debug.Print MyRequest.ResponseText
Authenticate = False
Else
s = InStr(MyRequest.ResponseText, ":")
i = InStr(MyRequest.ResponseText, ",")
'gPrivateSalt is "<SessionID>+<PrivateKey>"
gPrivateSalt = Mid(MyRequest.ResponseText, s + 2, i - s - 3)
i = InStr(gPrivateSalt, "+")
gSessionID = Val(Left(gPrivateSalt, i - 1))
gSessionIDasHex = To4ByteHex(gSessionID)
gPrivateSaltHash = AddCrc32(PasswordHashHexa, AddCrc32(gPrivateSalt))
Authenticate = True
End If
End If
End Function
After authenticating, yu can use the folliwng sub to append the needed signature to the url
'Construct the signature to add to the url,
Public Function SignUrl(Url As String) As String
SignUrl = SignUrlEx(Url, gSessionIDasHex, gPrivateSaltHash)
End Function
Public Function SignUrlEx(ByVal Url As String, ByVal SessionIDAsHex As String, ByVal PrivateSaltHash As Long) As String
Dim Sig As String
Dim Tix As Long
Dim Timestamp As String
Sig = "session_signature=" + SessionIDAsHex
Tix = Timer * 100
'//Must be larger then prvious request
If Tix <= gPreviousTix Then
Tix = gPreviousTix + 1
End If
gPreviousTix = Tix
Timestamp = To4ByteHex(Tix)
Sig = Sig + Timestamp
Sig = Sig + To4ByteHex(GetCrc32(AddCrc32(Url, AddCrc32(Timestamp, PrivateSaltHash))))
If InStr(Url, "?") > 0 Then
SignUrlEx = Url + "&" + Sig
Else
SignUrlEx = Url + "?" + Sig
End If
End Function
So you can test this with the following sub:
Public Sub TestUrl()
Dim MyRequest As WinHttpRequest
Dim Response As String
Dim Server As String
Dim Model As String
Dim Url As String
Set MyRequest = New WinHttpRequest
Server = "http://localhost:8080"
Model = "root"
If Authenticate(Server, Model, "User", "synopse") Then
Url = Model + "/SampleRecord"
MyRequest.Open "Get", Server + "/" + SignUrl(Url)
MyRequest.Send
Debug.Print MyRequest.ResponseText
Url = Model + "/SampleRecord/2"
MyRequest.Open "Get", Server + "/" + SignUrl(Url)
MyRequest.Send
Debug.Print MyRequest.ResponseText
End If
End Sub
If you need them I can give you the CRC32 and Sha256 methods too, just let me know.
Last edited by Bascy (2014-01-22 08:05:47)
Offline
Authentication is working fine in Excel 2003, but it fails on URISignature.
http://localhost:8080/root/auth?userName=User&PassWord=ee28af002f8437aac81c54e94538da108c97259664529e85254aa3786c3cf433&ClientNonce=355ba2d3734e098b7097528a2d9176481f8c6178365266342b385f8c6fe0d87d
{"result":"76578+18debb62e13f34a7092833bb7295168da820146fca75a6af76cc9177104c787d","logonname":"User"}
... As you can see, I have an issue with URISignature, this is probably my CRC32.
http://localhost:8080/root/SampleRecord?session_signature=0000004F0063168F
{"ErrorCode":403, "ErrorText":"Forbidden"}
?? gPrivateSaltHash = AddCrc32(PasswordHashHexa, AddCrc32(gPrivateSalt))
... I was looking for the CRC32 in VBA to match mORMot scheme but i couldn't find any usefull thing yet. I just implement something similar
Private malTable(256) As Long
Private Function AddCrc32(ByVal sItem As String, ByVal lCRC32 As Long) As Long
Dim yCharValue As Byte
Dim lCounter As Long
Dim lIndex As Long
Dim lAccValue As Long
Dim lTableValue As Long
malTable(0) = &H0
malTable(1) = &H77073096
malTable(2) = &HEE0E612C
malTable(3) = &H990951BA
malTable(4) = &H76DC419
malTable(5) = &H706AF48F
malTable(6) = &HE963A535
malTable(7) = &H9E6495A3
malTable(8) = &HEDB8832
malTable(9) = &H79DCB8A4
malTable(10) = &HE0D5E91E
malTable(11) = &H97D2D988
malTable(12) = &H9B64C2B
malTable(13) = &H7EB17CBD
malTable(14) = &HE7B82D07
malTable(15) = &H90BF1D91
malTable(16) = &H1DB71064
malTable(17) = &H6AB020F2
malTable(18) = &HF3B97148
malTable(19) = &H84BE41DE
malTable(20) = &H1ADAD47D
malTable(21) = &H6DDDE4EB
malTable(22) = &HF4D4B551
malTable(23) = &H83D385C7
malTable(24) = &H136C9856
malTable(25) = &H646BA8C0
malTable(26) = &HFD62F97A
malTable(27) = &H8A65C9EC
malTable(28) = &H14015C4F
malTable(29) = &H63066CD9
malTable(30) = &HFA0F3D63
malTable(31) = &H8D080DF5
malTable(32) = &H3B6E20C8
malTable(33) = &H4C69105E
malTable(34) = &HD56041E4
malTable(35) = &HA2677172
malTable(36) = &H3C03E4D1
malTable(37) = &H4B04D447
malTable(38) = &HD20D85FD
malTable(39) = &HA50AB56B
malTable(40) = &H35B5A8FA
malTable(41) = &H42B2986C
malTable(42) = &HDBBBC9D6
malTable(43) = &HACBCF940
malTable(44) = &H32D86CE3
malTable(45) = &H45DF5C75
malTable(46) = &HDCD60DCF
malTable(47) = &HABD13D59
malTable(48) = &H26D930AC
malTable(49) = &H51DE003A
malTable(50) = &HC8D75180
malTable(51) = &HBFD06116
malTable(52) = &H21B4F4B5
malTable(53) = &H56B3C423
malTable(54) = &HCFBA9599
malTable(55) = &HB8BDA50F
malTable(56) = &H2802B89E
malTable(57) = &H5F058808
malTable(58) = &HC60CD9B2
malTable(59) = &HB10BE924
malTable(60) = &H2F6F7C87
malTable(61) = &H58684C11
malTable(62) = &HC1611DAB
malTable(63) = &HB6662D3D
malTable(64) = &H76DC4190
malTable(65) = &H1DB7106
malTable(66) = &H98D220BC
malTable(67) = &HEFD5102A
malTable(68) = &H71B18589
malTable(69) = &H6B6B51F
malTable(70) = &H9FBFE4A5
malTable(71) = &HE8B8D433
malTable(72) = &H7807C9A2
malTable(73) = &HF00F934
malTable(74) = &H9609A88E
malTable(75) = &HE10E9818
malTable(76) = &H7F6A0DBB
malTable(77) = &H86D3D2D
malTable(78) = &H91646C97
malTable(79) = &HE6635C01
malTable(80) = &H6B6B51F4
malTable(81) = &H1C6C6162
malTable(82) = &H856530D8
malTable(83) = &HF262004E
malTable(84) = &H6C0695ED
malTable(85) = &H1B01A57B
malTable(86) = &H8208F4C1
malTable(87) = &HF50FC457
malTable(88) = &H65B0D9C6
malTable(89) = &H12B7E950
malTable(90) = &H8BBEB8EA
malTable(91) = &HFCB9887C
malTable(92) = &H62DD1DDF
malTable(93) = &H15DA2D49
malTable(94) = &H8CD37CF3
malTable(95) = &HFBD44C65
malTable(96) = &H4DB26158
malTable(97) = &H3AB551CE
malTable(98) = &HA3BC0074
malTable(99) = &HD4BB30E2
malTable(100) = &H4ADFA541
malTable(101) = &H3DD895D7
malTable(102) = &HA4D1C46D
malTable(103) = &HD3D6F4FB
malTable(104) = &H4369E96A
malTable(105) = &H346ED9FC
malTable(106) = &HAD678846
malTable(107) = &HDA60B8D0
malTable(108) = &H44042D73
malTable(109) = &H33031DE5
malTable(110) = &HAA0A4C5F
malTable(111) = &HDD0D7CC9
malTable(112) = &H5005713C
malTable(113) = &H270241AA
malTable(114) = &HBE0B1010
malTable(115) = &HC90C2086
malTable(116) = &H5768B525
malTable(117) = &H206F85B3
malTable(118) = &HB966D409
malTable(119) = &HCE61E49F
malTable(120) = &H5EDEF90E
malTable(121) = &H29D9C998
malTable(122) = &HB0D09822
malTable(123) = &HC7D7A8B4
malTable(124) = &H59B33D17
malTable(125) = &H2EB40D81
malTable(126) = &HB7BD5C3B
malTable(127) = &HC0BA6CAD
malTable(128) = &HEDB88320
malTable(129) = &H9ABFB3B6
malTable(130) = &H3B6E20C
malTable(131) = &H74B1D29A
malTable(132) = &HEAD54739
malTable(133) = &H9DD277AF
malTable(134) = &H4DB2615
malTable(135) = &H73DC1683
malTable(136) = &HE3630B12
malTable(137) = &H94643B84
malTable(138) = &HD6D6A3E
malTable(139) = &H7A6A5AA8
malTable(140) = &HE40ECF0B
malTable(141) = &H9309FF9D
malTable(142) = &HA00AE27
malTable(143) = &H7D079EB1
malTable(144) = &HF00F9344
malTable(145) = &H8708A3D2
malTable(146) = &H1E01F268
malTable(147) = &H6906C2FE
malTable(148) = &HF762575D
malTable(149) = &H806567CB
malTable(150) = &H196C3671
malTable(151) = &H6E6B06E7
malTable(152) = &HFED41B76
malTable(153) = &H89D32BE0
malTable(154) = &H10DA7A5A
malTable(155) = &H67DD4ACC
malTable(156) = &HF9B9DF6F
malTable(157) = &H8EBEEFF9
malTable(158) = &H17B7BE43
malTable(159) = &H60B08ED5
malTable(160) = &HD6D6A3E8
malTable(161) = &HA1D1937E
malTable(162) = &H38D8C2C4
malTable(163) = &H4FDFF252
malTable(164) = &HD1BB67F1
malTable(165) = &HA6BC5767
malTable(166) = &H3FB506DD
malTable(167) = &H48B2364B
malTable(168) = &HD80D2BDA
malTable(169) = &HAF0A1B4C
malTable(170) = &H36034AF6
malTable(171) = &H41047A60
malTable(172) = &HDF60EFC3
malTable(173) = &HA867DF55
malTable(174) = &H316E8EEF
malTable(175) = &H4669BE79
malTable(176) = &HCB61B38C
malTable(177) = &HBC66831A
malTable(178) = &H256FD2A0
malTable(179) = &H5268E236
malTable(180) = &HCC0C7795
malTable(181) = &HBB0B4703
malTable(182) = &H220216B9
malTable(183) = &H5505262F
malTable(184) = &HC5BA3BBE
malTable(185) = &HB2BD0B28
malTable(186) = &H2BB45A92
malTable(187) = &H5CB36A04
malTable(188) = &HC2D7FFA7
malTable(189) = &HB5D0CF31
malTable(190) = &H2CD99E8B
malTable(191) = &H5BDEAE1D
malTable(192) = &H9B64C2B0
malTable(193) = &HEC63F226
malTable(194) = &H756AA39C
malTable(195) = &H26D930A
malTable(196) = &H9C0906A9
malTable(197) = &HEB0E363F
malTable(198) = &H72076785
malTable(199) = &H5005713
malTable(200) = &H95BF4A82
malTable(201) = &HE2B87A14
malTable(202) = &H7BB12BAE
malTable(203) = &HCB61B38
malTable(204) = &H92D28E9B
malTable(205) = &HE5D5BE0D
malTable(206) = &H7CDCEFB7
malTable(207) = &HBDBDF21
malTable(208) = &H86D3D2D4
malTable(209) = &HF1D4E242
malTable(210) = &H68DDB3F8
malTable(211) = &H1FDA836E
malTable(212) = &H81BE16CD
malTable(213) = &HF6B9265B
malTable(214) = &H6FB077E1
malTable(215) = &H18B74777
malTable(216) = &H88085AE6
malTable(217) = &HFF0F6A70
malTable(218) = &H66063BCA
malTable(219) = &H11010B5C
malTable(220) = &H8F659EFF
malTable(221) = &HF862AE69
malTable(222) = &H616BFFD3
malTable(223) = &H166CCF45
malTable(224) = &HA00AE278
malTable(225) = &HD70DD2EE
malTable(226) = &H4E048354
malTable(227) = &H3903B3C2
malTable(228) = &HA7672661
malTable(229) = &HD06016F7
malTable(230) = &H4969474D
malTable(231) = &H3E6E77DB
malTable(232) = &HAED16A4A
malTable(233) = &HD9D65ADC
malTable(234) = &H40DF0B66
malTable(235) = &H37D83BF0
malTable(236) = &HA9BCAE53
malTable(237) = &HDEBB9EC5
malTable(238) = &H47B2CF7F
malTable(239) = &H30B5FFE9
malTable(240) = &HBDBDF21C
malTable(241) = &HCABAC28A
malTable(242) = &H53B39330
malTable(243) = &H24B4A3A6
malTable(244) = &HBAD03605
malTable(245) = &HCDD70693
malTable(246) = &H54DE5729
malTable(247) = &H23D967BF
malTable(248) = &HB3667A2E
malTable(249) = &HC4614AB8
malTable(250) = &H5D681B02
malTable(251) = &H2A6F2B94
malTable(252) = &HB40BBE37
malTable(253) = &HC30C8EA1
malTable(254) = &H5A05DF1B
malTable(255) = &H2D02EF8D
For lCounter = 1 To Len(sItem)
yCharValue = Asc(Mid$(sItem, lCounter, 1))
lAccValue = lCRC32 And &HFFFFFF00
lAccValue = lAccValue \ &H100
lAccValue = lAccValue And &HFFFFFF
lIndex = lCRC32 And &HFF
lIndex = lIndex Xor yCharValue
lTableValue = malTable(lIndex)
lCRC32 = lAccValue Xor lTableValue
Next lCounter
AddCrc32 = lCRC32
End Function
Public Function ComputeCRC32(ByRef sString As String) As String
Dim lCrc32Value As Long
lCrc32Value = InitCrcc32()
lCrc32Value = AddCrc32(sString, lCrc32Value)
ComputeCRC32 = Hex$(GetCrc32(lCrc32Value))
End Function
Private Function GetCrc32(ByVal lCRC32 As Long) As Long
GetCrc32 = lCRC32 Xor &HFFFFFFFF
End Function
Private Function InitCrc32(Optional ByVal Seed As Long = &HEDB88320, Optional ByVal Precondition As Long = &HFFFFFFFF) As Long
Dim iBytes As Integer
Dim iBits As Integer
Dim lCRC32 As Long
Dim lTempCrc32 As Long
For iBytes = 0 To 255
lCRC32 = iBytes
For iBits = 0 To 7
lTempCrc32 = lCRC32 And &HFFFFFFFE
lTempCrc32 = lTempCrc32 \ &H2
lTempCrc32 = lTempCrc32 And &H7FFFFFFF
If (lCRC32 And &H1) <> 0 Then
lCRC32 = lTempCrc32 Xor Seed
Else
lCRC32 = lTempCrc32
End If
Next iBits
malTable(iBytes) = lCRC32
Next iBytes
InitCrc32 = Precondition
End Function
Would you mind to share CRC32 module?
Offline
Alex, if you send me your email address, i'll mail the units for CRC32 and SHA256 to you
Offline
Suddenly, everything started to work properly. I realized that my CRC32 code is actually works.
Finally, this authentication and URISignature for VBA is working just fine with mORMot.
I think VBA's performance is very respectable in Excel 2003; It is partly compiled rather than purely interpreted as some people think.
Another idea is to use Add-in Express component for Delphi to develop COM-Addins for Excel. This must be easy to integrate mORMot. A COM Add-in will run considerably faster than VBA and will offer increased security in terms of people breaking into / reverse engineering your work in any way.
Thank's for sharing!
Offline
Pages: 1