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
Not sure exactly how you have it working before because my connection string doesn't go in <appSettings>
it goes in a separate <connectionStrings>
section. And providerName is an element, not part of the string itself.
Here is an example
<connectionStrings>
<clear />
<add name="xxx" providerName="System.Data.SqlClient" connectionString="Server=(local);Database=yyy;User=zzz;Password=123;MultipleActiveResultSets=True" />
</connectionStrings>
Hope this helps.