Windows Security: using integrated security in connection string.

Q: My sysadmin disallow the use of standard SQL security in the connection string.. what can I do to make ASPPlayground use Trusted Connection?

A: It is a very easy task. Please follow the instruction below carefully (the following technique applies only to those who run both SQL and Web servers on the same machine):

• For the ASP versions:
  • Open Masterinclude.asp
  • Look for this line and make sure it reads: 'datastore = "Provider=sqloledb;Data Source="&dbServer&";Initial Catalog="&dbName&";Integrated Security=SSPI;" , and remove the comment in front of it
• For the ASP.NET versions
  • For version 2.5.5
    
    • Open Masterinclude.aspx
    • Look for this line and make sure it reads: 'datastore = "Integrated Security=SSPI; initial catalog=" & dbName & "; Data source=" & dbServer & "; persist security info=False; Trusted_Connection=Yes" , and remove the comment in front of it
  • For version 3.x
    • Open web.config
    • Make sure you enter the correct format of connection string into the "ForumSoftwareConnection" connection string. The correct format is:
      Integrated Security=SSPI; initial catalog=dbName; Data source=dbServer; persist security info=False; Trusted_Connection=Yes
  • Open web.config
  • Add the follwing in between <system.web></system.web> (this will make ASP.NET to run under the IUSR account)
    <system.web>
    ...
    <authentication mode = "windows" />
    ...
    <identity impersonate="true" />
    ...
    </system.web>

Finally,

• SQL 2000 users (for version 2.x users)
  • use Enterprise Manager, and add your IUSR account to the "Logins" section
• SQL 2005 users (for version 2.x or 3.x users)
  • use SQL Management Studio, and add your IUSR account to the "Users" section

Do not forget to set the correct permission for the IUSR account to access the database - you can simply add the IUSR account into the "db_owner" standard role if you want to use all features of the software. You can tighten the security by giving the IUSR account db_datareader + db_datawrite (BOTH, not either or). Note that with this lesser security privilege you won't be able to upgrade your db to a newer version, but you can use it for regular forum operations just fine.

NOTE: It is much more complicated to use Trusted Connection (Windows Authentication) than to use the plain SQL Authentication, and the above is just a suggestion and you should review security settings carefully yourself. Refer to this document for other ways to accomplish the same thing in ASP.NET