Connection string using Windows Authentication

Question

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?

Not sure whether you meant a generic windows account to be used across different peoples — rchacko, Nov 03 '20 at 05:35

score 274 · Answer 1 · edited Aug 04 '15 at 05:55

274

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>

edited Aug 04 '15 at 05:55

Sami

8,168
9
66
99

answered Jan 11 '13 at 01:13

heads5150

7,263
3
26
34

1

i know you can set a specific AD user to app pool (web app). can you do the same for windows app? – user384080 Jul 04 '14 at 02:10
7

`Persist Security Info` is probably not needed: http://stackoverflow.com/a/2010059/1869660 – Sphinxxx Nov 13 '14 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 Nov 27 '15 at 07:08

nzrytmn · Answer 2 · 2020-05-26T16:30:02.703

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.

score 30 · Answer 3 · answered Dec 13 '17 at 12:33

30

For the correct solution after many hours:

Open the configuration file
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" />

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

Hope this will help.

answered Dec 13 '17 at 12:33

AhmedBinNasser

2,735
1
24
24

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 Mar 05 '19 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: https://public.cyber.mil/stigs/downloads/ – duct_tape_coder Mar 24 '20 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 Feb 14 '22 at 11:39

score 12 · Answer 4 · answered Sep 26 '17 at 15:45

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

score 7 · Answer 5 · answered Jun 22 '22 at 07:21

7

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

answered Jun 22 '22 at 07:21

Vikas Lalwani

1,041
18
29

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 Oct 16 '22 at 06:56

Connection string using Windows Authentication

5 Answers5

Linked

Related