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.
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.".
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:
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:
.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:
.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.
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:
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.
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.
"SQL Server 2005 Books Online (September 2007) - OPENROWSET (Transact-SQL)"
"SQL Server 2005 Books Online (September 2007) - Ad Hoc Distributed Queries Option"
"FileMon for Windows"
Excellent, really excellent - got me out of a bind. Thank you.