Using Windows Authentication with Connection string in IIS to connect to SQL 2008 R2

Question

I'm am making a model first website using MVC4 on IIS7.

Currently my connection string looks like this:

<add name="VaultEntities"  
    connectionString="metadata=res://*/Models.VaultDB.csdl|res://*/Models.VaultDB.ssdl|res://*/Models.VaultDB.msl;provider=System.Data.SqlClient;
         provider connection string=&quot;
         data source=sqlserver\instance;
         initial catalog=vault;
         integrated security=True;
         multipleactiveresultsets=True;
         App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" />

The connection was auto generated by Visual Studio 2011 when I added the database through the Model First approach. The query works perfectly locally on my development machine. But it dosen't work on the IIS server.

My guess is that this is because my user account on my development machine has access to the SQL server and when IIS tries to access the SQL server its user account gets denied.

In a perfect world I would add a user on the SQL server and use

User Id=myUsername;Password=myPassword;

in my connection string, unfortunately I don't have that option.

Without going into details the dba's are...difficult...to work with.

So is there any way to use my AD account in the connection string to login to the SQL server... Or can I make IIS identify itself as me?

Yes, you could change the Application Pool Identity, which is running the website, to your account in IIS. — Fung
– Fung, Commented Nov 12, 2012 at 14:03

Joe Ratzer · Accepted Answer · 2012-11-12 14:03:46Z

6

Is there any way to use my AD account in the connection string to login to the SQL server... Or can I make IIS identify itself as me?

You can get the application pool to use any AD user you want.

I would not recommend using your own username though. How many times have you locked your username in the last 12 months, and what happens when you leave?

Create/use a service AD account, map it in the application pool, and get the DBAs to give DB access to that user, using trusted connection. Sorry, you'll have to talk to the DBAs! :-)

answered Nov 12, 2012 at 14:03

Joe Ratzer

18.6k3 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JensB Over a year ago

Solved by selecting the application pool and clicking on Advanced Settings in the right "Action" panel. Once there I edited the "Identity" to be same as my AD account. Has been working great for 30 minutes or so ;)

Joe Ratzer Over a year ago

Glad you've got it working! Hopefully you can improve it later. And hopefully you get some nicer DBAs! :-)

Johnny_D · Accepted Answer · 2012-11-12 14:17:10Z

3

Ok, there is two options. 1. Either you change you application pool identity to one which has AD access to database, this can be done also by using impersonation section in web.config file of your website(Check this link). 2. Either you add sql account to your connection string. As I can see you are using EF connection string, it's a little bit different from standard sql connection string. But still has the same part with sql credentials, just replace integrated security = true to used id =...;password=.... That's all.

Hope this helps.

answered Nov 12, 2012 at 14:17

Johnny_D

4,6524 gold badges36 silver badges65 bronze badges

Collectives™ on Stack Overflow

Using Windows Authentication with Connection string in IIS to connect to SQL 2008 R2

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related