To execute OPENROWSET commands successfully we need to enable Ad Hoc Distributed queries since by default it will be turned off
To configure this follow the following:
sp_configure ‘show advance option’, 1
GO
RECONFIGURE
Afer executing the above, now configure Ad Hoc Distributed queries
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE