Login Security for Hybrid DB with Back End on Office 365 (1 Viewer)

Techworks

Registered User.
Local time
Tomorrow, 07:42
Joined
Feb 2, 2014
Messages
12
Microsoft seems to be using mixing confusing terminology when it comes to their Office 365 and cloud products, with terms like sharepoint lists, azure databases, custom web apps, so for the purpose of this question I'll use the following terms:

"Access 2013 Custom Web App" for my SQL Server Azure database that I created through the Office 365 portal.

"Access 2013 Desktop App" for my Access 2013 .accdb desktop database

I have a Office 365 Small Business Premium account with 1 licensed user

I have logged into my O365 portal and navigated to "Sites-Team Site-Site content" and created a new Access 2013 Custom Web App (which is actually a SQL Server Azure db sitting up somewhere in the cloud...great). This will be my back end database.

I have created an Access 2013 Desktop App for my front end database and linked this to my back end database using a file dsn odbc connection.

Getting an ODBC connections from my Access 2013 desktop app to my Access 2013 custom web app has been difficult to say the least. There is a bug in Microsofts ODBC manager, so you cant use this to create a file dsn. I suspect there are other issues with ODBC connections to Azure under Office 365/sharepoint as I have had unexplained random ODBC errors, but I am finally up and running.

In the end, I have linked using a file dsn and saved the password with the linked table in the font end app. I know this is not ideal, as the password is exposed, but what other choice is there?

With an Access FE and client side SQL Server BE I would normally use a trusted connection, with the users network login being validated against the SQL Server rights granted for that user.

However with an Access 2013 Custom Web App there doesn't seem to be any sync between the Office 365 login and the rights to the Access 2013 Custom Web App, and you only have one server login to make the connection with, so how else can you enable multiple users on different workstations to open the Access 2013 desktop app and have rights to read/write to the linked tables without saving the login/pwd with the linked tables?

Thanks

Grant
 

Rx_

Nothing In Moderation
Local time
Today, 12:42
Joined
Oct 22, 2009
Messages
2,803
I have been watching this kind of demo at the Denver Area Access Users Group.
By ODBC, are you using SQL Server Native Client? That is what was demonstrated for Azure.
Myself and others have posted code (on this sites forum) to link remoter tables. My preference is SQL Server Native Client 11.0. The UN and Password are at least in code for SQL Server Security.
For example: we offer a one click to re-link all tables to SQL Server from production to test databases. A local table has the list of SQL Servers Table Names to link.
 

Users who are viewing this thread

Top Bottom