August 16, 2017 at 7:51 am
Hi All,
I am having trouble setting up my linked server to Excel.
The problem only occurs when using the linked server trough a non-sysadmin user, when I login using sa account or similar admin account the linked server works.
When setting up the linked server using the security setting "be made without using security context" it works with sa account, however the following error occurs using a non-sysadmin sql account:
Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.
I need to map my non-sysadmin login to a remote login, however an Excel file does not have a user/pass login, so how should this be mapped?
The Excel file is on the local machine.
Whichever mapping I am trying the following error occurs:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLS" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLS" reported an error. Authentication failed.
This error occurs also when i map the non-sysadmin account to the working sa account.
The linked server seems to work only when no specific username/password is defined in the linked server settings and the admin user is using the linked server.
I am not able to use windows authentication from my app where I connect to the SQL server.
How do I configure a working linked server to Excel using a non-sysadmin sql authenticated user?
Currently I am creating the server as follows (resulting in Authentication failed as described above):
Thanks, Arie
USE [master]
GO
/****** Object: LinkedServer [XLS] Script Date: 16/08/2017 13:48:22 ******/
EXEC master.dbo.sp_dropserver @server=N'XLS', @droplogins='droplogins'
GO
/****** Object: LinkedServer [XLS] Script Date: 16/08/2017 13:48:22 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'XLS', @srvproduct=N'XLS',
@provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\exceldump\dbdump.xlsx', @provstr=N'Excel 12.0'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'XLS',@useself=N'False',@locallogin=henk,@rmtuser=sa,@rmtpassword=pass
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'XLS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
August 16, 2017 at 8:37 am
To set this up, try something like the following:
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'YourLinkedServerName',
@srvproduct=N'ACE 12.0',
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'X:\PathToYour\File.xlsx',
@provstr=N'Excel 12.0;HDR=Yes'
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'YourLinkedServerName',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'Admin'
Sue
August 17, 2017 at 1:31 am
Yes that works with the Admin user! Thanks a lot Sue, been searching for hours 🙂
August 17, 2017 at 3:48 pm
arjan.vanvugt - Thursday, August 17, 2017 1:31 AMYes that works with the Admin user! Thanks a lot Sue, been searching for hours 🙂
Yeah...the answers for those errors are kind of all over the place when you search on the issue.
Glad it worked for you - thanks for letting me know.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply