HI Team,
Recently we have migrated our Azure project with latest SDK(2.9), .Net Framework 4.7.2 and VS 2019.
When we debug the code locally ODBC connection is working fine. But once we hosted into azure cloud then exception is throwing when app is making sql connection through ODBC service. The problems seems to be SSL security issue in Web Instance role wise.
Exception:
-----------
System.Data.OleDb.OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error. Invalid connection string attribute at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource
1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource
1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open()
My ODBC SQL Connection method:
--------------------------------------
OleDbConnection oledbCon = new OleDbConnection("Provider=SQLOLEDB; Data Source=server_name; Initial Catalog=db_name; User Id=test; pwd=*****; Connect Timeout=300; Min Pool Size=350; Max Pool Size=3500");
if (oledbCon.State != ConnectionState.Open)
oledbCon.Open();
Please help us to solve this issue.
Welcome to the Microsoft Q&A (Preview) platform. Happy to answer your question.
Looks like you're using SQLOLEDB in your ODBC SQL Connection method. The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use for new development work. You need to use OLE DB Driver for SQL Server.
The OLE DB Driver for SQL Server is a stand-alone data access application programming interface (API), used for OLE DB, that was introduced in SQL Server 2005 (9.x). OLE DB Driver for SQL Server delivers the SQL OLE DB driver in one dynamic-link library (DLL). It also provides new functionality above and beyond that supplied by the Windows Data Access Components (Windows DAC, formerly Microsoft Data Access Components, or MDAC). The OLE DB Driver for SQL Server can be used to create new applications or enhance existing applications that need to take advantage of features introduced in SQL Server 2005 (9.x), such as multiple active result sets (MARS), user-defined data types (UDT), query notifications, snapshot isolation, and XML data type support.
You can download Microsoft OLE DB Driver for SQL Server
here
.
Also, please update your .Net framework to the
latest
version(4.8).
Hope that helps,
Grace
Hi, Thanks for your response. Yes after intalling the OLE DB Driver its working fine for me with the following connection string: "Provider=MSOLEDBSQL; Server=myServerName\theInstanceName; Database=myDataBase;
Trusted_Connection=yes;"
https://blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/announcing-the-new-release-of-ole-db-driver-for-sql-server/
But how to add this dll to my azure web app? Because we are using web role, each every VM how can I install this driver manually. So I need some solution or some reference to add dll reference through Azure SDK. How to add OLE DB driver as reference in my azure web app?
