asked on

IIS SQL Linked Server Authentication problem

I have an IIS 7.0 server connecting to an SQL 2012 Database. On the SQL database I have added a number of linked servers and created a distributed partitioned view to consolidate data from the multiple linked servers. I have a common Windows account on the main server and the linked servers that is common to all (same password). Querying this view from SSMS on the SQL server works fine logged in as the common user. The application pool I am using uses this same user (also on the IIS server) to connect to the SQL server using Integrated Security. I have a simple query of SELECT SYSTEM_USER when run from IIS reports the common user. When trying to retrieve the data from the linked server view I get Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. instead of the data in the view.

Help appreciated

Kyle Abrahams, PMP

Make sure you have loaduserprofile = true in the application pool.

Also in your web.config add:

<authentication mode="Windows"/>
<identity impersonate="true"/>

Open in new window

charland

ASKER

Thanks for the response.

Unfortunately the website uses forms authentication with an SQL Provider.

Everything else works except the query that uses the view that joins the linked servers.

Any way to get this working? - thanks

Kyle Abrahams, PMP

Is the SQL server on a different database? If so you may be facing a double hop issue.

Please review this article for a better description of the problem and a possible solution:
http://www.sqlservercentral.com/articles/Double+Hop/76743/

charland

ASKER

Thanks for the additional pointers.

I think you are correct about the double hop and I think I need to do something with Kerberos and SPNs - None of the servers involved are in a domain - they are all Workgroup. Any further tips would be most welcome I am now trawling thru these articles to try to find a solution.

Register a Service Princiapal Name for Kerberos Connections

How to use Kerberos in SQL Server

Kyle Abrahams, PMP

Are you only reading data or do you need to write as well?

The easiest way to get around that would be to make all the calls go under the same context. That's done at the time of creation of the linked server.

charland

ASKER

Thanks again.

I only need to read data from the linked servers (20 of them) - what is the setting I need when creating them?

Thanks

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

charland

ASKER

Ok - I’ll give it a try with one of the 20 - Hoping I can provide Windows credentials with machinename/Mycommonuser

charland

ASKER

Thanks for the help. Although I couldn’t get this working with Windows authentication your pointers have allowed me to get to a solution using SQL Authentication which I have deployed and the web now can query the linked server views.

Kyle Abrahams, PMP

Yeah, wasn't sure if the windows authentication would be possible.

I think you can do it via scripting options, but as long as you're able to get at the tables under a context you should be good to go. Glad you got it working.