May 25, 2014 at 9:53 pm
I have a 3rd party dashboard application that I can only use SQL authenticated logins to connect to the database.
I'm trying to create a query within the application that will directly access an excel file through a linked server.
As a test, I login to SSMS as the sql auth user to run the linked server query below but the following error is returned:
select *
from Corporate...[Sheet1$]
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate" 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 "Corporate" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate".
When I login as a Windows auth user, I can successfully run the above query.
I noticed within the linked server's security definition that I cannot specify a windows auth user as the mapped Remote User or as the Remote login
I've tried creating a Credential object with the identity of the windows user and assign that object to the sql auth user but to no avail. I still get the same error
I am using SQL Server express so the option of an automated server agent job to import the excel file is not available.
Details:
SQL Server Express 2012
Office version: Excel 10
Provider: Microsoft.ACE.OLEDB.12.0
May 26, 2014 at 9:49 am
This should be able to help;
http://msdn.microsoft.com/en-us/library/ff772782.aspx
MCSE SQL Server 2012\2014\2016
May 26, 2014 at 5:12 pm
Thanks. As I mentioned above, I can successfully create a linked server to an Excel file and I can successfully query it if I log in as a windows auth. user. I cannot query it logged in as a sql auth user.
Within the Security definition of linked server, I've set:
For a login defined in the list above, connections will Be made using the login's current security contect
I've tried creating the following credential with the identity of the server's windows administrator account and assigned it to the sql auth user:
CREATE CREDENTIAL [File System Access] WITH IDENTITY = N'DOMAIN\Administrator', SECRET = N'<password>'
GO
ALTER LOGIN [sqluser] ADD CREDENTIAL [File System Access]
Unfortunately, the sql user still does not have access to query the excel file. Is it possible?
May 27, 2014 at 6:49 am
create a connection using the SQL user that the linked server is using in Excel. It won't work being logged into excel with your domain account because the domain account is not what you used in the linked server connections.
MCSE SQL Server 2012\2014\2016
May 27, 2014 at 9:28 am
You might want to see if the vendor has any suggestions.
We setup Kerberos, which picks up the Windows User and impersonates across servers.
Generally, most dashboards need security to the logged in user level.
Not a generic (many times administrative level) account used in something like a linked server.
May 27, 2014 at 5:14 pm
I cannot create linked server to an excel file logged in as sql auth. user (I've logged in as sa). The following error comes up:
TITLE: Microsoft SQL Server Management Studio
------------------------------
The test connection to the linked server failed.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate_sa" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate_sa".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Corporate_sa" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". (Microsoft SQL Server, Error: 7399)
May 28, 2014 at 2:39 pm
Nick,
Maybe these articles with help.
http://wiki.servicenow.com/index.php?title=Using_ODBC_Driver_in_SQL_Server_2008_and_2012
MCSE SQL Server 2012\2014\2016
May 28, 2014 at 8:49 pm
I've created a ODBC linked server to excel called: CORPORATE_ODBC2 by following the instructions in the 1st link. I've setup the provider parameters as suggested also. ie. Nested queries, Level zero only, Supports 'Like' operator all checked
Then I run the query and received the following error:
select *
FROM CORPORATE_ODBC2...[Sheet1$]
OLE DB provider "MSDASQL" for linked server "CORPORATE_ODBC2" returned message "[Microsoft][ODBC Excel Driver] Syntax error in FROM clause.".
Msg 7306, Level 16, State 2, Line 1
Cannot open the table "Sheet1$" from OLE DB provider "MSDASQL" for linked server "CORPORATE_ODBC2". The specified table or view does not exist or contains errors.
I've then set the Allow Inprocess attribute on the provider and re-ran the query with the following error:
OLE DB provider "MSDASQL" for linked server "CORPORATE_ODBC2" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "CORPORATE_ODBC2" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "CORPORATE_ODBC2" returned message "[Microsoft][ODBC Excel Driver] External table is not in the expected format.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "CORPORATE_ODBC2".
I also get the same error when using OPENQUERY
select *
FROM OPENQUERY(CORPORATE_ODBC2, 'select * from [Sheet1$]')
I've tried changing the SQL statement around by removing square brackets, $ symbol in both query types, but no luck. I'm assuming that an excel worksheet is classed as a 'External Table' as mentioned in the error message. Is there something syntactically wrong with my query as I cannot find a pure example online of an SQL query sourcing from a linked server that is configured to an excel file using ODBC? All the examples I've found are linked server queries to MS Access tables.
Note: At the moment, I've created the link and running the query as a windows auth. user. I want to make sure I can get SQL output first before trying to change to a sql auth user.
May 29, 2014 at 6:09 am
SQL User has no network rights.
Is what is in the Excel sheet something that can be imported and stored in SQL Server?
And did you ever contact the vendor?
That would be the quickest way to understand what their application supports.
June 1, 2014 at 8:22 pm
From the lack of positive responses that have been posted here, it appears Greg is correct! A SQL auth. user doesn't seem to have any rights to the server's file system.
I have contacted the dashboard vendor and they are considering expanding their login functionality to include windows authentication. I will also consider creating a batch process to import the excel file at regular intervals.
June 2, 2014 at 8:45 am
So sorry Nick if my responses were negative, I was only trying to help you see what Greg has said.
Can lead a horse to water but you can't make them drink!
Good luck with you endeavors. 🙂
MCSE SQL Server 2012\2014\2016
June 10, 2014 at 7:14 am
Hi Nick,
To be able to access/query Windows file system e.g. excel file using ad-hoc queries (openquery openrowset) or using
linked server I would suggest to:
1. Change sql server service account( form one of the virtual e.g. NT Service\MSSQLSERVER to a doman user, or a local windows account with enough credentials to access the excel file- linked server location)
2. Connect to the Sql instance using windows/sql server sysadmin account.
3. Create the linked server using the tsql script :
EXEC master.dbo.sp_addlinkedserver
@server = N'TEST123',
@srvproduct=N'ACE 12.0',
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'D:/TEST/ABC.xlsx', --excel file location
@provstr=N'Excel 12.0;HDR=No' -- include table header if needed
4. Test the LS connection (still logged as win user)
exec sp_testlinkedserver TEST123 --or run a simple query
SELECT * FROM TEST123 ...[Sheet1$] -- the name of the Excel Sheet you're retrieving data from
5. CTRL+N -open a new query window. reconnect using sa sql server login (for the sake of this exercise only)
6. use the scripts from the step 4 to try to use LS as a sql server user. You should see the err. msg. like:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST123 " returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TEST123 " reported an error. Authentication failed.
7. Final step is to add a sql server login(in this case sa) for the sql Authenticated account and set the remote user
and password to NULL.
exec sp_addlinkedsrvlogin
@rmtsrvname=N'TEST123',
@useself=N'False',
@locallogin=N'sa',
@rmtuser=NULL,
@rmtpassword=NULL
8. Now, try to repeat the test connection from step 4.
# It's not recommended to use the sa account for this task(or any non-administrative task 🙂 ). When using different sql server account , e.g "acc1_ls" - with minimal credentials, use "admin" remote user with no password
exec sp_addlinkedsrvlogin
@rmtsrvname=N'TEST123',
@useself=N'False',
@locallogin=N'acc1_ls',
@rmtuser=N'admin'
Cheers
Dean Mincic
MCTS Database Developer SQL Server 2008
D.Mincic
😀
MCTS Sql Server 2008, Database Development
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply