SQLTeam.com | Weblogs | Forums

Connecting to Linked Server in SQL Server database (Windows Authentication) from IIS

sql2012

#1

When I try to query the linked server outside of IIS, from console application everything works fine without any additional configuration.

  1. When I try to query the linked server from website hosted in IIS, I get the following error: Login failed for user 'DOMAIN\USER'. Obviously, I can "fix" this problem by adding that user to SQL Server, but why is it trying to access the SQL from user account and not from application's pool account? Why don't I get error Login failed for user 'IIS AppPool\MyApplicationPool'? Is there a way to force it to use application pool?

  2. Obviously, I can't access the Linked Server data without giving that user a read permissions on the folder. If I give permissions to Everyone - it works. If I give permissions to Users - it works...but which user is accessing the linked server data? Is it the MSSQL user? Is it the DOMAIN\USER?

  3. Is there a simple way to do the following: Force application in IIS to authenticate with IIS AppPool\MyApplicationPool instead of local account so that I can simply give the read permissions on the folder to that application pool.

  4. If I somehow manage to force it to use the app pool and configure the required permissions, did I bypass the double hop problem? Is there a need for Kerberos delegation?

Thank you in advance.


#2

Mostly the account under which your web application is running is totally depend upon IIS version (most likely network service in IIS 6/IIS 7) does not have access to your linked SQL server.

Have your web application uses an impersonated account and give restricted access to your linked sql server through impersonated account.

Please visit this hoping it will resolve your issue. http://www.iis.net/learn/extensions/database-manager/use-the-database-manager