OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
The scenario:
You want to use a linked server (directly or indirectly) in SQL Server using the OleDb provider.
With this provider you wish to use, for example, the OPENROWSET query against a file of some sort, *.txt, *.csv, *.xls, etc.
The problem:
You issue a select using the OPENROWSET and the OleDb Provider against a text file, for example:
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [Test.csv]')
And sometimes you may get the following error:
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" 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 "(null)".
Now, a more verbose error would explain why the query fails, this is the case for other situations, for example:
If the filename is wrong or if the file doesn’t exist, you would get:
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Test.csv'.
Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".
If the directory name is wrong or if the directory doesn’t exist, you would get:
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'C:\Temp\' is not a valid path.
Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
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 "(null)".
If the file is open by another user, you would get:
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine cannot open the file ''.
It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT * FROM [Test.csv]" against OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".
The errors above are all easy to understand and should therefore be easy to resolve, but what about the:
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" 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 "(null)".
Not very clear what is going on.
Now, the simple explanation is this, when using a linked server (and the OPENROWSET is a sort of linked server) then a temporary DSN (Data Source Name) is created
in the TEMP directory for the account that started the SQL Server service. This is typically an account that is an administrator on the machine.
However, the OLEDB provider will execute under the account that called it. This user can even be sysadmin on the SQL Server, but as long as this user is not an administrator
on the machine, it will not have Write access to the TEMP directory for the SQL Server service account.
The longer explanation is perhaps best shown by a step-by-step explanation, so, here goes (it does not have to be a .csv file or this particular provider).
.1 Create a *.csv file that looks something like this:
ID;NAME;CITY
1;Mike;Stockholm
2;Michael;London
.2 Save it as C:\Temp\Test.csv
.3 Configure SQL Server Surface Configuration, "Ad Hoc Remote Queries" and enable "Enable OPENROWSET and OPENDATASOURCE Support"
If this is not done, you will get the following error:
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part
of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.
For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
.4 SQL Server service starts as Admin/Admin, and we have logged into the machine as Admin/Admin.
.5 Admin/Admin is a member of the Administrators group on the machine.
.6 Execute: SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [Test.csv]')
.7 This returns the rows in the *.csv file
.8 Add a user to the same machine called User/User.
.9 Add the User/User to the SQL Server Logins with Windows Authentication and give it the SysAdmin role.
.10 Log out from the machine and login as User/User.
.11 Start Sql Server Management Stuido, login as User/User and open a new query
.12 Execute: SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [Test.csv]')
.13 This fails with the:
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" 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 "(null)".
.14 This is because the SQL Server Service is trying to write the temp DSN to the temp folder for the login that started the service, in this case the Admin/Admin login.
The temp folder is something like: C:\Documents and Settings\Admin\Local Settings\Temp
.15 As mentioned, the OleDbProvider will always execute in the context of the user who initialized it, in this case User/User.
.16 User/User has no rights on this folder (C:\Documents and Settings\Admin\Local Settings\Temp).
If running FileMon when the SQL is executed, we can see the following:
sqlservr.exe:000 QUERY INFORMATION C:\DOCUME~1\Admini~1\LOCALS~1\Temp ACCESS DENIED Attributes: Error
So to summarize so far:
The SQL Server service is started as Admin/Admin, when the select is made, the OleDb provider is invoked by User/User.
Now the OleDb provider attempts to create a temporary DSN in the temp directory. This will be the temp directory for the SQL Server service (Admin/Admin)
but the user (in this case User/User) does not have write permissions on this folder. And the error occurs.
There are two ways to resolve this.
Option 1:
Log out of the machine and log in as the account that starts the SQL Server Service (in this case Admin/Admin) then start a command prompt
and type “set t” (no quotes), this will show something like:
TEMP=C:\DOCUME~1\Admin\LOCALS~1\Temp
TMP=C:\DOCUME~1\Admin\LOCALS~1\Temp
these are the environment variables set for %TEMP% and %TMP%, so go to that folder and right click and select Properties -> Security,
then add the user, in this case User/User, note that the default for the user is Read&Execute/List Folder Content/Read, this not enough, you have to select Write as well.
Log out, and log in again as User/User and rerun the command from SSMS. This time it should work.
Option 2:
Log on as Admin and change the TEMP and TMP variable to, for example, C:\Temp, basically this moves the Temp directory out of the Documents and Settings folder.
However, you must restart the SQL server for this to take effect.
So basically, what happens is that when SQL Server starts, it uses the Temp folder of the startup account (Admin/Admin) but the MICROSOFT.JET.OLEDB.4.0 will always execute
as the user who calls the SQL command (User/User) and this will fail unless this user does not have Write access to that temp folder.
Without knowing all setups out there, perhaps option 2 is the preferred solution since with option 1, you will have to add ALL the users that will invoke the provider which may not be practical.
Also, when changing the startup account for the SQL Server service, then the TEMP directory for that account will be used, and you will see the error again until you, again, give write permissions
for all the users on this TEMP folder.
References:
"SQL Server 2005 Books Online (September 2007) - OPENROWSET (Transact-SQL)"
https://msdn.microsoft.com/en-us/library/ms190312.aspx
"SQL Server 2005 Books Online (September 2007) - Ad Hoc Distributed Queries Option"
https://msdn.microsoft.com/en-us/library/ms187569.aspx
"FileMon for Windows"
https://technet.microsoft.com/en-us/sysinternals/bb896642.aspx
Comments
Anonymous
October 22, 2008
Thank you Maspeng! Excellent work. I searched for hours looking for a resolution to this problem and your solution was the only one that made sense and worked. Non Domain admins need to be added with write access to the sql server account's temp directory if they want access to the linked server. Who knew? Thanks again.Anonymous
January 18, 2009
This is one of the best articles with real-life how-to. top notchAnonymous
February 18, 2009
This is a great article, but it doesn't seem to solve my problem. I wrote a SQL script that queries a linked server. It works fine if I run it from SQL Server Management Studio Express (2005). It also works fine if I run it from the ASP.NET Development Server that launches when I test an .aspx page in Visual Studio 2005 (in this case, I run the script by calling ExecuteCommand() on a SqlCommand object). The only time it doesn't work is when I publish my project to an IIS directory and run it out of there. Then, I get the exact same "unsepcified error" you reference in this article. Unfortunately, giving "full control" to the [MACHINENAMEASPNET] user of the Temp folder seems to have no effect. I've been struggling with this for a while now. Do you have any idea what could be causing this problem? If you'd like more details on my situation and what I've tried so far, please see this thread: http://forums.asp.net/t/1383105.aspx Thanks!Anonymous
April 07, 2009
hi another reason for this issue might be that if you have renamed the original database file. I faced similar issue while trying to run linked query to a .dbf file. Originally the name of the file was fl.dbf but later i copied it and renamed it to fl1.dbf. when i faced the issue i ran procmon and found that the query is still trying to access fl.dbf. But i dont have any reference to the file fl.dbf in the query. I think what actually happens is the JET driver reads the header and then tries to access the file by the name mentioned in the header.since the name in the header doesnot change on copying and renaming, it fails to find the file and give us the error.Anonymous
August 13, 2009
We have several development workstations here with the SQL Server set to log in via the system account. Without changing the account, which I have not yet tried, nothing we have tried resolves this problem. Even administrators of the machine cannot create a Jet Linked server. Also, it seems as though linked servers in SQL 2005 that log in via a user account still timeout and cease to work. I still have to restart SQL on our development server everytime someone wants to use a linked server, because we still get the unspecified error, even though everyone has sufficient rights. After restarting the SQL service it works again for a limited time.Anonymous
February 02, 2010
Awesome! This is a very common problem (as noted by the amount of web content regarding it). However, so far this is the ONLY article I've seen that has explained exactly what the problem is and how to fix it. Thank you very much! As with the other posts, I spent a ton of time trying to research this. I was experiencing linked server null errors while trying to import from Excel using OpenDataSource. Thank you again and keep up the great work!Anonymous
January 03, 2011
Hello, I have the same message. Applied your solution (write permission to temp directory). It fixed my problem, but the problem comes back each time we reboot the server. After the server is rebooted, the SQL Server service starts automatically, and OpenRowSet fails. I have to stop and start (or simply restart) the SQL service and it works until the machine is rebooted... So our solution is to restart the SQL Server service after rebooting the machine. Any help with getting rid of this would be highly appreciated... Thanks, YvesAnonymous
January 03, 2011
Hi Jason, We experiment the same problem here on one of our production servers. Same thing works fine on quite identical production servers. You said "After restarting the SQL service it works again for a limited time." The limited time ends when we reboot the machine... YvesAnonymous
January 21, 2011
I have the same problem, but it has occurred recently and only on the computer I use to develop programs, on other computers, including laptops, everything proceeds normally, ie I can transfer data in a CSV file type in a DataSet / DataTable without any problem. I think at this point, Microsoft should solve the problem, or it should act on the security settings of the computer where there is a serious problem. At the moment so I'll decline to upload data to the database from a text file, which is CSV, addressing the nature of things by loading an Excel file, after importing it in the data from the CSV file type. I look forward to a response and thanks for your attention. Francesco, SicilyAnonymous
May 05, 2011
The comment has been removedAnonymous
May 10, 2011
An article with great guidance. However, I have this simple problem: how to login as Admin/Admin, currently I login as a User with Windows Authentication? thanks.Anonymous
June 30, 2011
Great article. Spent a long time looking for a article like this and finally able to understand the workings of openrowset. And your solution works!!Anonymous
November 06, 2011
Nice blog, hopefully it will answer all questions related to OPENROWSET.Anonymous
May 22, 2012
this is the best step by step i have ever had ....on any subject!!!Anonymous
January 08, 2013
There is other reason for "Unspecified error" in SQL Server 2008 R2: I don't know why but some times, the SQL Agent is running but is "disabled"... this is weird, sad and true, so make you sure in the SQL Server Management Studio that SQL Server Agent is enabledAnonymous
April 29, 2013
Excellent, really excellent - got me out of a bind. Thank you.Anonymous
January 02, 2014
Right click on the program's shortcut or an .EXE file, BAT file, CMD file, or MSI file, and click on Run as administrator, it will impersonate the adimin/adminAnonymous
February 09, 2015
Thanks a lot Michael! This was fantastic! I had the same problem and this article helped me fix the issue!Anonymous
July 23, 2015
The comment has been removedAnonymous
August 04, 2015
I came across this very problem, but I have another solution, and it depends on what interface you're using your SQL Server instance from. In my case it was ASP.net 2, under VS2005. If that is you, then I suggest (somewhat reluctantly) to not use the sqldatasource in ASP.net, but the AccessDataSource control instead. It's an outdated control, but then again you're using Access 2003 or less when it comes to MDB files. Therefore let ASP.net handle the connection to your Access database and viola! Problem solved. I only get the above errors when I tried to do all this using sqldatasource, which is assumed by Microsoft to be the control of choice from most databases these days.Anonymous
November 16, 2015
Was facing this issue many times without any clear idea. This explained the exact reason. Thanks for the article.Anonymous
December 05, 2015
I started my SSMS by an Administrator account and then grant All Accesses for it on the corresponding temp folder(sample : 'C:UsersMSSQLSERVERAppDataLocalTemp') under the account(NT ServiceMSSQLSERVER) which is used to start the SQLServer service .However unfortunately it still raised the above error when I executed the below test scripts . My OS is 64-bit . Scripts:SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;imex=1;Database=D:test.xlsx', 'SELECT * FROM [Sheet3$]')