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

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, Feb 16 '18 at 10:51
@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, Feb 16 '18 at 12:58
@Dan Guzman..can you pla help to rewrite the connection string — vmb, Feb 17 '18 at 18:20
@vmb, remove the `Provider=SQLNCLI11;` and it is a valid Sqlclient connection string just like your original one. — Dan Guzman, Feb 17 '18 at 18:27
If i remove SQLNC11,is it connect through sqlnativeclient ??TLS 1.2 compatibility will work only if it connects through nativeclient — vmb, Feb 17 '18 at 18:29
@vmb, [SqlClient does support TLS 1.2](https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server) — Dan Guzman, Mar 01 '18 at 01:18

score 3 · Answer 1 · answered Feb 16 '18 at 12:27

3

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.

answered Feb 16 '18 at 12:27

Mark Wagoner

1,729
1
13
20

@Mark..i didn't get you.. ideally it should work with "add key" also..am i right ??..why it saying "provider" keyword is invalid ?? – vmb Feb 17 '18 at 18:24
@vmb, because Provider is not a valid keyworkd for SqlClient. It is a valid keyworkd for Ole DB provider although. Please see https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx and https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.connectionstring(v=vs.110).aspx – Jesús López Feb 27 '18 at 15:30

Jesús López · Accepted Answer · 2018-02-27T15:25:24.507

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

score 1 · Answer 3 · answered Mar 02 '18 at 17:55

1

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

Ref: MSDN

answered Mar 02 '18 at 17:55

user9375338

102
1
10

score 1 · Answer 4 · answered Mar 03 '18 at 10:42

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>

score 0 · Answer 5 · answered Feb 26 '18 at 15:20

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" />

I am tryying solution withou code change and only by configuration changes — vmb, Mar 21 '18 at 06:00

score 0 · Answer 6 · answered Feb 28 '18 at 16:09

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>

score 0 · Answer 7 · answered Mar 02 '18 at 18:16

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

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

7 Answers7