Linked Server

Question

Linked Server

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

megha.chandak88 SSC Eights! Points: 990 More actions · Answer 1

Error using SQL login :Continue ...

7) SQL AGENT and SQL services are running using Service account .
Service account is in administrator group. Also having Full access to the folder from where we are reading Excel.

ERROR I AM GETTING WHILE EXECUTING IT USING NON SYSADMIN SQL LOGIN AND WINDOW LOGIN HAVING ADMIN RIGHTS

1) xp_cmdshell 'type "L:\Linked Server\EXCEL.xls"'
--- I can see data, but not in readable format for non sysadmin SQL login and Windows login

2) select * from OPENROWSET('MICROSOFT.ACE.OLEDB.12.0',
'Excel 8.0;HDR=YES;DATABASE=L:\Linked Server\RT_CargaPolizas.xls',sheet$)

--- Using SQL LOGIN :
Msg 7415, Level 16, State 1, Line 9
Ad hoc access to OLE DB provider 'MICROSOFT.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

-- Using Windows login
OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 8
Cannot initialize the data source object of OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)".

3) SELECT * FROM OPENQUERY(ExcelServer2, 'SELECT * FROM [Sheet$]')

--- Using SQL LOGIN
Msg 7416, Level 16, State 2, Line 12
Access to the remote server is denied because no login-mapping exists.

-- Using Windows login
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelServer2" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 11
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelServer2".

4) SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=L:\Linked Server\EXCEL.xls;Extended Properties=Excel 8.0')...Sheet$

--- Using SQL LOGIN
Msg 7415, Level 16, State 1, Line 14
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

-- Using Windows login

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 13
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

5) SELECT * FROM ExcelServer2...[Sheet$]

--- Using SQL LOGIN
Msg 7416, Level 16, State 2, Line 19
Access to the remote server is denied because no login-mapping exists.

-- Using Windows login
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelServer2" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 18
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelServer2".

6) SELECT * FROM OPENQUERY(LINKEDSERVERNAME, 'SELECT * FROM [Sheet$]')

--- Using SQL LOGIN
Msg 7416, Level 16, State 2, Line 14
Access to the remote server is denied because no login-mapping exists.

-- Using Windows login

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "DBL_POLIZAS" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 19
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "DBL_POLIZAS".

Sue_H SSC Guru Points: 90860 More actions · Answer 2

That's a whole lot of reading to get one questions. Not really needed. Executing:
sp_configure 'Ad Hoc Distributed Queries'
Will tell you if Ad Hoc Distributed Queries is enabled
You really don't need to follow advice in every link as they are all doing different things. There was no need to grant ADMINISTER BULK OPERATIONS permission - if you read the links you referred to it says: OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider. To use the BULK option requires ADMINISTER BULK OPERATIONS permission.
You aren't doing a bulk operations at this time, just a query.

But in all of that writing over and over, you never did answer the second questions. You just need to execute the following:
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0'

No offense but you seem to be totally lost on all of this. It would be better for you to understand linked servers, linked server properties, providers - what they are and how they play a part in all of this, what the provider and linked server settings need to be for different options, etc. If you don't get your head around that you can't support the linked server. After you understand more of what linked servers are and how they work and need to be configured, you can walk through an Excel linked server in this article:
Excel Import to SQL Server using Linked Servers

Sue

megha.chandak88 SSC Eights! Points: 990 More actions · Answer 3

HI Sue,

Actually we are trying to coonect using non admin sql login having developer access.

SELECT * FROM OPENQUERY(ExcelServer1, 'SELECT * FROM [Sheet1$]')

While executing it using SQL login it giving error as :

"Access to remote server denied because no login mapping exist."

As per the link you have shared Everything is in place the only doubt is as per link it
providing access to temp drive but that is for 32 bit server .

And the server where I am trying is 64 bit.

Still getting error .

Regards,
Megha Chandak

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Okay...still don't know what the provider settings are so execute this - you need to change the name of the linked server to whatever you need it to be and you need to change the path to the Excel file.

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

megha.chandak88 SSC Eights! Points: 990 More actions · Answer 5

Hi sue,

Thanks you very much .

I am stuck in this issue from long time.

the mistake I was doing is @rmtuser = Admin .

I was mentioning rmtuser as " sa or Windows" login.
Thanks a ton. you have made by day. thank you.

Regards,
Megha chandak

megha.chandak88 SSC Eights! Points: 990 More actions · Answer 6

HI Sue ,

one question ... Can we implemnt this on prod . Will it be any security constraint if we mention @rmtuser as Admin.

Regards,
Megha Chandak

megha.chandak88 SSC Eights! Points: 990 More actions · Answer 7

HI Sue,

A quick question .. if we mention @rmtuser as admin which login it will refer while executing linked server.

Also can we implement in production also there will not be any security issue.

Regards,
Megha Chandak

Sue_H SSC Guru Points: 90860 More actions · Answer 8

Glad it's working now. There is no security risk with that, using an account called Admin makes it seems like there is but there isn't. It's an old MS Access thing. I can't remember all the details of it but in a multi-user environment,the default user for Access is Admin with a blank password. Doesn't really mean Administrator of anything it's just the user you had to use if you hadn't setup security.
From a SQL Server standpoint, the login that accesses the file is going to be the service account for SQL Server.

Sue

megha.chandak88 SSC Eights! Points: 990 More actions · Answer 9

megha.chandak88

SSC Eights!

Points: 990

March 6, 2017 at 2:54 am

#1931739

OK thanks a lot.

mtrudel337 SSC Rookie Points: 33 More actions · Answer 10

In response to @sue she provided the correct information for me

THE USER NAME TO PROCIDE TO AN EXCEL FILE IS

User: Admin

No pasword

My linked server is an xlsx using ACE driver

Works fine from SSMS in a view accessing the remote server but from an SSRS report I got a login error