Changing connection string from SQL Server to windows authentication

Question

I have to change from SQL Server authentication to Windows authentication:

My SQL Server authentication string is:

 <add name="GCDataContext" 
      connectionString="Data Source=111.78.152.64;Initial Catalog=GC;User Id=sa;Password=xxxx;Trusted_Connection=False;Persist Security Info=True;MultipleActiveResultSets=True" 
      providerName="System.Data.SqlClient" />

Changed to (not working)

 <add name="GCDataContext" 
      connectionString="Data Source=111.78.152.64;Initial Catalog=GC;Persist Security Info=True;Integrated Security=True;" 
      providerName="System.Data.SqlClient" />

Can anyone please help me on this?

Integrated Security=True is for Windows authentication, but the connection is not established.

Make a test UDL file to get connection string with reference to windows authentication. — Hassan Nisar
– Hassan Nisar, Commented Apr 22, 2014 at 6:37
Does the sql server have windows auth enabled? What account is the application running in? (hint: for asp.net, it probably isn't a useful one) - does that account have access to the server? and to the database? And most importantly of all: what exactly is the error message? — Marc Gravell
– Marc Gravell, Commented Apr 22, 2014 at 6:38
On one side you connect with sa, on the other with the currently connected windows user. Does that user have proper rights for accessing your database? — David Brabant
– David Brabant, Commented Apr 22, 2014 at 6:38
Oh, you've changed the MARS setting between the two; I'm not saying MARS is right or wrong, but you probably want to leave it the same for the moment, to reduce the number of moving parts. — Marc Gravell
– Marc Gravell, Commented Apr 22, 2014 at 6:39
@ David Brabant - For which user can i check the rights for accessing database? I got application installed on machine and database on other machine. Does the logged in user suppose to have a login created in - SECURITY -LOGINS ?? — user1282609
– user1282609, Commented Apr 22, 2014 at 6:41

sajjad ahmad · Accepted Answer · 2014-04-22 10:46:47Z

ASP.NET applications do not impersonate by default. As a result, when they use Windows authentication to connect to SQL Server, they use the Web application's process identity. With this approach, your front-end Web application authenticates and authorizes its users and then uses a trusted identity to access the database. The database trusts the application's identity and trusts the application to properly authenticate and authorize callers.

To connect to SQL Server using Windows authentication, perform the following steps:

Step 1. Configure a connection string. The connection string used with Windows authentication must include either the Trusted_Connection=Yes attribute, or the equivalent attribute Integrated Security=SSPI,

Step 2. Configure SQL Server security: You need to create a SQL Server login for your application's service account, and grant restricted permissions to access your database. You application's service account is usually either the Network Service account, which is the default account used to run ASP.NET application pools on Windows Server 2003 and IIS 6.0 or a custom service account.

For this do the following-

                 a).Create a SQL Server login for your application's account.
                 b).Map the login to a database user in the required database.
                 c). Provide appropriate permissions

ravidev · Accepted Answer · 2014-04-22 07:09:41Z

1

<add name="GCDataContext" 
      connectionString="Data Source=111.78.152.64;Initial Catalog=GC;Integrated Security=True" 
      providerName="System.Data.SqlClient" />

This should work.

You just need to remove Persist Security Info=True
Also you can copy my above code

answered Apr 22, 2014 at 7:09

ravidev

2,7486 gold badges28 silver badges42 bronze badges

Comments

Sachin CR · Accepted Answer · 2014-04-22 07:23:17Z

0

Remove : Persist Security Info=True; If it's not able to connect verify DB permission and privilege.

edited Apr 22, 2014 at 7:23

answered Apr 22, 2014 at 7:00

Sachin CR

215 bronze badges

Collectives™ on Stack Overflow

Changing connection string from SQL Server to windows authentication

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related