To troubleshoot database connection issues in Commerce Server 2007, use the following methods.
Examine the error message
Read the error message, and determine the database that has the connection problem. When you receive a database connection error message, the error message typically contains the database name that has a connection problem.
Check the connection strings in Commerce Server Manager
In Commerce Server Manager, check the connection strings.
For more information about how to check connection strings and how to change connection strings in Commerce Server Manager, click the following article number to view the article in the Microsoft Knowledge Base:
321619�
How to change connection strings to Commerce databases
Check the other connection strings
For a profile database, you must check two or more SQL connection strings. Then, you must check Site Resource reference strings. To do this, follow these steps:
- Open Commerce Server Manager, and then locate the Commerce site that you want to check.
- Expand Site Resource, and then locate Profiles(DatabaseName_SiteName) under Site Resource.
- Expand Global Resources, and then find the same name that exists under Site Resource.
- Right-click the global resource that you located in step 3, and then click Properties.
- Check and then edit the profile data store connection string, and then click OK.
- Expand the global resource that you located in step 3, expand Profile Catalog, and then expand Data Sources.
- For each data source that appears under Data Sources, right-click the data source, and then click Properties.
- Click the Partitions tab, select each partition name, and then click Edit to check and modify the data source connection string.
Check name resolution
Check the name resolution of the connection strings that you obtained in the "Check the connection strings in Commerce Server Manager" section and in the "Check the other connection strings" section. Then, test the connection string to determine why the connection string does not work. To do this, follow these steps:
- In the connection string, determine whether you are using the computer NetBIOS name, a SQL alias, a fully qualified domain name (FQDN), or an IP address.
- Determine how you resolve the name that you found in step 1. For example, you can use DNS or a local host file. Verify that the name resolution resource is available.
- Determine whether you can ping the instance of SQL Server by its name in the connection string. Determine whether the IP response from the ping is the same as the IP that is returned when you run the ipconfig command on the computer that is running the instance of SQL Server.
- Determine the port on which the instance of SQL Server is listening.
- Determine whether the connection string contains a port number.
If SQL Server is listening on port 1433, you do not have to specify the port number. Port 1433 is the default port for SQL Server. If SQL Server is not listening on port 1433, determine whether the port in the connection string matches the port on which SQL Server is listening. If the ports do not match, determine whether you use a SQL alias to resolve the port.
- If a SQL client tool is installed on the Web server, run the cliconfg command at a command prompt to determine whether the TCP/IP protocol is enabled and to determine the defined aliases.
- If a SQL client tool is installed on the Web server, follow these steps:
- In a text editor, create an empty text file.
- Save the empty text file as testConnection.udl.
- In Windows Explorer, double-click the file to open it.
- Add the SQL Server name in the connection string to test the connection string.
Check the firewall
Sometimes, the SQL Server and the Web server computer are separated by a firewall. If a firewall is installed between the Web server computers and the instances of SQL Server, determine whether the port on which SQL Server listens is open on the firewall.
If the instance of SQL Server is a clustered named instance and if a firewall exists between the Web server computer and the instance of SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
318432�
BUG: Cannot connect to a clustered named instance through a firewall
Determine whether the user account has the correct user rights
You must determine the user account that will access the databases and the user rights that this user must be granted.
The Commerce site is composed of the following elements:
- The Commerce front store Web application
- The Business Management Applications for the site
- The site resources that have databases for data storage
These applications must access the databases for data operations. After you verify that the instance of SQL Server and the database can be connected, you must determine whether the actual user account has the correct user rights to access the databases.
You must determine which accounts will access the databases.
By default, Commerce Server 2002 and Commerce Server 2007 use Windows authentication in all connection strings. By default, Commerce Server 2000 uses SQL authentication in all connection strings. If the connection string use SQL authentication, the SQL login is the account that will access the database.
The following table lists the accounts that access the databases.
Collapse this tableExpand this table
| Application | User account that accesses the database |
|---|
| Commerce Server 2007 business management applications, the Commerce Server 2007 front store Web application, and other Commerce Web services | The application pool identity
The user security control is controlled by the Windows Authorization Manager. After a user is authorized, the account to access the databases is the application pool identity. |
| A Commerce Server 2002 front store Web application that runs on Microsoft Internet Information Services (IIS) 5.0 and uses a Microsoft ASP.NET commerce Web application | The ASP.NET worker process identity |
| A Microsoft Active Server Pages (ASP) commerce Web application, an ASP virtual directory, or an ASP Web site | The anonymous user |
| The Commerce Server 2002 front store Web application on a computer that is running IIS 6.0 | The application pool identity |
Commerce Server 2002 business management applications use the Business Desk (BizDesk) application. By default, BizDesk uses Windows authentication on the IIS side, and the domain user who opens the BizDesk client tool is the user who accesses the databases. If the BizDesk client, the BizDesk Web application, and the instance of SQL Server are on different computers, you experience Kerberos issues.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
326985�
How to troubleshoot Kerberos-related issues in IIS
319723�
How to use Kerberos authentication in SQL Server
907272�
Kerberos authentication and troubleshooting delegation issues
To work around the Kerberos issues, use basic authentication on the BizDesk Web application. Or, use SQL authentication in the SQL connection strings.
If you use a CSAuthFilter filter in Commerce Server 2002, you must use SQL authentication in the MSCS_Admin database connection string.
For more information about run-time users or admin users and for more information about what minimum user permissions the users must have on databases, see the following topics in Commerce Server help:
- Commerce Server 2002
Deploying Commerce Server using Windows authentication - Commerce Server 2007
How to associate database accounts with database roles
Additionally, you can use the Microsoft Commerce Server 2007 Best Practices Analyzer to check connections and permissions on databases.
To obtain the Microsoft Commerce Server 2007 Best Practices Analyzer, visit the following Microsoft Web site:
Determine the applications that access the database
Use SQL Profiler to determine what applications are accessing the database. SQL Profiler is a powerful tool that can be used in to determine which applications, Web servers, and user accounts are accessing the database. SQL Profiler also indicates whether appropriate permissions have been granted to the users.