2

I am converting my application to use MSOLEDBSQL from SQL Server Native Client 11.0

I have changed my connection string from "Driver={SQL Server Native Client 11.0}" to "Provider=MSOLEDBSQL" along with UID,PWD,Server,Database parameters.

But I could not able to connect to the Database and getting "[Microsoft][ODBC Driver Manager] Dialog failed"error message when using SQL_DRIVER_COMPLETE.

IM008   Dialog failed   The driver attempted to display its login dialog box and failed.

WindowHandle was a null pointer, and DriverCompletion was not SQL_DRIVER_NO_PROMPT.

Similarly, when used SQL_DRIVER_NOPROMPT, it throws an error "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

IM002   Data source not found and no default driver specified   (DM) The data source name specified in the connection string (InConnectionString) was not found in the system information, and there was no default driver specification.
(DM) ODBC data source and default driver information could not be found in the system information.

The above errors are returned as a result of a call to SQLDriverConnect().

I have downloaded and installed https://learn.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15 and could see the binaries in the system32 folder.

As per MS Docs, registry settings will be done as part of the installation.

All appropriate registry settings for the OLE DB Driver for SQL Server are made as part of the installation process.

  1. Should we do any additional registry configuration or other settings in order to use the MSOLEDBSQL driver ?

  2. When I use the Driver keyword as mentioned above it works for native clients. But if I used "Provider=SQLNCLI11" it fails. Any thoughts on this ?

I suspect the problem is related to loading the driver.

Appreciate it if anyone could help in fixing this issue.

Thanks in Advance.

Ash
  • 809
  • 1
  • 8
  • 14
  • 2
    Per the error prompt, you are connecting through ODBC, not OLE DB. While you can use the Microsoft ODBC driver to wrap an OLE DB driver, there is no need for this, as SQL Server also has a [native ODBC driver](https://learn.microsoft.com/sql/connect/odbc/download-odbc-driver-for-sql-server), which is the recommended approach. Either use OLE DB and connect with the `MSOLEDBSQL` driver, or use ODBC and connect with `{ODBC Driver 17 for SQL Server}`. – Jeroen Mostert Apr 09 '20 at 10:49
  • Thank you for the response Jeroen. Can you please advise how to confirm whether the OLE DB driver is not picked up and ODBC is selected. How to use OLE DB driver. Do we require any change in system registry or configuration ? – Ash Apr 10 '20 at 06:15
  • 1
    If you're using [`SqlDriverConnect`](https://learn.microsoft.com/sql/odbc/reference/syntax/sqldriverconnect-function), then you're using ODBC. If you were using OLE DB, you'd be using [`CDataSource`](https://learn.microsoft.com/cpp/data/oledb/cdatasource-class). These are completely different APIs for accessing databases; most applications support only one or the other. You cannot change between layers by tweaking system settings. Use the correct driver for the data access technology you are using. – Jeroen Mostert Apr 10 '20 at 06:58
  • @JeroenMostert actually going through the OLE DB might be necessary to fix some issues with varchar(MAX) which the direct ODBC driver does not handle properly. The only way I have been able to use new features like FOR JSON which returns varchar(MAX) via ODBC is through `Provider=MSDASQL;DRIVER={SQL Server}` – Charles Okwuagwu Oct 04 '20 at 11:34
  • @CharlesOkwuagwu: assuming you're using a recent version, it's likely the driver does handle it properly, but the client software isn't prepared to handle it. The behavior [was changed in 2012](https://docs.microsoft.com/sql/relational-databases/native-client/features/odbc-driver-behavior-change-when-handling-character-conversions) to (correctly) return the length as "unlimited", whereas previous versions returned a length, which could lead to other problems. Wrapping the OLE DB driver might "fix" that but introduce other issues for types without downlevel support (like `DATETIMEOFFSET`). – Jeroen Mostert Oct 04 '20 at 14:58
  • @JeroenMostert I have used `Driver={ODBC Driver 17 for SQL Server}` it cannot handle varchar(MAX) correctly. – Charles Okwuagwu Oct 04 '20 at 16:19

1 Answers1

0

I had the same error connecting to Azure SQL Server using C++.

Check your connection string.

DRIVER={ODBC Driver 18 for SQL Server};Server=${HOST},${PORT};DATABASE=${DATABASE};UID=${USER};PWD=${PASSWORD};Authentication=SqlPassword;Encrypt=No

I had to turn off Encryption for SqlPassword authentication to work.

Then use the SQL_DRIVER_COMPLETE flag and not SQL_DRIVER_NOPROMPT as advised in the Official Documentation here.

SQLDriverConnect(hDbc, nullptr, connStr, SQL_NTS, nullptr,
                                                            0, nullptr, SQL_DRIVER_COMPLETE);
Victor Mwenda
  • 1,677
  • 17
  • 16