Connection string for SQL Server Native Client in ASP.NET web.config

Question

I want to connect to SQL Server 2012 using the native SQL Server client from my ASP.NET application. Currently, I have one existing connection string 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 shown below, the code throws an exception

<add key="StagingConnect"  
     value="Provider=SQLNCLI11;Integrated Security=True;Initial Catalog=Staging;Data Source=AUBDSG01.AUYA.NET\INST1"/>

An exception:

System.Web.HttpUnhandledException (0x80004005): An exception of type "System.Web.HttpUnhandledException" was thrown.

System.ArgumentException: : "".
System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable, Boolean firstKey)
System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable, Boolean useOdbcRules)
System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
System.Data.ProviderBase.DbConnectionFactory

, SQL Server 11

Answer 1

, , <appSettings>, <connectionStrings>. providerName - , .

  <connectionStrings>
    <clear />
    <add name="xxx" providerName="System.Data.SqlClient" connectionString="Server=(local);Database=yyy;User=zzz;Password=123;MultipleActiveResultSets=True" />
  </connectionStrings>

, .

Answer 2

ODBC, SqlClient.

- Ole Db, SQL Server. , SqlClient SQL Server.

SqlClient SQL Native client SQL Server. , :

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

SqlClient TLS1.2 Microsoft:

• TLS 1.2 Microsoft SQL Server

Answer 3

Provider = SQLNCLI11 - ​​ .

: MSDN

Answer 4

, , SQL Windows

:

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

Answer 5

MSDN 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";

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

Answer 6

you can check this in your web.config. This requires your username and password. I'm not sure how you will use the connection string. This should work if the account has 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>

Answer 7

if you use SqlConnection, use the 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 everything and worked great. Hope this helps