Problem authenticating with SQL server 2008 server from IIS 6 server
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.
Solution 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:
- Use SQL Server Integrated Authentication
- Move the machines to a domain and use a service account
Your best bet is to go with SQL Server Integrated Authentication.