Cannot Connect to Your Database

If you run your application or deploy it to a new machine and do not see any pages displayed, or see pages displayed, but no data displayed on the pages, then most likely your application’s security settings are not configured appropriately.

Microsoft .NET Application Security

Every Microsoft .NET application runs under the credentials of a specific user.  The specific user under which it is running determines which resources the application can access, including web pages, temporary files, and databases.  There are two factors that determine which user your application is running under.

1. Virtual Directory Security Settings:  The virtual directory can be set up to Allow Anonymous Access.  If Allow Anonymous Access is selected, then you must specify a user account and password.  The application will run under this user account specified and all resources will be accessed as this user.  By default, Iron Speed Designer creates the virtual directory where No Anonymous Access is selected.

2. Impersonation:  The impersonation settings are only used if No Anonymous Access is specified in the Virtual Directory Security Settings.  Impersonation means that the application runs based on the credentials of the user connecting to the application.  The application’s resources are available based on which user is connecting to the server.  The impersonation setting is specified in your application’s Web.config file and by default is set to True in applications created with Iron Speed Designer. 

How Anonymous Access and Impersonation Affect Application Deployment

No Anonymous Access (Recommended for Development Environment)

If No Anonymous Access is selected, then impersonation must be set to True in order for your application to run.  The application will run under the user connecting to the application.  Microsoft Internet Explorer passes the Windows credentials to the server, and the application runs under the passed credentials.

Other browsers like Mozilla Firefox, Opera or Netscape do not pass the Windows credentials to the server.  When accessing your application through non-Internet Explorer browsers, you will see an “Enter username and password” dialog to specify the user under which the application should run.

As different users access the application from different systems, their individual Windows credentials are passed to the server by Microsoft Internet Explorer.  The server uses these credentials to access the application folder, the temporary ASP.NET files folder as well as the database file or server.

Access to Application Files

1. Application Folder:  The user connecting to the application must have read and execute privileges on the Application Folder (e.g., C:\MyApp) and all files and sub-folders within this folder.  If the user does not have read and execute privileges, they will receive one of several error messages such as “HTTP 401.2: You are not authorized to view this page”, “CreateIdentity failed”, or other messages that indicate that files or directories cannot be created or accessed.

2. Microsoft .NET Framework Temporary Folder:  The user connecting to the application must have read, write and execute privileges on the Microsoft .NET Framework Temporary files folder (e.g., C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files) and all files and sub-folders within this folder.

Access to Database File or Server

1. Microsoft Access Database File and Folder:  If you are using Microsoft Access, the user connecting to the application must have read, write and execute privileges to both the file and the folder containing the Microsoft Access database.

2. Microsoft SQL Server using Windows Authentication, Database on Same Server:  If you are using Microsoft SQL Server database using Windows Authentication, then the user connecting to the server must have access to Microsoft SQL Server.   This is by far the most common reason why data is not displayed in the application.  To fix this problem, you can either:

• Set the Virtual Directory to Allow Anonymous Access;

• Set Microsoft SQL Server to use SQL Server Authentication; or

• Provide each possible user an account in Microsoft SQL Server.

3. Microsoft SQL Server using Windows Authentication, Database on Different Server:  Microsoft .NET Framework does not allow a “double hop” in transferring credentials, so this scenario will not work.  Double hop refers to passing credentials from the browser to the application server, and then again from the application server to the database server.  To fix this problem, either:

• Set the Virtual Directory to Allow Anonymous Access; or

• Set Microsoft SQL Server to use SQL Server Authentication.

4. Microsoft SQL Server using SQL Server Authentication:  If you are using Microsoft SQL Server with SQL Server Authentication, then the SQL Server user must have access to the database to read, write and update data in the database.  If you are continuing to see problems when using this scenario, check to make sure:

• The SQL Server user has access to the database in read, write and update mode.

• The SQL Server user’s password is correct.

The Microsoft SQL Server could be located on the same server or on a different server in this scenario, and either scenario should work as long as the SQL Server user name has appropriate permissions and the password is correct.

Iron Speed best practice recommends this approach for production systems when using Microsoft SQL Server in cases where No Anonymous Access is selected.

5. Oracle:  If you are using an Oracle database, then the Oracle user must have access to the database to read, write and update data in the database.  If you are continuing to see problems when using this scenario, check to make sure:

• The Oracle user has access to the database in read, write and update mode.

• The Oracle user’s password is correct.

