CODEWITHSUNDEEP
c#asp.net.netsql-serversql-server-native-client
vmb
vmb

Reputation: 3028

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

Upvotes: 4

Views: 14298

Answers (7)

Hatim Ranapurwala
Hatim Ranapurwala

Reputation: 113

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>

Upvotes: 1

volkan
volkan

Reputation: 103

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

Upvotes: 0

user9375338
user9375338

Reputation: 174

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

Ref: MSDN

Upvotes: 1

Mark Vance
Mark Vance

Reputation: 166

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>

Upvotes: 0

Jes&#250;s L&#243;pez
Jes&#250;s L&#243;pez

Reputation: 9221

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:

Upvotes: 2

user1773603
user1773603

Reputation:

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

Upvotes: 0

Mark Wagoner
Mark Wagoner

Reputation: 1769

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.

Upvotes: 3

Related Questions