7399 error linked server

This is for my reference, as I encountered a variety of SQL error messages while trying to connect with provider. Other answers prescribe «try this, then this, then this». I appreciate the other answers, but I like to pair specific solutions with specific problems

Error

…provider did not give information…Cannot initialize data source object…

Error Numbers

7399, 7303

Error Detail

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. 
  The provider did not give any information about the error. 
Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object
  of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

File was open. Close it.

Credit

• https://stackoverflow.com/a/29369868/1175496

Error

Access denied…Cannot get the column information…

Error Numbers

7399, 7350

Error Detail

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. 
  Access denied.
Msg 7350, Level 16, State 2, Line 2 Cannot get the column information 
  from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

Give access

Credit

• https://stackoverflow.com/a/27509955/1175496

Error

No value given for one or more required parameters….Cannot execute the query …

Error Numbers

???, 7320

Error Detail

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 2
Cannot execute the query "select [Col A], [Col A] FROM　$Sheet" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". 

Solution

Column names might be wrong. Do [Col A] and [Col B] actually exist in your spreadsheet?

Error

«Unspecified error»…Cannot initialize data source object…

Error Numbers

???, 7303

Error Detail

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

Run SSMS as admin. See this question.

Other References

Other answers which suggest modifying properties. Not sure how modifying these two properties (checking them or unchecking them) would help.

• https://stackoverflow.com/a/31605038/1175496
• http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx
• https://social.technet.microsoft.com/Forums/lync/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess#3fcc14f4-420e-4544-be74-eea1e0e78462

[{«Product»:{«code»:»SSVT2J»,»label»:»Informix Tools»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Informix Client Software Development Kit (CSDK)»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»3.5;3.7;4.0″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Dear all,

I am having a 7399 error and cannot solve, can anyone please indicate how might be probably solve the problem:

Server: Windows server 2008 (32 bit)
Sql Server: Sql server 2008 R2 (32 bit)
Linked Server DB: Pervasive 10.0
Linked Method: Pervasive OLEDB Linked to Z: (z drive contains pervasive data file)
Error Message 7399
Others: SQL Server 2008 R2 is using Windows Authenication method to log in.  Windows can access the Z: path with another username and password

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

——————————
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
Program Location:

   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.RunQuery()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.Process()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.NavigableItemBuilderDataReader.get_PropertyNames()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItemWithQuery(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, String urnQuery, Boolean registerBuilder, Boolean registerBuiltItems)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItem(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.Build(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest request)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

===================================

The OLE DB provider «PervasiveOLEDB.10.0» for linked server «192.168.0.1» reported an error. Access denied.
Cannot obtain the required interface («IID_IDBSchemaRowset») from OLE DB provider «PervasiveOLEDB.10.0» for linked server «192.168.0.1». (.Net SqlClient Data Provider)

——————————
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

——————————
Server Name: SERVER1
Error Number: 7399
Severity: 16

I created a linked server, then I tried to query the table from the server. I received the following message:

OLE DB provider "SQLNCLI11" for linked server "DBServer" returned message "Invalid authorization specification".

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI11" for linked server "DBServer" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "DBServer".

—My local host login credential: WSSERVER2012Administrator

— under Server Objects—>Linked Servers —>DBServer (database AP displayed)

The code I used to created linked server:

EXEC sp_addlinkedserver   
   @server='DBServer', 
   @srvproduct='',
   @provider='SQLNCLI', 
   @datasrc='localhost'
go

The code I used to query the table

USE AP;
go

SELECT 
VendorName, 
CustLastName, 
CustFirstName,
VendorState AS State,
VendorCity AS City
FROM DBServer.AP.dbo.Vendors AS Vendors
    JOIN DBServer.ProductOrders.dbo.Customers AS Customers
    ON Vendors.VendorZipCode = Customers.CustZip
ORDER BY State, City

Please review the configuration setting for linked server and advise what configuration I need to change in order to query the able listed under the linked server. Thanks!

linked server general tab

linked server security tab

linked server server option tab

I did some search online and used the following steps to make the query work.
Connection was made from local host to the linked server.

1. drop linked server using sp_dropserver

exec sp_dropserver
@server = ‘DBServer’
go

1. Re-create the linked server using the same code

2. From Server Objects—>Linked Servers->right click on the ‘DBServer’ Choose properties—>under the Security tab select Be made using the login’s current security context

Sometime you may get the below error when you run a query on Excel or DBF file in SQL Server:


Msg 7399, Level 16, State 1, Line 2
The OLE DB provider «Microsoft.Jet.OLEDB.4.0» for linked server «» reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider «Microsoft.Jet.OLEDB.4.0» for linked server «».


This error generally comes only when you try to query from a remote machine. And this is something to do with the Security Context of the Linked Server.


Solution to this problem is very simple. Simply follow steps —
1. Open the SQL Server Management Studio and go to Server Objects and then to Linked Server.
2. Right click on your Linked Server Name, and click on Properties.
3. Go to Security Page. Here there are two options. You can try any one option.

• Select the option “Be made without using a security context” by clicking on radio button.

                                                  OR

• Select the last option “Be made using this security context ”.

When click on the radio button before “Be made using this security context ”, the“Remote login” and “With password” text boxes become active to be filled in.
Now in the “Remote login”, enter Admin as the login user. Leave the password text box as blank.
Now just click on OK, and now can run the query against your linked server without any error.