SQL and IIS on Different Boxes Using Windows Authntication

Question

Post reply

SQL and IIS on Different Boxes Using Windows Authntication

htt

SSCertifiable

Points: 5242
More actions
March 14, 2005 at 3:43 pm

#102434

I am trying to setup connection from IIS to SQL using trusted connection. On my XP box I setup IIS running with local acct myPcName\IUSR_myPcName. On a SQL2K server running on a separate Win2K box I setup a local windows user named IUSR_myPcName. In SQL I create a windows authentication acct named mySqlName\IUSR_myPcName and grant this acct permission to read data. My PC and the SQL server are on same local domain. I am able to read SQL data from IIS using this setup. However when I use this same setup for an IIS server in DMZ I got error:
Microsoft OLE DB Provider for SQL Server error '80004005'
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I spent hours searching but can't solve this. There is a firewall between IIS and SQL. On IIS server I can read SQL using Query Analyzer so no port blocking issue at firewall. Please help.
Also myPC is XP, SQL server is SQL2K on Win2K Adv server. Both are in same domain in an AD and behind firewall. IIS is on Win2K std. server in a WORKGROUP in DMZ. I am able to access SQL if I change the connection string to using SQL authentication. I am thinking the firewall doesn't pass on the trusted authentication?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

You are right.

I know that ports need to be open for Windows Integrated Authentication. Here is the list what can be checked

139 - NetBios

441 - SSL

http://techrepublic.com.com/5100-6264-5054630.html#

lists also:

UDP 137 - UDP 137 (NetBIOS Name)
UDP 138 - UDP 138 (NetBIOS Netlogon and Browsing)
1024-65535/TCP - TCP 139 (NetBIOS Session)

You should be able to talk to your network support and they have logs that show dropped connections. Again, everything depends on the application. I was trying to make a third-party web app working and our network admin checked his logs and told me that one of the unusual ports was dropping connections. We contacted the vendor and they confirmed, that yes, we had to open that port.

One thing: do you really want that a web user from the outside will be able to access SQL Server inside ? It may be a major security issue. I would not recommend that. You will be better off having the database outside on the same server as IIS. You may even set up sort of replication using FTP

Yelena

Regards,Yelena Varsha

ferrell SSC Enthusiast Points: 155 More actions · Answer 2

(1) On the IIS Server in the DMZ - make sure that IIS is configured to use an Anonymous account that is in the DOMAIN, NOT the local Server that IIS is running on.

(2) On the SQL Server, give the same account the required permissions to access the database.

This does work because we use it here for > 500 web sites on 30 servers getting to the SQL Server.

htt SSCertifiable Points: 5242 More actions · Answer 3

One thing: do you really want that a web user from the outside will be able to access SQL Server inside ? It may be a major security issue. I would not recommend that. You will be better off having the database outside on the same server as IIS. You may even set up sort of replication using FTP

Yelena, I am not sure of what you are saying. Are you saying hosting IIS and SQL on same box in DMZ is safer than having IIS in DMZ and SQL behind firewall?

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Hi,

That is exactly what I am saying with understanding that SQL Server database outside the firewall will contain only public information or take care of securing sensitive information. In this case ONLY the information on in this database exposed if hacking happens. The rest of the network is safe.

Now imagine what could happen if you have 1433 open to the world. Especially with IIS being connected to it. Some mistake in setting the security or hacking attempt and your web user connects through IIS to SQL Server with admin rights, creates Windows administrative login and your network is exposed.

Again, I said "on the same server", but we both understand that having SQL Server on another machine is much safer if you have extra hardware.

How it is going with Windows Authentication? Did you resolve your issue?

Yelena

Regards,Yelena Varsha

htt SSCertifiable Points: 5242 More actions · Answer 5

Hmm, I don't see pros with that setup. IIS and SQL in DMZ, only public info on SQL! What happen when IIS needs to look up SSN or DOB? There must be a place for IIS to look.

Of course we don't have SQL port open to public. The firewall routes traffic to SQL only if it comes from IIS.

And we are not going to open ports at firewall for windows auth. So, wae are

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

The normal NetBIOS ports needed for Windows authentication are not needed to connect to SQL Server via Windows authentication. The client passes the credentials to SQL Server through 1433 or whatever port you have defined. You can actually see it in a packet trace.

However, in order to make this work, the IUSR account will need to be created locally on the SQL Server side. It can't be a domain account. BTW, this would be true, opening NetBIOS ports or not. SQL Server will only go to the Local Security Authority to authenticate the user if the user account isn't from a trusted domain. It will not kick the request back to a DC.

Yelena, as far as access across the firewall, this is typically a standard practice. Ideally the SQL Server and the IIS would sit in separate DMZs (not even in the same one) and any sensitive data would not be stored on the publically accessible SQL Server. However, reality often forces this solution out the window.

K. Brian Kelley
@kbriankelley

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

Brian,

Thanks.

I have our network support to take care of all firewall problems, I knew that we need 139 for Windows Authentication, I did not know that we did not need it when we use Integrated Authentication using SQL Integrated.

As for the standard practices, they are different in different companies.

As a DBA I guess, I would like to learn more what other people say on advantages and disadvantages of different IIS / SQL architectures. Please, post references here if you know of any good articles.

Yelena

Regards,Yelena Varsha

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

Last time I checked, it was not needed. As always, run the tests to verify. Most firewall admins balk at allowing any NetBIOS traffic, so I think you'd have heard about it if it was allowed.

As for articles, I'll cite mine from the November 2004 issue of SQL Server Standard.

http://www.sqlserverstandard.com/issue/200411.aspx

There's also information on the Microsoft and Cisco sites. As a matter of fact, a good general resource (although slightly dated) for web security is Cisco Press' Web Security Field Guide

K. Brian Kelley
@kbriankelley

htt SSCertifiable Points: 5242 More actions · Answer 9

I still don't have it work

Does it matter when the IIS isn't part of the AD?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 10

On the SQL Server itself you have a user account created that matches the account the web application runs under on the IIS box? Both username/password must match.

K. Brian Kelley
@kbriankelley

htt SSCertifiable Points: 5242 More actions · Answer 11

I got it works. Same configuration as in original post. And very same setup following http://support.microsoft.com/default.aspx/kb/247931 that didn't work few days ago but now.