Wednesday, December 7, 2005
This discussion briefly describes how to set up your IIS 5.0 Web Server to work with Microsoft SQL Server 2000 in a Domain or Trusted environment, when IIS and SQL are on different servers.
Sometimes (usually late at night, and you have only a few precious hours to get the clients web app and database online...), you may get the following error message while attempting to access the SQL Server:
Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection
The user name may be (null), or some user name you've specified. You receive this error message even though on the SQL Server you've selected Mixed Mode Authentication! What is going on??!?
This generally happens when IIS and SQL are on seperate servers, and you are using domains or active directory. And, this happens with either ASP or ASP.NET. Also, in cases where you have your web server on the DMZ of your firewall and the SQL Server on the trusted or private zone, this problem can be experienced.
It seems there is just no trust in the world of the Internet.... I wonder why?
Now, in .NET you could just do the following in your web.config:
<identity
impersonate="true"
userName="DomainName\UserName"
password="password" />
but, this defeats the purpose of the security in the first place, and it exposes the password. And, for those that still have some legacy and production systems still running in good old ASP (and who doesn't?), there is no "simple fix".
The best practice is to set up both servers so that IIS authenticates properly to, and is therefore trusted by, the SQL Server. Below are basic steps to do this. I'm assuming you have a good working knowledge of the Windows operating system, IIS, and SQL Server - although, this is by no means a really technical thing to do. I'll briefly address firewall, IIS and the SQL Server caveats.
NOTE: IWAM_ and IUSR_ in this discussion refer to the IWAM_[ComputerNameHere] and IUSR_[ComputerNameHere accounts, unique to your server install.
Firewall (optional) considerations:
Depending on your firewall make/model, if you are using different zones where the IIS and SQL servers sit, you'll need to setup SQL rules for traffic between the two servers. The best method is to add a set of rules that allow the IIS server and the SQL Server specifically to talk - not ALL servers on either side. Doing so tightens down the exposure of the SQL Server to other servers in the DMZ.
If your firewall does not have a built-in rule, the following ports/protocols are used. You should be able to whip up a rule in no time...:
SQL Server - port 1433 UDP and port 1433 TCP, ignore client port.
SQL Server Monitor / Listener Service - port 1434 UDP and port 1433 TCP, ignore client port.
Netbios - port 137 UDP, ignore client port.
NOTE: Port 1434 is really important - it's used to locate named instances and other SQL "browsing". Port 1434 cannot be changed - it's built-in to use that port, whereas port 1433 is the default port SQL runs on, and it can be changed based on your installation. Verify that port 1433 is being used (and it will be in probably 99% of the installs) and setup your rules accordingly.
SECOND NOTE: Port 137 may or may not be important, depending on the version of your Windows server(s), and whether or not you have the latest Service Pack or MDAC installed. If you've recently updated your Service Pack or MDAC, and now your system is so secure that you get the following message (or similar)...
80004005 - MS ODBC SQL Server driver cannot initialize SSPI package
... then the problem is in Netbios. This usually occurs if you have the SQL Server on your trusted network, and your web server on the DMZ, and you are running Domains or Active Directory.. For security reasons, you should only need to setup Netbios port for traffic between the Trusted and the DMZ specifically to the IP addresses of your Web and SQL Server.
If your application complains that it can't connect to the database server, especially if you notice that it pauses for some time, or appears to hang, prior to throwing the error, then check your firewall configuration. Usually you'll get the following message:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/MyPerfectScript.asp, line 21
This problem is almost always one of the following: Incorrect server name or IP address, dns lookup failed on the server name, the server isn't running, the network is down, or the route to the server isn't working (eg: firewall or some other network issue).
IIS Server considerations:
IIS uses two accounts to run both itself and "out-of-process" (ASP, ASP.NET, COM, etc.). Normally, you will never know the passwords to these accounts as they are created by the installation. For the IIS server to be trusted, you will need to either change the passwords to something you know, or create new accounts and use those accounts to run the IIS processes. For this discussion, we're taking the easy route and just changing the IIS passwords.
1. Using Computer Management / Local Users and Groups, set the IUSR_ and IWAM_ user passwords to something that you know (or don't know, just be sure to remember what you set it as).
2. While you are in Computer Management, scroll down to Internet Information Services, and right click on it then choose properties. Make sure that WWW Service is in the drop down box and click Edit. Select the Directory Security tab. Under Anonymous access... click Edit. If you will be having anonymous user's access the site (which most sites will), make sure the Anonymous access is checked, and click Edit. Uncheck the Allow IIS to control password, and enter the new password. Click Ok, and confirm the password. Close all windows and exit out of Computer Management. Phew! Take a moment to catch your breath....
3. Click Start > Run, and type in cmd and click Ok to get to a command prompt (or just click your command prompt shortcut for those shortcut lovers out there) From the command prompt, go to the folder C:\inetpub\adminscripts and set the IWAM_ password (include the double quotes) by doing:
adsutil set w3svc/WAMUserPass "the_new_password_here"
If adsutil is not in this folder, or the folder doesn't exist, search your drive to find it - this is the default install location, but your location may vary.
4. Restart IIS.
The IWAM_ account is used to run scripting in medium or high isolation application pooling. You'll need to resync the password for this as well. Don't ask me why adsutil doesn't do it, it just doesn't, so you have to do the following:
5. Click Start > Programs > Administrative Tools > Component Services. Expand Component Services > Computers > My Computer > COM+ Applications. Right click on IIS Out-Of-Process Pooled Applications and choose properties. On the Identity tab, enter the password and confirm. Close the window and exit out of Component Services.
Your IIS and .ASP/.ASPX pages should still work - you'll want to test just basic scripting at this point - don't try to connect to the database server yet. If you somehow get the IWAM_ account really messed up, or out of sync, refer to this MS Knowledgebase article:
http://support.microsoft.com/kb/296851/EN-US/
Usually you'll get the following error message when you try to run anything that requires scripting if you've messed something up:
Server Application Error. The server has encountered an error while loading an application during the processing of your request. Please refer to the event log for more detail information. Please contact the server administrator for assistance
SQL Server Considerations:
Hang on, we're almost done.... You just need to setup the IIS accounts on the SQL Server....
1. Using Computer Management > Local Users and Groups, add the IWAM_ and IUSR_ accounts using the passwords you used on the IIS server - uncheck User must change... and check Password never expires. I know, security, yada, yada, yada - depending on your local policy, set to what you are comfortable with. Technically, these accounts should never change the password... right? And remember, these account names are the names that you have on your IIS server.
2. Launch SQL Enterprise Manager, open the server and expand to security / logins. Right click on Logins and choose "New Login". Add both the IWAM_ and IUSR_ accounts using the "3-dot" lookup button.
3. Choose the appropriate Database access, and server roles, etc. Apply / Ok / Close.
That's it!
For your classic ASP, the DSN should appear something similar to the following:
Driver={SQL Server};
Server=sqlserver_ip_address_or_fqdn_here;
Database=your_db_name_here;
Trusted_Connection=Yes;
Note that you will not need to supply a user ID or password as the application scripting is running under the IIS IWAM_ user context.
At this point, you should be able to run your ASP and ASP.NET applications AND access the database. Click here for a simple ASP script to test your trusted database connection.
This is by no means an exhaustive research, but should provide you with the basic information needed to get your site up and running.
Enjoy!
BJ Williams
President / Chief Software Architect