Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How do configure a trusted connection between IIS 7 and SQL Server 2005?

My webapp was working fine with Windows Authentication enabled in IIS. Now, in order to solve a problem, we need to use a trusted connection. Unfortunately, enabling the trusted connection in the web.config broke the webapp. Oddly enough, when I run this application with trusted connection from my local dev machine (using the Cassini web server)

IIS (Windows Server 2008) is running on one machine. The database (SQL Server 2005 but could migrate to 2008) is running on another machine. We are on a Windows domain running AD. All traffic is within our own firewall - no public access. Beyond that, I can't provide much info but I can find it. We're very "compartmentalized" (we have server people, security people, oracle people, SQL Server people, etc.)

Thanks!

Update 02/14/2012 0902: The webapp is now functional (app no longer broken) but the main issue is still unresolved.

Now I have the app's application pool running as a domain account with permissions on the SQL Server box and IIS box. We were using this account to run the application but, and here's the problem, we need to log the real user name that made a change. When using the service account, the name of that service account appeared in the audit tables, making the auditing quite useless. So, not I'm at least running again.

The connection string in the web.config is using "Trusted_Connection=True", the appPool is using a domain account with access to both boxes, BUT when I make a change (logged in as me) the name of the service account (appPool identity) is still logged in the audit tables.

I also manually granted full permissions to the service account on the webapp folder.

What do I need to do in order to log my name, not the service account, in the audit tables? Everything I'm reading says I need to establish a trusted connection between the two servers.

share|improve this question
    
When you say it "broke" the web app, you need to be a whole lot more specific. What happend? What broke? Did you get an error message? What was it? What did the event log say? –  Mark Henderson Feb 14 '12 at 1:12

1 Answer 1

The user authenticating to the web server and the user account used by the web server to connect to MSSQL are not necessarily one and the same.

One solution is to add group permissions to MSSQL and make all eligible Windows users members of this group. This way, it is the group that grants access to MSSQL, but the actual user will still be the one making the connection.

share|improve this answer
    
My AD account is setup as a SQL user account and this doesn't work for me. I might not be understanding what you're saying... –  user1180652 Feb 14 '12 at 15:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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