Hi guys
We have a domain server,
15 users,
Access mde as FE, with SQL server as BE on a shared folder1,
shared folder2 contains files.
The users have full access to the SQL server BE folder, with full control for read/write/modify,
so no issues with the Database.
On shared folder2, we have files/documents.
The FE will read the files the use want to save in folder2, and it should be able to save them there,
and through FE, it can display the files on folder2 for the user, and on click, it will open it, delete it, based on the user privilege set in the DB.
I can code the above.
my question is, since folder2 will be shared on the network,
how can I make the DB access it, and not allow the users to access it.
in another words, how can I make the DB act like a user with full privilege to folder2, and how can I set the authentication username and password to the server via code.
TIA
jaffer
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
If i am understanding this correctly, the you have linked tables in the front-end to sql server yes?
I would have thought that you could setup user permissions inside of sql server, and then disallow the users access to the database shared folder, Worst case scenario, setup the sql server in mixed mode and and setup some sql authentication and modify the links in the fe mde.
This is not so much a SQL problem as it is an OS problem, so you need to look at the users (accounts) and controls from an OS perspective. So, might be worthwhile making a new domain user and having that account start up SQL Server Service, that way when SQL Server wants to access shares and so on you can individually identify the SQL Server Service Account and change access control accordingly. As a matter of course nowadays, I always set up a domain account for SQL Server Service and SQL Server Agent Service rather than use some of the default or built in accounts. Either which way, will be an idea to see how you can group various users so that you can control access a bit easier.
Have a look at http://support.microsoft.c
I think the other users on here aren't reading your description correctly. Let me explain it a little better.
His request has nothing to do with SQL, he has a folder on a server (Folder2) that contains documents that Access should be able to open and or delete.
He wants Access to be able to open/delete these files, but not the user. The user should get an access denied when they try and few that folder.
@jaffer: Ideally, in your code to list the files from Folder2, you will want to do some sort of Impersonation, that will allow Access access to that folder with permissions other than the current user. Access uses VBScript so you should be able to impersonate with your query. Something like:
Dim strPath, strUser, strPW
strPath = "\\ServerName\Folder2"
strUser = "DOMAIN\User"
strPW = "P@$$w0rd"
What command are you using to list the files in the users folder? There should be a way to include authentication with it.
Agree, it is not a sql server problem - it is OS and user account privileges - the reason for those links is to establish a domain user account for SQL so you can control and identofy users seperately. Once you do that, you can easily deny access to everyone but the SQL Server Service Account (domain user).
aibusinesssolution, Mark
Thanks for your input, very much appreciated.
You are right interms of this is more of a OS Q rather than SQL, however, I always like to explain the whole setup, as sometimes the solution is some where else other than where I see :)
Mark:
I will go through the links you gave, and I prefer the SQL being a Domain user, it might make things easier.
Anything further hints you can provide for this setup?
aibusinesssolution:
If Access have the privilege as a Domain user, then why should I need to identify its username/password everytime it accesses folder2?
I use normal FileSystemObject method.
jaffer
If you do not want the standard domain user to have access to folder2, but you Do want your access app to have access to that folder, then you will need to identify the username and password of a user that Does have access to folder2 with your FileSystemObject.
Standard User tries to view Folder 2 = denied
User with read/write permissions impersonated by FileSystemObject = granted.
agree, but doesn't the permission gets granted the 1st time Access log-in on the Domain server?
then after that, Access can read/write in folder2 without further permissions!!
It's like a normal Domain user who log-in one time, then everything they do, doesn't require further log-in.
Am i on the same wave lengther as you are??
jaffer
Jaffer
Assume makes ASS out of U and ME.
I'm sorry but I am unclear as to what files you are talking about in SharedFolder2
Just to clarify
a) your users should be able to access (read/write/modify) the folder in which the Front End mde resides.
b) your users do not need access to the folders where the mssql data is, as once you have setup the security correctly in....
i) your sql server
ii) your linked tables in the mde
... the mde will be able to access the table data in sql server.
You need to search for a solution of how to properly setup security within SQL Server in order to resolve your connectivity issues.
mildurait - yep jmoss11 is a master, and I am kind of handy on SQL Server as well. But I guess I am asking you to trust me on that score.
The thing is, if you can personalise the ownership of the (SQL) services with domain user accounts, then you can manage them just like any other user, and that is the difference here - we need to differentiate between user accounts.
Folder 2 we ONLY want the sql account to have access, it is easy enough to hide from all users, but need to be able to indentify the one user (or more accurately specific users) who can access that folder.
jaffer, those links are pretty self explanatory. The second one does have a few additional links going off to relevant sections. I think it is best to read through them, and maybe then ask questions. We will be here, and then when it comes down to more specific settings, then I think aibusinesssolution has the goods...
Business Accounts
Answer for Membership
by: aibusinesssolutionsPosted on 2009-02-28 at 13:38:06ID: 23765466
Sounds like you need the access front end to impersonate a user that has access to folder2, instead of running as the current user.
It's simple to make a program run as administrator in the compatibility tab, but I'm not sure about making it run as an alternate domain user.