Linked Server and Non-sysadmin User

Hi,

Can a user not belonging to sysadmin fixed role run queries against the linked server (Excel Linked Server)? User has permission to the folder/file (Excel File).

If not possible, can this be achived using Proxy account?

Thanks

Shafiq

See this thread for answer

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=405118&SiteID=1

Linked Server - Excel via UNC

Is it possible to create a linked server to an Excel spreadsheet using a UNC?
I have no problems doing it with a file on the local server, but am having
trouble when using a UNC, which is what I really need to do. If I use no
security context, I get an upspecified error. If I specify a remote login, I
get an authentication error. I have tried domain\username and password and
without the domain name. All the articles I find on the internet only refer
to local paths.
Thanks!
Buddy
Hi,
I tested a Linked Server to Excel file on a Network share with UNC
(\\testmachine\testshare\testfile.xls) successfully. On the Security tab,
I did not enter any Logins in the Login Mapping Grid, at the bottom
selected the 'Be made without using a security context' option. It worked
fine when I had permissions on theNetwork share to Read the XLS file. when
the permissions on the network share were removed, I got an error.
I suggest that you check the following:
1. SQL Server Startup Account - Does that have Network permissions on the
share where Excel file resides
2. Current User Windows Account - Does that have Network permissions on
the share where Excel file resides
After checking these two points if you still get an error, Please send me
the exact error message that you are receiving.
Meanwhile I will compile some documentation on this issue (Security Context
used by the Linked Server to Excel Files) and keep you posted on this.
Thanks
Soma Sekhara Reddy

Linked Server

Hi,

I have created a linked server using Excel file. When I run the query again this linked server, it works fine as I have all kind of authorities. But when user run it gives the following message (Queries are running from ASP.Net Page)

System.Data.SqlClient.SqlException: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LPG".

Here is how linked server is configured

EXEC master.dbo.sp_addlinkedserver @.server = N'LPG', @.srvproduct=N'Excel', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'E:\MyFolder\LPG.xls', @.provstr=N'Excel 8.0'

Please Guide.

Should I have to add a Proxy Account so that Domain User can also access the information from excel file?

Select * from LPG...Sheet1$

Please Guide.

|||

Please make sure you have configured the remote login and password for the provider using sp_addlinkedsrvlogin stored procedure.

The BOL topic

Accessing and Changing Database Data > Distributed Queries > OLE DB Providers Tested with SQL Server > OLE DB Provider for Jet

has more details.

Excerpts from that topic:

To access a nonsecured Access database, SQL Server logins trying to access an Access database should have a login mapping defined to the user name Admin without a password. The following example enables access for the local user Joe to the linked server named Nwind.

|||

I did and still not working. I am working with Microsoft to resolve this issue. They have also duplicated the same issue. Waiting for there response.

Thanks

|||

After getting support help from Microsoft, I have found this solution.

Any Non-sys admin user wants to get the access to linked server (Excel ) must have the Read/Write access to SQL Server Service account's temp folder.

C:\Document and Settings\Administrator\Local Settings\Temp

or

C:\Document and Settings\Administrator.DomainName\Local Settings\Temp

Thanks

Shafiq

|||

Hi,

Temp folder has granted the proper permission but sometime it does not work. So only solution right now is to reboot the server. After rebooting the server it works fine. Any one has any idea why it behaves differenlty?

Error is shown below:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LPG" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LPG".

Linked Server

Hi,

I have created a linked server using Excel file. When I run the query again this linked server, it works fine as I have all kind of authorities. But when user run it gives the following message (Queries are running from ASP.Net Page)

System.Data.SqlClient.SqlException: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LPG".

Here is how linked server is configured

EXEC master.dbo.sp_addlinkedserver @.server = N'LPG', @.srvproduct=N'Excel', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'E:\MyFolder\LPG.xls', @.provstr=N'Excel 8.0'

Please Guide.

Should I have to add a Proxy Account so that Domain User can also access the information from excel file?

Select * from LPG...Sheet1$

Please Guide.

|||

Please make sure you have configured the remote login and password for the provider using sp_addlinkedsrvlogin stored procedure.

The BOL topic

Accessing and Changing Database Data > Distributed Queries > OLE DB Providers Tested with SQL Server > OLE DB Provider for Jet

has more details.

Excerpts from that topic:

To access a nonsecured Access database, SQL Server logins trying to access an Access database should have a login mapping defined to the user name Admin without a password. The following example enables access for the local user Joe to the linked server named Nwind.

|||

I did and still not working. I am working with Microsoft to resolve this issue. They have also duplicated the same issue. Waiting for there response.

Thanks

|||

After getting support help from Microsoft, I have found this solution.

Any Non-sys admin user wants to get the access to linked server (Excel ) must have the Read/Write access to SQL Server Service account's temp folder.

C:\Document and Settings\Administrator\Local Settings\Temp

or

C:\Document and Settings\Administrator.DomainName\Local Settings\Temp

Thanks

Shafiq

|||

Hi,

Temp folder has granted the proper permission but sometime it does not work. So only solution right now is to reboot the server. After rebooting the server it works fine. Any one has any idea why it behaves differenlty?

Error is shown below:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LPG" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LPG".