Authentication for XML Queries from remote IIS server
-
Hi All,
Hope this is the appropriate forum for this.
I'm working towards my MCDBA and in the process trying to set up one of my databases so I can do XML queries through an IIS server on another machine. If I set up IIS for XML locally I have no problems with authentication, (I'm using windows only authentication.) but as soon as I attempt to use separate machine as an IIS server I am running into authentication problems.
I get a 404 or 400--can't remember for sure now as I was doing this yesterday--error in my broswer and in the Event logs on the SQL Server 2000 machine I get a message that "null" user was denied access because it was not an authorized account. I can see why it happens but don't exactly know how to fix this.
Can someone point me toward some whitepapers or some good books on this subject? I've got the book "XML and SQL Server 2000" and it goes into how to set up local and remote access but it doesn't touch on any authentication issues on the database server at all for remote access. All the author covers is local access.
I'm guessing I have to set up access for IUSR or IWAM on the SQL Server. Is this correct? Or do I need to set up a way to pass the authentication on from the IIS server to the database server as the only access I'm allowing to the IIS server is AD integrated access through Windows Authentication?
-
Hi Freddy
As a starting point you might want to disallow anonymous access on the IIS thereby forcing an authentication of the user making requests to the IIS. This way, you can avoid being forced to use the IUSR_machine.
I'm not sure, but I think you will run into difficulties if you try to transfer this authentication of the user from the IIS to the SQL Server if it is running on a different machine.
In some similar cases, I have used a fixed sql server security login or in a single case a trusted connection (Windows)login in an intranet solution where the IIS resided on the same server as the SQL Server. This is not recommended for Internet solutions though because of security issues.
Mads Holm
-
Mads Holm,
Thanks for the reply.
A couple of things here that I think I might not have made clear in my first post judging from your answer.
I have two servers in my test lab with SQL Server on them. Both have SQL Server installed. One server has IIS installed. I can successfully send an XML query through the IIS server to the SQL Server that is local to that machine. However when I point the IIS configuration towards the SQL Server on the other machine I get the "null" user error in the event logs on that machine.
Both machines are part of the same AD domain. Both SQL Servers use Windows AD-integrated Authentication only. The IIS server also uses Windows AD-integrated authentication only. I can successfully access the SQL Server that is installed on the same machine as the IIS server through the IIS server. I just can't access the "remote", relatively speaking anyway as the only way it is remote is that it is on another machine, SQL Server though IIS.
Neither of these servers are exposed to the Internet. This is a test lab network only. I don't figure I'm quite up to the task of keeping an IIS Server exposed to the Internet secure as yet. I'm pretty knowledgeable about configuring IIS I'm just not up to the task of taking on the world as yet. Ya gotta know yer limitations.
-
Freddy
I think you are experiencing the problem of transferring the authentication from the IIS-machine to the (remote) SQL Server.
By default, IIS is using the IUSR_machine account which shouldn't be granted access to the SQL server. By disallowing anonymous access to the IIS, you would force it to use another account, but I don't think you can use this other account against the SQL Server unless you are using basic authentication (username and password sent in cleartext). I think this is the case, even both machines are in the same domain, but again, I'm not sure, and things might have changed since the last time worked with this.
To summarize, I still think you should use SQL server authentication, because this way you won't have to transfer the authentication through the IIS.
Mads Holm
-
Freedy
You might want to lookup Security Account Delegation..
Tony
-
Maudapa,
I think you're on to something here.
However, I am running a named instance of SQL Server not a default installation, and there are no instructions for adding spns for named instances of sql server in either BOL or the Win2K documentation. I've tried adding sqlsvc as both mssqlsvc/myserver.mydomain.local and mssql$instancename/myserver.mydomain.local. Both attempts were successfull as I got the correct feedback and then was able to see my changes by using the -l switch in setspn but but I still am getting the 'null' user error. Do you know the specific syntax for how to add a named instance of sql server as an spn?
I have added the http spn to my IIS server successfully.
BTW, thanks for pointing me to this utility. This is really handy and I hadn't seen it before.
-
Freddy
The following might also help
Knowledge Base Article - 319723
INF: SQL Server 2000 Kerberos support including SQL Server virtual servers on server clusters
Knowledge Base Article - 326985
HOW TO: Troubleshoot Kerberos-Related Issues in IIS
I've not implemented it myself yet but intend to in the next few weeks..
Tony
-
Freddy
Recently posted on Miceosoft site
A new white paper on how to troubleshoot Delegation Scenarios is
available at
While this document concentrates on troubleshooting all delegation
scenarios, it has detailed information related to IE -> IIS -> SQL scenario
and SQL Client -> SQL Server -> SQL Server (using Linked Server) scenario
and also general troubleshooting steps for Kerberos protocol.
Tony
-
Maudapa,
Thanks for the replies and the links. I'll look into them.
-
Maudapa,
The last link you posted was excellent. It solved all my problems.
Thanks for help.
It's not that difficult to do once you understand the steps. It was only a five step process once I read the article.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply