September 8, 2008 at 9:03 pm
binduldo,
you have to understand a concept of security behind linked server.
Your linked server currently using "YourSQLLogin", not sa.
If you will enter "sa" in your linked server settings, it will work for sa.
Ideally, you should change your settings on linked server and on your SQL server to work with Windows authentication (for this case scenario). But I would recommend reading several articles about linked servers first.
September 9, 2008 at 8:10 am
Here is the scenario .
I am connecting to the MSaccess database from the production server. If I logon to the server , I am able to see the database and table. If, however, within enterprise manager on my machine I register my production server and try to look at the linked server that I created on the production server I get the following message:
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB Provider returned message: The Microsoft Databse engine cannot open the file '//ampcds080/VI/info.mdb'. It is already opened exclusively by another user, or you need permission to view its data]
I know this file isn't opened by anyone. I gave Full control for everybody on the share..
I am not able to figure out what's wrong. Going crazy . I am new to SQL server..
thanks
bindueldo
September 9, 2008 at 11:21 am
September 9, 2008 at 3:05 pm
I followed the same procedure , and created the share on Access machine with Everyone Full Control.
After that I ran the query Exec SP_TABLES_EX 'VIMachine'
it gives me the same error . Microsoft jet database engine cannot open the file '\\ampss04\VI\Sam.mdb'. It is already opened exclusively by another user or you need permission to view its data
thanks
September 9, 2008 at 6:33 pm
Ultimate security guide for Access:
Test scenario:
1. Computer 1 - SQL server 32 bit edition running on Windows 2003
2. Computer 2 - Windows XP desktop.
On computer 2 create a new folder on c:\ drive called ACC.
Copied a new Access file in there. File name is saminfo.mdb. Access version is Access 2003.
Right click on Acc; Properties; Sharing. In column "Share this folder as" entered Acc
Clicked on Permissions.
Add -> Everyone (type it if necessary)-> Full Control.
OK.
Remotely connected to SQL server. Being logged as Windows account, created a linked server by executing following statement
Exec sp_addlinkedserver
@server = N'VIMachine',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'\etworkcomputername\ACC\SamInfo.mdb'
Go
make sure that linked server is created. In order to clarify it for the case when you are logged in to SQL server as SQL account, went to Security TAB of VIMachine linked server and modify it to "Be made without using security context".
Run following queries:
EXECUTE SP_TABLES_EX 'VIMachine' ( showing list of tables defined in this ACCESS file)
SELECT * from VIMachine...tablename
So far - everything successful.
If at this point you will go back to the ACC share created on a network computer and remove share permissions - you will get all the variety of errors you were receiving so far.
I am providing you with this example ONLY for test purposes, since creating a share permissions as a Full access for Everyone is a BAD PRACTICE. But that is the point where you can start from and build the necessary security after test passed.
Hope it helps.
September 10, 2008 at 7:09 am
Again I am stuck at EXEC sp_tables_ex 'VIMachine'
It is giving me the error Microsoft Jet database engine cannot open the file '\\ampcs04\Test\SamInfo.mdb' , It is already opened exclusively by another user or you need permission to view its data.
thanks
September 10, 2008 at 7:32 am
Forget about sp_tables_EX.
That is nice and simple sproc, which you do not need (or can read about in BOL).
What about select * from ACC...tablename?
September 10, 2008 at 7:56 am
I am getting the same error message for select * also.
I gave the sharing security for everyone and my login name full control . But if I go to the server where SQL server is installed , I can see the data . But if use the enterprise manager in my pc(where this server is added in the server group ) I am getting the error message.
thanks
September 10, 2008 at 8:10 am
Tough luck... As I said before that is happening because you did not read documentation on SQL server security.
That is also happening because your local SQL server on your PC is running NOT using windows account. I can bet that you are using either Local Service or Local System.
Now ask yourself a question: WHAT security rights could possible local service or local system have on the networked share??? NONE.
Why would you want to run stored procedure (which is working otherwise) through your local PC?
September 10, 2008 at 9:02 am
The linked server I am creating (SQL Server 2000 MAchine) is our Production server. Nobody cannot logon to that machine (remote desktop ) except administrators.
If other engineers needs to see the data , they have Enterprise manager in their local Pcs and registered this production server in their local enterprise manager as a SQL Server Group.
Like I said , I am fairly new to SQL server. What is the suggested workaround for this problem?
thanks
September 10, 2008 at 9:21 am
Sorry, your solution then does not make any sense for me.
Why would you want to involve SQL server at all? You are creating a stored procedure in SQL server just to let "engineers to get to the enterprise manager"?
Use Access directly. It will be less efforts and for sure will be faster.
September 10, 2008 at 9:28 am
No. All our web pages are connected to SQL Server 2000.
This data is in a machine on the same network , as an access file. I need to get that data to SQL server to see it or use it in reports or anything.
All Engineers are checking the data in SQL server. They use this data with other datas in the SQL server machine to make different reports and draw charts. So they need to see it in SQL server .
thanks
September 10, 2008 at 9:54 am
That is different from your original request, but let's try.
What is the authentication that you are using on your local machine?
How is your local PC registration is done (SQL authentication, Windows authentication)?
What is authentication on your SQL server where the stored procedure is located?
When you created the stored procedure how were you logged in on the server?
Is the user used on your local PC registration registered as a Login on the SQL server server machine?
September 10, 2008 at 10:04 am
What is the authentication that you are using on your local machine?
Windows Authentication . My useris has Administrative rights. My pc is also in the same network as SQL server and Access machine.
How is your local PC registration is done (SQL authentication, Windows authentication)?
I just have the Enterprise Manager tool in my PC.
What is authentication on your SQL server where the stored procedure is located?
Both windows and Sql server authentication
When you created the stored procedure how were you logged in on the server?
I did it through the enterprise manager in my machine . Tried with QA ,logged in with windows authentication , logged in with sa account, logged in with another administrator account but still gives the same error. The file is exclisively open or you don' have permission to view it.
Is the user used on your local PC registration registered as a Login on the SQL server server machine?
Yes. I use the same userid to log in to my pc and SQL server. It's a domain account.
thanks for trying to solve this with me.
September 10, 2008 at 10:10 am
Let's separate the issues.
I am under impression that you created a linked server and sproc on the SQL server machine (not the local PC) and if you are going to a remote console to your SQL server machine, you can execute it being logged there? Is that true?
If yes, then on the next step: can you verify that your Windows login is registered on SQL server machine?
What role?
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply