Optimizing SQL Server and IIS Security and Connectivity

Here's a summary of steps you can take to optimize SQL Server security and connectivity.

1. Use Windows-only authentication with SQL Server.
2. Use trusted connections instead of strings that pass SQL Server usernames and passwords.
3. Put the connection objects in DLLs and put them in Microsoft Transaction Server (MTS).
4. Set your code to use OLE DB instead of ODBC if you're using ADO. With ADO, ODBC calls OLE DB, so by using OLE DB directly, you improve performance by eliminating a processing layer.
5. Use TCP/IP between your IIS and SQL Server machines, not the default Named Pipes, if IIS and SQL Server are on separate servers. As Microsoft article "PRB: 80004005 ConnectionOpen (CreateFile()) Error Accessing SQL" at http://support.microsoft.com/support/kb/articles/q175/6/71.asp states, "When Named Pipes are used to access the SQL Server, IIS tries to impersonate the authenticated user, but it does not have the ability to prove its identity."
6. Put your connections and stored procedure calls into Visual Basic (VB) code DLLs, install them in MTS (which will automatically pool connections for you), and create server objects in VBScript to use the connections.
7. Ask for the correct Microsoft department if you need help using ADO-based code to talk to SQL Server. Microsoft Technical Support not only has IIS and SQL Server experts; it also has ADO-to-SQL Server experts.