212

I am creating a website, but in the database I use windows authentication.

I know that you use this for SQL authentication

<connectionStrings> 
    <add name="NorthwindContex" 
       connectionString="data source=localhost;
       initial catalog=northwind;persist security info=True; 
       user id=sa;password=P@ssw0rd" 
       providerName="System.Data.SqlClient" /> 
</connectionStrings>

How do I modify this to work with windows authentication?

Improve this question
1
  • Not sure whether you meant a generic windows account to be used across different peoples
    – rchacko
    Commented Nov 3, 2020 at 5:35

5 Answers 5

Reset to default
287

Replace the username and password with Integrated Security=SSPI;

So the connection string should be 

<connectionStrings> 
<add name="NorthwindContex" 
   connectionString="data source=localhost;
   initial catalog=northwind;persist security info=True; 
   Integrated Security=SSPI;" 
   providerName="System.Data.SqlClient" /> 
</connectionStrings>
Improve this answer
3
  • 1
    i know you can set a specific AD user to app pool (web app). can you do the same for windows app?
    – user384080
    Commented Jul 4, 2014 at 2:10
  • 9
    Persist Security Info is probably not needed: stackoverflow.com/a/2010059/1869660
    – Sphinxxx
    Commented Nov 13, 2014 at 23:46
  • @heads5150: Is it possible that there is no connection strings in my project? am i missing something. i have searched through my entire solution to find a connection string as above. i could not find any. The one that i founded was commented in the web release and web config.. i am using vs express 2013 with local db.
    – Vini
    Commented Nov 27, 2015 at 7:08
49

For connecting to a sql server database via Windows authentication basically needs which server you want to connect , what is your database name , Integrated Security info and provider name.

Basically this works: 

<connectionStrings>      
<add name="MyConnectionString"
         connectionString="data source=ServerName;
   Initial Catalog=DatabaseName;Integrated Security=True;"
         providerName="System.Data.SqlClient" />
</connectionStrings>

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

It is also working different according which provider you are using.

  • SqlClient both Integrated Security=true; or IntegratedSecurity=SSPI; is working.

  • OleDb it is Integrated Security=SSPI;

  • Odbc it is Trusted_Connection=yes;
  • OracleClient it is Integrated Security=yes;

Integrated Security=true throws an exception when used with the OleDb provider.

Improve this answer
31

For the correct solution after many hours:

  1. Open the configuration file
  2. Change the connection string with the following

<add name="umbracoDbDSN" connectionString="data source=YOUR_SERVER_NAME;database=nrc;Integrated Security=SSPI;persist security info=True;" providerName="System.Data.SqlClient" />

  1. Change the YOUR_SERVER_NAME with your current server name and save
  2. Open the IIS Manager
  3. Find the name of the application pool that the website or web application is using
  4. Right-click and choose Advanced settings
  5. From Advanced settings under Process Model change the Identity to Custom account and add your Server Admin details, please see the attached images:

enter image description here

Improve this answer
3
  • 4
    This solution worked for me, but I was wondering how does this change on identity impact the behavior of the application, in terms of security?
    – CesarB
    Commented Mar 5, 2019 at 14:14
  • All actions performed by the process will be run with the permissions/privileges of that account. Don't grant more permissions than needed. A dedicated service account would be advisable. Would recommend checking out the DISA IIS and Windows Server STIG as well: public.cyber.mil/stigs/downloads
    – duct_tape_coder
    Commented Mar 24, 2020 at 15:45
  • Solution works but just would like to add that when you try to set the Custom account, the account that you are trying to add should already be present in the Control panel > User accounts else you will get error (If you try to add an account that's not present in Control panel > User accounts). So first add the account in User accounts and then you will be able to set it in Application identity.
    – m_beta
    Commented Feb 14, 2022 at 11:39
12

This is shorter and works

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

Persist Security Info not needed

Improve this answer
9

If anyone comes looking for asp.net core, we will have to add connection string in appsettings.json

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

Source: add windows authentication sql server connection string

Improve this answer
1
  • 1
    TrustServerCertificate=true as well if you get "A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)" for Windows Login in .NET Core 6
    – divay pandey
    Commented Oct 16, 2022 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.