The Oracle database could be located on the same server or on a different server in this scenario, and either scenario should work as long as the Oracle user name has appropriate permissions and the password is correct.

Iron Speed best practice recommends this approach for production systems when using Oracle in cases where No Anonymous Access is selected.

Allow Anonymous Access (Recommended for Production Environment)

Iron Speed best practices recommend this approach for the production environment.

If Allow Anonymous Access is selected, then one of the following users can be specified:

• IUSR_<MachineName>:  This is the default user created when Microsoft Internet Information Server (IIS) is installed.  If Allow Anonymous Access is selected, this user is the most likely default displayed by IIS.

• ASPNET:  This is the default ASPNET user created when Microsoft .NET Framework is installed.

• Specific User: Any valid user name and password combination can be specified.

The choice of which user is selected, and the resources they have access to, is critical in making sure your application runs properly and data is displayed in your application.  The following resources are accessed by the application and the selected user must have access to them:

Access to Application Files

1. Application Folder:  The selected user must have read and execute privileges on the Application Folder (e.g., C:\MyApp).   The permissions for all files and sub-folders in the Application Folder must be set to “inherit permissions”.

2. Microsoft .NET Framework Temporary Folder:  The selected user must have read, write and execute privileges on the Microsoft .NET Framework Temporary files folder (e.g., C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files).  The permissions for all files and sub-folders in the temporary folder must be set to “inherit permissions”.

Access to Database File or Server

1. Microsoft Access Database File and Folder:  If you are using Microsoft Access, the selected user must have read, write and execute privileges to both the file and the folder containing the Microsoft Access database.  Note that if your database is located in your application folder, then the selected user must have read, write and execute privileges to the application folder.  The permissions for all files and sub-folders in the database folder must be set to “inherit permissions”.

2. Microsoft SQL Server using Windows Authentication, Database on Same Server:  If you are using Microsoft SQL Server database using Windows Authentication, then the select user must have access to Microsoft SQL Server.   If the Virtual Directory is configured to use the IUSR_<MachineName> or ASPNET account, make sure that this user has access to Microsoft SQL Server.

3. Microsoft SQL Server using Windows Authentication, Database on Different Server:  The selected user must have access to Microsoft SQL Server.  This scenario will only work if the Virtual Directory is configured to use a Domain Account.  If the Virtual Directory is configured to use the IUSR_<MachineName> or ASPNET accounts, this scenario will not work since these accounts are local to the application server machine, and most likely do not have access to the database server machine.  To fix this problem, either:

• Set Microsoft SQL Server to use SQL Server Authentication; or

• Configure the Virtual Directory to use a Domain Account.

4. Microsoft SQL Server using SQL Server Authentication:  If you are using Microsoft SQL Server with SQL Server Authentication, then the SQL Server user must have access to the database to read, write and update data in the database.  If you are continuing to see problems when using this scenario, check to make sure:

• The SQL Server user has access to the database in read, write and update mode; or

• The SQL Server user’s password is correct.

The Microsoft SQL Server could be located on the same server or on a different server in this scenario, and either scenario should work as long as the SQL Server user name has appropriate permissions and the password is correct.

Iron Speed best practice recommends this approach for production systems.

5. Oracle:  If you are using an Oracle database, then the Oracle user must have access to the database to read, write and update data in the database.  If you are continuing to see problems when using this scenario, check to make sure:

• The Oracle user has access to the database in read, write and update mode.

• The Oracle user’s password is correct.

The Oracle database could be located on the same server or on a different server in this scenario, and either scenario should work as long as the Oracle user name has appropriate permissions and the password is correct.

Iron Speed best practice recommends this approach for production systems when using Oracle.

Remote Database Access through a Firewall

If you cannot connect to a remote Microsoft SQL Server, one possible explanation is that your firewall, on either the ISP side or your Microsoft SQL Server machine, is blocking Port 1433, the default port used by Microsoft SQL Server.  (Please note that you port 1433 is the default port; your Microsoft SQL Server may be configured to use a different port).

Please review these help links from Microsoft:

http://support.microsoft.com/default.aspx?scid=kb;en-us;287932

http://www.microsoft.com/windows2000/techinfo/howitworks/communications/networkbasics/tcpip_implement.asp

Microsoft SQL Server Connectivity Troubleshooting

See these helpful links for tips on troubleshooting Microsoft SQL Server connectivity issues.

http://support.microsoft.com/default.aspx?scid=827422

http://www.databasejournal.com/features/mssql/article.php/3341651