Openrowset fails with "network access was interruped"

A SQL Server openrowset query using ODBC to retrieve data from an Excel spreadsheet fails with the error:

"Your network access was interrupted. To continue close the database and then open it again."

Example Openrowset query (Excel 2010+):

SELECT *
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=C:\temp\test.xlsx;Uid=;pwd=;','SELECT * FROM [memberlist$]')

Solution:
  • Disable all network connections and try again. Only works if the frontend (e.g. Management Studio) and the backend (SQL Server database) are on the same machine
  • Change SQL Server service user to an NT account
Remarks:
  • In some cases, activating the network connections again will prevent the error from occurring
  • In some cases, changing the NT account to the "local service" account will prevent the error from occurring
  • The MSDASQL keyword indicates that openrowset will use the ODBC driver in stead of an OLE DB driver

No comments: