Access FE SQL Exress BE securing

[Dan_T4]

Hi all,

I've been reading around the web and here but generally getting confused trying to work out what I should be doing. I want to make moves forward to limit the possibility of the traffic between my Access client and remote SQL server express R2 being compromised. These users make 4-5k record entries a day and I'm quite happy with the way I manage their access from the client but I want to know more about how exposed I am from the connection.

The setup is:
SQL Server Express R2 on a fully managed remote server.
ACCESS 2013 FE distributed to 45+ home workers.
DNS-Less connection (SQL Server Native Client 10.0)


My questions currently revolve around encrypting the connection. Should this be encrypted? If so:

1: How do I achieve this?
2: once set up do all connections have to be encrypted?
3: Other than the connection string are any other client side changes needed?

I'm sure the answer is out there but I've failed to find it or understand it.

Cheers,

Dan

 

[Rx_]

https://msdn.microsoft.com/en-us/library/ms131691.aspx
Chart with Native Cleint 11.0 and Encryption. My conversion from 10.0 to 11.0 was very straight-forward using DSN Less.

It probably would help us "Access Programmers" to set up a discussion on SQL Server Central. I have found many people on this forum are willing to help. For security issues, they are even willing to help off-line.
As demonstrated in this link, few of them admit to know much about MS Access.

http://www.sqlservercentral.com/Forums/Topic1532212-1526-1.aspx
Note: I use SQL Server Native Client on Citrix. All users on the WAN or in the field outside the WAN are clearly identified on my record fields.

That said, the new Azuer uses SQL Server Native Client in the MS Access Web products. I think you bring up a great point about the security. The term "sniffing" is something discussed with linked tables.

Another concern listed is allowing Ad Hoc Queries by end users.

Since 1997, I have been using Citrix to run Access applications with Citrix back end for many applications. Citrix solves all of your security questions. The ICA client is very thin. No process takes place on the client computer. Yet, sharing USB (keyboard, thunbdrive, mouse, ...) and local recources are made possible with permissions. The ICA Client is extremely encrypted. For example: Imagine your computer screen as 64 squares of display. Each square is encrypted with 128 Key encryption. As the display changes (mouse move) only the squares that change are re-submitted from the server to the client.
Since all processing takes place on a server, only the keystokes, mouse movement and video updates are transmitted. Many Citrix sessions can take place on 56K modems. This is why the ICA is called a Thin-Client.

Microsoft has the Office 365 option for MS Access. In my opinion, it just isn't there and the use of macro's instead of running VBA in its own sandbox is a limitation.
Microsoft also offers a Remote Terminal (like Citrix) option for MS Office (including Access). The downside is that it is 20 end-user license minimum.
However, anyone who understands the true Cost of Ownership for a cleint will find the price very competitive.
The MS Office (including Access) is provided by remote terminal. It includes licesing, virus check, software updates, security, backups, and I beleive even SQL Server. This came out for somewhere under $80 USD a month. But, it is a 20 minimum client license. Still, a new client version release goes to one single server template folder. All clients (users) are updated with the next login. All users run 100% of the application and linking back to SQL Server in the security of a server. This means that VBA is in the virtual machine's users sandbox.

Remote Terminal (based on Citrix technology) also runs on any client that supports the ICA client. Probably any PC with Windows 2000 or after. Mac, Unix and more.
For the end user, just the ICA client to install, no ODBC version, no patches and so on.

According to another comment on SQL Server Central, ADO (as opposed to DAO) is automatically encrypted. But, I can't personally validate that.

Hope you find and share some good information.