Post reply

Authentication for XML Queries from remote IIS server

  • April 9, 2004 at 12:14 pm

    #82644

    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?    

  • April 9, 2004 at 12:48 pm

    #502337

    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

  • April 9, 2004 at 1:45 pm

    #502339

    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. 

  • April 9, 2004 at 2:53 pm

    #502346

    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

  • April 10, 2004 at 5:30 am

    #502363

    Freedy

    You might want to lookup Security Account Delegation..

    Tony

  • April 10, 2004 at 11:45 am

    #502366

    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.   

  • April 10, 2004 at 12:21 pm

    #502367

    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

  • April 10, 2004 at 2:04 pm

    #502370

    Freddy

    Recently posted on Miceosoft site

    A new white paper on how to troubleshoot Delegation Scenarios is

    available at

    http://www.microsoft.com/downloads/details.aspx?FamilyID=99b0f94f-e28a-4726-bffe-2f64ae2f59a2&displaylang=en

    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

  • April 11, 2004 at 2:26 am

    #502375

    Maudapa,

    Thanks for the replies and the links.  I'll look into them. 

  • April 11, 2004 at 6:31 pm

    #502378

    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