I have the following configuration...
- ASP.Net 4.0 Web Application (AppServer)
- SQL Server 2005 (SQL2005)
- SQL Server 2000 (SQL2000)
I have a linked query which links from SQL2005 to SQL2000. The security of the link ...
- has no user mappings
- is set to "be made using this security context" and defines an SQL Server login that should be used (and exists) on the SQL2000 machine.
I understand that when the link is established, all operations performed on the SQL2000 machine will be done under this specified login. Additionally, the SQL2005 machine will try to login to the SQL2000 machine using this login.
When my web app calls the stored proc. on the SQL2005 machine (that links to SQL2000) a strange thing happens. I get an error...
Login failed for domain\machine_name
What is happening is that the link is trying to connect from SQL2005 to SQL2000 not using the specific login I have defined but actually using the machine name of the web server. It gets this name from the App Pool identity in IIS. I have proved this by updating the app pool to run the app as a registered domain user, i then gave that domain user permissions for the DB on SQL2000 and it worked fine.
So why is the link being made with the web servers machine credentials and not with the specific SQL login I have asked it to use?
