Setting up Linked Server on multiple servers with mulitple types of authentication

[Vikki McCormick]

In our environment we have several databases on multiple instances on a clustered environment.

SQLSERVER1 - contains Stored procedure
SQLSERVER2 - Linked Server mixed mode authentication
SQLSERVER3 - Lined Server Windows Authentication Only
SQLSERVER4 - Reporting Server using mixed mode

We are using NTLM authentication on the windows server.

A developer wrote a Stored Procedure that Hops from SQLSERVER1 to SQLSERVER2 in mixed mode, then later added a hop to SQLSERVER3 which is windows auth only using NTLM.

He then runs the report using the stored procedure from SQLSERVER4.

We run the stored procedure from SQLSERVER1 and it runs fine.

We use a Domain ID which as been setup with rights to all 4 servers. Nonetheless we are getting a Login Anonymous error when we tried to run the report from the report server.

I know when making a couple of hops it's better to use Kerberos, but we are only starting to convert our servers over now. Is there any other way to get the authentication to work through this process?

[ck9663]

The report in S4, did it call the stored proc in S1 that calls the S2 and then S3?


~~ CK

[Vikki McCormick]

Hi,

Thanks for the response. Yes exactly. It runs fine on S1. When it gets called in S4 Login failed for user 'NT AUTHORITY\ANONY MOUS LOGON'

This is because Kerberos is not setup. I just don't know if there is a work around until the entire environment is converted.

I should add they don't want me to use SQL Logins.

[NeoPa]

For this to work you'd need a setup where :

1. All servers are authenticated by the same authority (Domain).
2. The account used to run the original SP is recognised on all servers. This is most easily handled by the account being a member of the domain running the authority.
3. All the SPs involved in the process have permissions set up for the account used.

Is this currently the case?

[Vikki McCormick]

@NeoPa
Yes this is currently the case. I think we are going to take the easy way out and decide to move all databases to the same server/instance and thus removing the double hop. Solved.

[NeoPa]

Multiple servers and databases across domain boundaries can be managed, but it can very quickly get very complicated. I think you've taken the right course here.