How To SQL Server with Integrated Security, IIS on Same Machine

MORE INFORMATION

When using SQL Server through a Trusted Connection, it may be necessary to host SQL Server and Internet Information Server on the same machine. This is commonly done with IIS 3.0 to work around limitations in accessing SQL Server through a Trusted Connection.

To allow access to SQL Server through a Trusted Connection, the SQL Server Login Security Mode needs to be set to either Mixed, or Windows NT Integrated. This can be changed from the Microsoft SQL Server Enterprise Manager,. From the Server menu, select SQL Server and Configure. The login settings are on the tab labeled Security Options.

To properly facilitate ActiveX Data Objects (ADO) connections from Active Server Pages (ASP), a System data source name (DSN) must be used.

Use the following steps to set up this DSN

1. On the machine running Windows NT 4.0 that hosts these two products, click the Start menu.
2. Select Settings.
3. Click Control Panel.
4. Double-click ODBC.
5. Select the System DSN tab from the tabbed dialog box.
6. Click Add.
7. Select the SQL Server ODBC driver from the list.
8. Name your new data source.
9. In the Server combo-box, type "(local)" without the quotes.
10. Check the "Trusted Connection" check box.
11. Change whatever additional settings are necessary for your DSN.
12. Click OK.

By setting the server to (local) we are preventing anyone using this DSN from looking out to the network for the SQL Server. By avoiding this step, you prevent the NTLM authentication process necessary to establish a trusted connection to SQL Server.

After setting up this DSN, you may access it as you would normally in ASP code.