How to add windows authentication sql server connection string in Web Config?

I am currently using Entity Framework in my ASP.NET MVC web application, I would like to use windows authentication in SQL Server string, so how can do it?

Currently, I have web.Config configuration using username/password of SQL Server in the connection string as below

<add name="BlogSearchManagementContext" connectionString="metadata=res://*/BlogSearchManagement.csdl|res://*/BlogSearchManagement.ssdl|res://*/BlogSearchManagement.msl;provider=System.Data.SqlClient;provider connection string=&quot;,1433;initial catalog=testDBName;persist security info=True;user id=userid;password=password;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />


Asked by:- neena
: 6259 At:- 11/10/2021 11:45:08 AM
C# sql server connection string windows authentication sql server connection string

2 Answers
profileImage Answered by:- vikas_jk

You need to remove username/password and replace it with Integrated Security=SSPI; in your connection string, to use windows authentication, so considering your above example, it will look like below

    <add name="BlogSearchManagementContext" connectionString="metadata=res://*/BlogSearchManagement.csdl|res://*/BlogSearchManagement.ssdl|res://*/BlogSearchManagement.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DESKTOP-1PM1CJ9\SQLEXPRESS2;
initial catalog=dbName;
persist security info=True;
Integrated Security=SSPI;
MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

For .NET Core

 "ConnectionStrings": {
    "DefaultConnection": "Server=SQLServer\\Instance;Database=MYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

You can also check all type of SQL Server Connection String Example.

You can also make Integrated Security = true in the above code of Web.Config in connection string.

Setting Integrated Security = true means basically you want to reach database via Windows authentication, if you set this field false Windows authentication will not work.

To make it work with all other database, you may need to change it accordingly:

  • For OleDb it is Integrated Security=SSPI;
  • For Odbc it is Trusted_Connection=yes;
  • For OracleClient it is Integrated Security=yes;

That's it, hope it helps.

At:- 11/10/2021 12:31:16 PM
Thanks for the detailed answer, yes making integrated security: SSPI, it worked. 0
By : neena - at :- 11/10/2021 12:33:21 PM

profileImage Answered by:- Vinnu

You don't need Persist Security also, so here shorter version of Windows Authentication SQL Server Connection string in .NET/Web.Config

<add name="DBConnection"
             connectionString="data source=SQLSERVER\MYINSTANCE;
             Initial Catalog=MyDB;Integrated Security=SSPI;"
             providerName="System.Data.SqlClient" />

in ASP.NET Core Appsettings.JSON

 "ConnectionStrings": {
    "DefaultConnection": "Server=SQLServer\\Instance;Database=MYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

and for JDBC client Windows Authentication connection string can be

String url ="jdbc:sqlserver://PC01\instance01;databaseName=MYDB;integratedSecurity=true";

in Python

conn_str = (
    r'Driver=SQL Server;'
cnxn = pyodbc.connect(conn_str)

Using PHP

/* Specify the server and connection string attributes. */  
$serverName = "(local)";  
$connectionInfo = array( "Database"=>"AdventureWorks");  
/* Connect using Windows Authentication. */  
$conn = sqlsrv_connect( $serverName, $connectionInfo);  
if( $conn === false )  
     echo "Unable to connect.</br>";  
     die( print_r( sqlsrv_errors(), true));  

Hope it helps.

At:- 6/22/2022 6:59:12 AM Updated at:- 6/22/2022 7:10:12 AM

Login/Register to answer
Register directly by posting answer/details

Full Name *

Email *

By posting your answer you agree on privacy policy & terms of use