Access and LOTS of users

[RonPaii]

Probably no real options here. While networks and WANS have vast improved? I think remote desktop is the better choice. However today you can get away on a good solid network. We often have some users in a different city accidentally connecting their front ends through the VPN when they should be using remote desktop. The app runs ok, but back end is SQL server.

I think using SQL server is the best option, and in most cases the free edition will suffice. In other words I not done/used an access BE in such cases over such WANS.
R
Albert

I have tested FE/BE over a VPN, both ends on the same provider fiber FE home, BE Business. It was much slower and prone to corruption. VPN was added to the disallowed connection type the FE checks for at startup and monitors while open.

 

[RonPaii]

Have you tested without VPN?

No
SMB if extremely insecure, I don't think you would ever want to expose it to the Internet.

 

[The_Doc_Man]

Are there bots that crawl around hunting Access backends to steal?
What are the chances of being hacked without using a VPN connection?

In general, unlikely. SMB is a full-on TCP-family protocol in which every message has a sequence number within its header. The upgrades to SMB (v2, v3) did not change that factor. Which means the only way to steal a connection is to have been running a continuous packet-sniffer that knows the last valid sequence number and to send a message with the next sequence number. BUT... if that last message was a segmented transfer of data that DIDN'T continue a segmented sequence logically as well as per protocol, that's a dead database. Of course, by segmented I mean that the limit of the OSI model is that you send no more than about 1500 bytes per network packet, so when sending a 4KB file block, you have to send it in three segments.

 

[Albert D. Kallal]

Are there bots that crawl around hunting Access backends to steal?
What are the chances of being hacked without using a VPN connection?

For any public exposure - a VPN is a must have.
I remember many years ago - in fact it was 2004.

Just before going to the MVP summit in Redmond, I want to show my fellow Access developers how cool it is that you can have a Access front end connect to a database with any valid internet connection.

So, I forwarded the router ports on my home network to a desktop computer running SQL server.

And looking at the logs?
I don't think it was much more then 1/2 an hour, and I was already seeing attempts to logon to SQL server!

logs:
sa logon fails etc......

Now, that was 2004!!! - Today, with faster internet?
You have faster bots, and boatloads more of them!!!

Now, above was not exposing my computer network to the public internet, but only that of the SQL server ports being opened up to my at home IP address. As noted, I don't think more then 30 minutes had passed, and already attempts to logon to SQL server was occurring....

So, really?
No, you can't open up any ports or ANYTHING at all to the public internet - it's far too risky.....

And the current VPN I'm using to remote into clients company network?

It's two factor - so when I try to connect, I have to then launch the authentication application on my phone, and approve that connection.

In other words, near all VPN systems I used?
They require more then say just a IP address and password - such connections near always require two factor authentication nowadays....

So, yes there are bots looking for SQL or even open network ports and connections.

And under ZERO use cases can you thus risk opening anything or any kind of network access to the wild and crazy internet.....


R
Albert

 

[Albert D. Kallal]

How did you arrive at this conclusion? - I don't think it is correct.
OleDB and ODBC can be implemented in the very same DLL. So, both technologies using the same file is not an indication of this being the case.

I missed above -- sorry for not following up.

What I was stating is that oleDB can use a ODBC connection.

Microsoft OLE DB Provider for ODBC

Office developer client VBA reference documentation

learn.microsoft.com

from above:

The Microsoft ODBC Provider, however, allows ADO to connect to any ODBC data source.

So, for example, in Access when we use a oleDB provider, and I have a linked table to SQL server?
Well, the linked table ONLY HAS a ODBC connection specified right?

But, both of these code snips work - one is ADO, and the other is DAO, yet they BOTH WORK with the SAME connection string!!!!

Hence:

Sub QueryTest()

    Dim strSQL      As String
    Dim rstHotels   As DAO.Recordset
   
    strSQL = "select count(*) as MyHotelCount from dbo_tblHotels WHERE Description is not null"
   
    Set rstHotels = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    Debug.Print "hotels with descripiton = " & rstHotels!MyHotelCount
    rstHotels.Close
   
    Dim rstHotelsADO    As New ADODB.Recordset
    rstHotelsADO.Open strSQL, CurrentProject.Connection
    Debug.Print "ADO - hotels with description = " & rstHotelsADO!MyHotelCount
    rstHotelsADO.Close

End Sub

So, in above, where (and how) is ADO working, when we never provided a ADO connection string, but are ONLY using a ODBC one!

Well, we are in fact using the oleDB provider for ODBC - that's how!!

And that's how Access is allowing me to connect to the ODBC table (in this example SQL server), but at no point in time did I create, nor provide a valid ADO connection string, I'm ONLY using a valid ODBC one.....

So, if your point was about MS dropping ADO support? I stand corrected - it's still supported, my bad.

However, my point about ADO and say the oleDB provider actually winding up using a ODBC connection and driver?
Yup -- the above code shows this in action, and the link I provided further explains this.

R
Albert