If you are unable to create a new account, please email support@bspsoftware.com

 

Native DSN Testing Issue - Microsoft Native Client 2012 (SQL Server 2016 server)

Started by Cognos91, 01 Apr 2021 02:04:49 PM

Previous topic - Next topic

Cognos91

I am receiving the below error while trying to test the Native DSN connection in Cognos UI. We have Multisubnetfailover=Yes added as connection parameter. I am not sure if this has specifcially anything to do with Native Client and how it works with clustered instances; but the funny thing is it succeeds while connecting through one server while fails while connecting through the other server.

QE-DEF-0285 The logon failed.
QE-DEF-0323 The DSN(ODBC)/ServiceName is invalid. Either the DSN is missing or the host is inaccessible.
RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s).
UDA-SQL-0031 Unable to access the database.
Check that the connection parameters to the database are configured correctly.
For example, ensure that the data source connection contains the signon information, such as a password,
to connect to the database.
UDA-SQL-0532 Data Source is not accessible.
UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Unable to complete login process due to delay in opening server connection (SQLSTATE=08001,
SQLERRORCODE=0)UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Invalid connection string attribute (SQLSTATE=01S00, SQLERRORCODE=0)

I checked the Native Client versions on both Cognos servers, and these are the same - Microsoft SQL Server 2012 Native Client version 11.4.7001.
The JDBC connections work fine across both servers

Does anyone have any additional input on why connection through one server would pass where as through other server would fail?

Cognos91

Just to add more context:
The below link provides microsoft driver compatibility with AlwaysOn Cluster Instances.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-client-connectivity-sql-server?view=sql-server-ver15

It looks like SQL Server Native Client 11.0 does not support cluster instances.

Also, based on the below article, it looks like SQL Server Native Client will be deprecated. SQL Server OLE DB Driver is what will be supported.
https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15

Has anyone created a DSN with the MS OLE DB Driver (MSOLEDBSQL)? What should be the supporting type selected while creating the data srouce connection?