Search code examples
c#asp.net.netsql-serversql-server-native-client

Connection string for SQL Server native client in ASP.NET web.config

I want to connect to SQL Server 2012 using SQL Server native client from my ASP.NET application. Currently, I have one existing connection string connect using odbc and working fine.

<appSettings>
    <add key="StagingConnect" 
         value="Integrated Security=True;Initial Catalog=Staging;Data Source=AUBDSG01.AUYA.NET\INST1"/>
</appSettings>

When I tried as below, the code throws an exception

<add key="StagingConnect"  
     value="Provider=SQLNCLI11;Integrated Security=True;Initial Catalog=Staging;Data Source=AUBDSG01.AUYA.NET\INST1"/>

Exception:

System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown.

System.ArgumentException: Keyword not supported: 'provider'.
at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory

How can I modify this connection string so that it should connect through SQL Server native client 11

Solution

  • The first connection string is not an ODBC connection string, it is an SqlClient connection string.

    The second connection string is an Ole Db connection string the uses SQL Server Native Client. But your stack trace shows that you are using SqlClient to connect to SQL Server.

    You cannot use SqlClient and SQL Native client at the same time to connect to SQL Server. To use Native Client you have two options:

    You can use SqlClient and TLS1.2 as per the following Microsoft Support article: