Problem authenticating with SQL server 2008 server from IIS 6 server

Question

I have 3 seperate physical servers:

• Old Webserver (IIS6; Windows Server 2003)
• New Webserver (IIS7; Windows Server 2008)
• DB Server (SQL 2008; Windows Server 2008)

These are all non-domain standalone machines.

The sites on the new webserver connect to the db server for their data. I accomplished this by setting up a local user on each machine and the IIS 7 sites are in an app pool that has it's identity set to the created local user account. I am using windows authentication to connect to the db server.

Now, I am trying to migrate databases off of the old web server onto our db server and reconfigure the web sites to connect to the db server for their data.

So, I created the local user account on the old webserver to match the account on the db server, set up a new app pool with identity set to the local user account. The site is running, when the site goes to connect to the database server I get the error:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

Please help.

Answer 1

You will not be able to use Pass-Through authentication in this fashion with SQL Server. That works with file shares and even authenticating people via web apps, but SQL Server itself treats the local machine like a DOMAIN and accounts with the same username/password are not the same across machines.

Your options are:

1. Use SQL Server Integrated Authentication
2. Move the machines to a domain and use a service account

Your best bet is to go with SQL Server Integrated Authentication.

Answer 2

Confirm that the application is configured to use the newly created app pool.