October 14, 2003 at 6:04 pm
I have a web server that is running IIS 5.0 on win2k server sp4. This same machine also runs sql server 2000 sp3. On this sql server, I have a linked server set up to another sql server 2000 box (sp3 as well). I have a couple of web apps (ASP) that access databases on both machines. As you may have guessed, I can connect just fine to the databases on the local machine - the server running IIS as well as sql server. If I try to connect to the linked server I get a message telling me that "login failed for user nt authority\anonymous login". In IIS, I have enable anonymous login using a user account I will call 'webuser'. Now, on the linked server, I have mapped webuser to a user account with appropriate permissions on the linked server. In IIS, I also have checked "Integrated Windows Authentication". In my asp code, I use a connection string like the following:
cn.connectionstring = "provider=sqloledb;data source=linkedservername;initial catalog=mydbname;integrated security=SSPI"
For some reason, no matter what I try, I can not get around the message that login failed for anonymous user/nt authority. Any ideas?
Thank you,
Michael
Michael Weiss
October 15, 2003 at 2:45 am
We have a similar problem, and despite help from Microsoft, we are no nearer to solving the problem.
The following link may help:-
http://support.microsoft.com/default.aspx?scid=KB;EN-US;811889
It didn't in our case. We would get the 'nt authority\anonymous login' error intermittently, and in the end resorted to using SQL Server Authentication instead of Windows Authentication when using a linked server.
October 15, 2003 at 5:03 am
If you will open the linked server properties on the local SQL server you will see a tab labeled "Security", on this tab you will find multiple options of which the default when setup is "Be made without using a secuity context" when setting up a linked server. You have multiple options including impersonate, use the same security context as the logged in account and be made using a specific context. I personally would avoid the last and either map or use the currently logged in but the account on the other machine has to be valid.
October 15, 2003 at 1:10 pm
Thank you, Antares. I have tried your suggestion and still cannot get the authentication to work properly. Here is what I have configured and tested (to no avail) so far:
One sql server runs on the same server as IIS (this is a win2k server with sp4, running sql server 2000 w/sp3). The second database server is a win 2003 enterprise server running sql server 2000 w/sp3.
Each database server, let’s call them s1 and s2 with s2 being the win2003 server box, is set up as a linked server on the other box. In other words, s1 is a linked server on s2 and s2 is a linked server on s1. I have not mapped local and remote logins on the linked servers, but have checked the option “Be made using the login’s current security context” below the local/remote logins mapping area.
On server s1, I am running IIS 5.0 and have set the following parameters:
Anonymous access is enabled and is using a domain user account called WebUser. This WebUser account has appropriate permissions on all databases on both sql servers as needed.
Under Authenticated Access, both Basic Authentication and Integrated Windows Authentication are enabled(checked).
On server s2, I have granted the WebUser domain account the “Access this computer from the network” right as detailed in the MS Knowledge Base article 247931. Also, I should add that I have enabled through Active Directory the “Trust this computer for delegation” option for both servers. Also, my WebUser user account is trusted for delegation, as is my own account that I use for testing purposes as well.
Any other ideas? I am totally at a loss here...
Thank you,
Michael
PS - I can ping the servers okay so I think that DNS is working properly and the proper tokens, spn's etc are being created...or at least the name resolution is correct within the domain.
Michael Weiss
October 15, 2003 at 1:22 pm
Ok let's step back just for testing to see what I may be missing. Sorry I have not used the trusted accounted method myself so not sure what applies but let's make sure all is right with the SQL Servers interconnect.
First create a test user account on both servers with the access to some table to verify you get data.
Next setup the test user accounts by mapping the remote test account to the local one on the linked server setup on each.
Finally login with the test user on either server and query both the local and remote systems. Keep in mind when talking across the linked server you must use the 4 part name server.db.schemaowner.object.
If you have troubles outline what you did and let's go from there.
October 15, 2003 at 2:17 pm
I am able to set up a test user on server1 - the win2k server box. I can log into sql server on this machine using sql authentication and access table data without problems. This test user is a user on the local machine only.
I am not able to set authentication on the second server - the win2003 server - to sql and windows authentication. It keeps reverting back to windows authentication only. Consequently, I can not test the local test user account I set up on that box. Any ideas why I can not select sql server and windows authentication on this machine?
Michael Weiss
Michael Weiss
October 28, 2003 at 11:50 am
Do the 'servers' need to be linked? You could manage connections through your application running on IIS with ADO.
November 5, 2003 at 9:03 am
The Problem you are dealing here is call account delegation.
If you use NT Authentication to connect to your first box you have to use KERBEROS, make sure you set that account with Delegation privileges on AD and then try again.
If you use SQL Authentication to connect to the first box then all you need to do is map your login to the remote one on the Linked server
HTH
* Noel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply