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

4
votes

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

7
c#asp.net.netsql-serversql-server-native-client
Apart from "it already does" why do you ask? Is there some issue that you think will be fixed by changing the client? Most likely that won't help at all – Panagiotis Kanavos
Purpose is to make it compatible with TLS 1.2 – vmb
@vmb, the stack trace shows you are using Sqlclient rather than ODBC. Why use ODBC at all? Sqlclient supports TLS 1.2 and will perform better with .NET applications. – Dan Guzman
@Dan Guzman..can you pla help to rewrite the connection string – vmb
If i remove SQLNC11,is it connect through sqlnativeclient ??TLS 1.2 compatibility will work only if it connects through nativeclient – vmb

7 Answers

3
votes

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.

2
votes

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:

1
votes

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

Ref: MSDN

1
votes

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>
0
votes

According to MSDN use Database.OpenConnectionString Method (String, String):

var connectionString = "Data Source=.\\SQLExpress;Initial Catalog=SmallBakery;Integrated Security=True";

var providerName = "System.Data.SqlClient";

var db = Database.OpenConnectionString(connectionString, providerName);

var selectQueryString = "SELECT * FROM Product ORDER BY Name";

Add this setting in web.config:

<add name="ConnString" connectionString="Password=Secret;Persist Security Info=True;
     User ID=MyUserID;Initial Catalog=SmallBakery;Data Source=.\\SQLExpress" 
     providerName="System.Data.SqlClient" />
0
votes

you could test this in your web.config. This requires your username and password where shown. I'm not sure how you will be using the connection string though. This should work providing the account used has the access.

<?xml version="1.0" encoding="utf-8"?>
<configuration>

</configSections>
<connectionStrings>
    <add name="StagingConnect" connectionString="data source=AUBDSG01.AUYA.NET\INST1;initial catalog=Staging;persist security info=True;user id=user;password=password" providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
0
votes

if you are using SqlConnection use provider name "System.Data.SqlClient" but if you want to use another provider

SqlConnection

<add key="StagingConnect" 
         value="Integrated Security=True;Initial Catalog=SampleDatabase;Data Source=."/>

OleDbConnection

 <add key="StagingConnect2" 
         value="Provider=SQLNCLI11;Server=.;Database=SampleDatabase;
Trusted_Connection=yes;"/>

OdbcConnection

 <add key="StagingConnect3" 
         value="Driver={SQL Server Native Client 11.0};Server=.;
Database=SampleDatabase;Trusted_Connection=yes;"/>

i tested all of them and works fine.i hope this will help