x

linked server & permissions

Hello all,

[see previous question & answer for background][1]

$env:temp | out-file C:\Users\Tony\Documents\out.txt -append

I know this was a while ago but I find myself in the same position as you were then and could do with a couple of pointers.

i followed your advice and ran the powershell commands as above.

1 component as Network service logon. Tony C:\Users\Tony\AppData\Local\Temp

2 component as Local service logon. C:\Users\Tony\AppData\Local\Temp Tony

3 component as local system C:\Users\Tony\AppData\Local\Temp Tony

as can be seen the system always seems to point to the "tony temp"

I have changed the permissions to temp to allow network service and local service full control.

The services directory, as mentioned in your previous answer, also has permissions for full control.

The odd bit about this is that i have 3 or 4 times managed to get the access database to return data. 1st time I changed from network service to local system. It all worked until I started the computer the next day. After a few attempts I changed to network service and it started to work again. Then after a restart it would not work and this time no swithing to alternative component logons has any affect. I changed the permissions to allow "everyone" but with no luck.

Oh I am using 32 bit and have the access database engine installed. Also i am using 2008 r2 developer version.

[1]: http://ask.sqlservercentral.com/questions/77455/location-of-user-account-temp-dir-for-service-accounts-jet-drivers-on-64-bit?page=1#78033
more ▼

asked Aug 21, 2011 at 12:35 AM in Default

tony_l gravatar image

tony_l
21 2 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

What error do you get? What query are you executing?

Did you read and follow all the links in the previous question? I had to try multiple things before arriving at a satisfactory conclusion.

Using the [SysInternals ProcMon tool][1] was useful in determining/clarifying that it was those directories I needed, but that was only after many earlier steps of getting the 64 bit drivers configured properly.

[1]: http://technet.microsoft.com/en-gb/sysinternals/bb896645
more ▼

answered Aug 22, 2011 at 12:53 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

hi Kev Sorry for delay in response I am in Australia..

I did follow the links and then did as you did with permissions and logons etc.

here is the message

"Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "SQLACCESS". (.Net SqlClient Data Provider)"

I have tried the openrowset, opendata source and linked server all with the same error.

here are the attempts.

EXEC sp_addlinkedserver @server = N'SQLACCESS',

@provider = N'Microsoft.ACE.OLEDB.12.0',

@srvproduct = N'OLE DB Provider for ACE',

@datasrc = N'C:\Users\Tony\Documents\Northwind.accdb';

--------------------------------------------------------------------------- SELECT [Company] ,[Last Name] ,[First Name] FROM OPENROWSET(N'Microsoft.ACE.OLEDB.12.0','C:\Users\Tony\Documents\Northwind.accdb';'admin';'',Customers)

---------------------------------------------------------------------------------- SELECT [LAST NAME], Company FROM OPENDATASOURCE(N'Microsoft.ACE.OLEDB.12.0', 'data source=C:\Users\Tony\Documents\Northwind.accdb;user id=admin')...Customers


All i am trying to do is connect to the northwind database as an exercise. I am an AS400 programmer converting my skills to MS SQL 2008. I already know DB2.

The Procmon gives a lot of info but the only relevant thing I could find is:-

9:45:59.5985966 AM svchost.exe 4016 CreateFile C:\Windows\ServiceProfiles\LocalService NAME COLLISION Desired Access: Read Data/List Directory, Synchronize, Disposition: Create, Options: Directory, Synchronous IO Non-Alert, Open Reparse Point, Attributes: N, ShareMode: Read, Write, AllocationSize: 0 9:45:59.5995218 AM svchost.exe 4016 CreateFile

C:\Windows\ServiceProfiles\LocalService\AppData\Local   NAME COLLISION (as above)

C:\Windows\ServiceProfiles\LocalService\AppData\Local\PnrpSqm   NAME COLLISION

I will need to get better at this tool. The thing that confuses me is that it worked the first time I changed the logon and then it stopped. When i changed back to network service it worked and then it stopped (afetr a reboot) - To me it seems as though the exact sequence/setup is not right.

i also created a system DSN and this did not work - changed the provider to MSDASQL and it still failed.

Tony

An update in case it sheds any light..

running the profiler led me to a stored proc sp_catalogs which in turn has a function of

fn-remote-catalogs. --The underscores did not show up--- Now I cannot find this anywhere in the system databases and because it is missing I believe this is causing the eventual error. Of course i could have been completely misled.

Anyone know if this is actual function. I ran a repair on sql but this had not affect.

Aug 23, 2011 at 12:16 AM tony_l

Have you ensured that the MS Access database is not open elsewhere? You probably have , but it's worth checking.

When you run these queries are you running a query window connected to the database using a login that is in the sysadmin role?
Aug 31, 2011 at 06:21 AM Kev Riley ♦♦
Access is not open. I even tried it with it open to see if it made a difference. I am running the query or opening object explorer (linked servers) using windows authentication which has a server role of sysadmin. My sql instance is the default. The access database never gets as far as being opened. Except for the few times it has mysteriously worked.
Aug 31, 2011 at 06:22 PM tony_l
well i am more baffled now. It has started to work again and this time i ran the profiler over it and spotted nothing that would lead to an explanation. The remote_catalogs as pointed out seems to have been a red herring. Also as pointed out I have no sysremote objects and it doesn't matter. All services are started with network service but this has been the case several times. I have no that if shut down the computer and start up again it will stop working!!!
Aug 31, 2011 at 10:40 PM tony_l
yep it stopped working
Sep 02, 2011 at 06:17 AM tony_l
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x107
x49

asked: Aug 21, 2011 at 12:35 AM

Seen: 2025 times

Last Updated: Sep 01, 2011 at 12:45 AM