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

Question

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 1

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:

• Use ODBC. (System.Data.Odbc)
• Use OLEDB. (System.Data.OleDb)

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

• TLS 1.2 support for Microsoft SQL Server

Solution 2

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.

Solution 3

Remove the Provider=SQLNCLI11 portion of your connection string -- it's not a supported property and is unnecessary.

Ref: MSDN

Solution 4

I think the problem with your connection is you are not able to connect remotely to SQL when you are using the windows authentication

You can try like this:

<appSettings>
<add key="StagingConnect" 
     value="data source=AUBDSG01.AUYA.NET\INST1;initial catalog=Staging;persist security info=True;user id=username;password=password;MultipleActiveResultSets=True"/></appSettings>

View more solutions

Comments

[vmb]

@Mark..i didn't get you.. ideally it should work with "add key" also..am i right ??..why it saying "provider" keyword is invalid ??

[Jesús López]

@vmb, because Provider is not a valid keyworkd for SqlClient. It is a valid keyworkd for Ole DB provider although. Please see msdn.microsoft.com/en-us/library/… and msdn.microsoft.com/en-us/library/…

[vmb]

I am tryying solution withou code change and only by configuration changes