X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> User / Administrator, Any Versions    
post Oct 10 2017, 10:23 AM

Posts: 237
Joined: 9-February 06
From: South Africa

Please can someone give me some advice
I have a MS Access frontend with a SQL Express 2014 backend. This is used over the network by other computers.
Recently the owner decided to split the server computer into two users. This seemed to cause problems. They have now reformatted and I am in the process of reinstalling SQL Express. But I see that when doing the installation that the computer is setup as a user... not as administrator.
Is this what is causing the problems?
Must you have administrator rights on the computer where the backend is held?
Go to the top of the page
post Oct 10 2017, 05:06 PM

UtterAccess VIP
Posts: 2,552
Joined: 12-April 07
From: Edmonton, Alberta Canada

Well, installing and setup of SQL server on that computer likely requires admin rights.

However each workstation on the network that connects to the instance of SQL server most certainly does NOT require admin rights.

Each workstation will require a valid SQL logon, and ALSO a valid user for each database. (The SQL logon, and the required “user” for each database usually have the same name).

Now the above assumes that each workstation is connecting to SQL server with a valid SQL logon and not using the windows logon system.

So the second choice is what is called windows authentication. That means the permissions and control of “who” has rights to the SQL server is NOT a SQL logon but is a valid windows logon. This setup means that you’re running a “domain” controller, and the creating of windows users is controlled by that domain server, and that includes the creating of new windows users. And it is on that “domain” controller that you also manage and give permissions to use SQL server.

However, most “small” business with say less than 15 users tends NOT to use windows authentication for each workstation. And thus they don’t use windows logons with SQL server – but use SQL logons.

So SQL server has two methods and systems of permissions. Windows authentication, and that of SQL logons. And of course SQL server can have “both” types of users activated at the same time.

But a simple answer to your question?

No, each workstation does not require nor need admin rights, but do need a valid SQL logon if you using SQL logon, or you would go to the “domain” server and create each user, and assign them rights to the running instance of SQL server. So “use” and permissions to that SQL server can be controlled by two different methods.

I for the most part use SQL logons – even in cases where windows authentication and logons are used by the company. So I tend to create “one” SQL logon that has rights to the database, and I use this “same” logon for the linked tables. In effect this means that all workstations are logging onto SQL server and using the same one login + password that linked the tables with.

Last but not least: If you use windows authentication, then when linking Access tables no user nor password is required since the permissions are control by the windows operating system and not SQL server. So the rights are determined by your logon + password when starting up windows. (the windows logon).

Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
post Oct 11 2017, 01:14 AM

Posts: 237
Joined: 9-February 06
From: South Africa

Hi Albert
Many thanks for you response.
The server computer does have administrator rights and this is the computer where I am having the problem
The reason why re reformatted and reinstalled in because of an error that we are getting :

Run-time error '3156'
ODBC - delete on a linked table failed
[Microsoft][SQL Server Native Client 11.0 ... etc

In my attempts to fix this, I have deleted the SQL Native client 11 and downloaded a new one and and installed it - its a 64bit computer so I downloaded the 64bit Native client. I installed in using the syswow64 but nothing I do is working, The error still appears. In case it was a code problem, I manually added a new record to a table and then tried to delete it and get the same error.

Please give me some advice, I am desperate.
Go to the top of the page
post Oct 11 2017, 04:06 AM

UtterAccess VIP
Posts: 9,407
Joined: 6-December 03
From: Telegraph Hill


Albert will be able to give an authoritative answer when he next comes online, but in the meanwhile ...

I suspect that if you are using 32bit Access in your 64bit environment, then you will need to use the 32bit SQL Native Client driver.





David Marten
Go to the top of the page
post Nov 2 2017, 09:55 AM

Posts: 237
Joined: 9-February 06
From: South Africa

Thanks all for your responses.
I got a friend who is an expert in SQL and he queried the data and discovered that there was some sort of data integrity problem. So we restored a backup and the problem went.
Thanks again
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    20th January 2018 - 10:26 AM