CODEWITHSUNDEEP
sqlsql-serveriis-7.5credentials
Aaron Newman
Aaron Newman

Reputation: 626

SQL Server trusted connection has wrong user in IIS, but SSMS is OK

I am using VS 2012, connecting to SQL Server 2008 host using windows credentials. I can connect fine from Management Studio, but when I run a web application (IIS 7.5 windows 7 ASP.NET 4.0 integrated) and my application tries to connect, I get this:

Login failed for user 'MyDomain\MyMachineName$'.

For some reason IIS App pool using Network Identity is not using MyUserName but my machine name instead. My connection string is:

<connectionStrings>
    <clear />
    <add name="Assignment" 
         connectionString="Data Source=a.b.c.d;Initial Catalog=My_Db;Trusted_Connection=true;Max Pool Size=200;Pooling=True;" />
</connectionStrings>

I should mention this is a new (to me) computer. I looked in credential manager and there is nothing there. Any ideas why IIS and my web app using the wrong thing?

Upvotes: 4

Views: 2457

Answers (1)

Jim
Jim

Reputation: 6881

IIS is actually doing exactly what it was designed to do when you run your app pool under the Network Service identity.

Using the Network Service account in a domain environment has a great benefit. Worker process running as Network Service access the network as the machine account. Machine accounts are generated when a machine is joined to a domain. They look like this:

<domainname>\<machinename>$,

For example: mydomain\machine1$

The nice thing about this is that network resources like file shares or SQL Server databases can be ACLed to allow this machine account access.

The quote above is from the following documentation...

http://www.iis.net/learn/manage/configuring-security/application-pool-identities

Hopefully this answers the question of why it isn't working the way you expect.

As far as how to make it work the way you want, I think this article should answer that question, and if not, some basic google searching should come up with quite a bit of information on how to properly configure your environment for what you're trying to do.

https://gilesey.wordpress.com/2013/05/11/allowing-iis-7-5-applications-to-communicate-to-sql-server-via-windows-authentication/

Upvotes: 2

Related Questions