1. Home
  2. Docs
  3. Administration
  4. SQL Server database issues

SQL Server database issues

1. Cannot Connect To SQL Server

Are you using FastMaint Web?

FastMaint Web uses Microsoft OLEDB to connect to SQL Server. You can test your connection to a local/ remote SQL Server instance without having to install the Microsoft SQL tools just to make that test. This can be done by creating an empty UDL file. To create this file use any folder on your system and create an empty text file. Give it some name (e.g. TestSQLConnection.udl). Instead of the default TXT extension make sure that the extension is UDL. Now double-click on the file. Windows will pop up a SQL connectivity window to allow testing of SQL connections. Make sure to select the Microsoft OLEDB Provider for SQL Server in the Provider tab before entering your connection properties to test.

If you get an SSL error e.g. “Unable to connect to database. Details: [DBNETLIB][ConnectionOpen(SECDoClientHandshake().]SSL Security error.“:
Make sure that the “Force Protocol encryption” option is removed from both the “SQL Server Network Configuration, Protocols For SQL …” and the “SQL Native Client … Configuration (32 bit)”.  Use the SQL Server Configuration Manager on the SQL Server computer. This is available under Control Panel>System and Security>Administrative Tools>Computer Management, then look under Services & Applications in the popup that comes up.

Are you using FastMaint Professional?

FastMaint Professional uses Microsoft ODBC to connect. You should use the Windows 32-bit ODBC Manager to test ODBC connections used by FastMaint Professional. On 64-bit machines if FastMaint Professional cannot see the System DSN you created, you may need to delete the System DSN and then create it again using the Windows 32 bit ODBC Manager. This 32 bit ODBC Manager can be found in Control PanelSystem and SecurityAdministrative Tools or in the folder location “C:WindowsSysWOW64odbcad32.exe”.

Still Unable To Connect?

See this detailed Microsoft article with detailed troubleshooting steps “SQL Connectivity troubleshooting checklist“.

2. SQL Server Performance Issues

Are seeing slow performance or frequent timeouts (e.g. errors like “execution canceled”) when using FastMaint with an external SQL Server database? Here are a few reasons we have seen at customer locations:

a) Open the database in SQL Server Management Studio. Make sure that the database recovery mode is Simple (the default SQL Server setting). Other modes can generate ever-growing log files that finally fill up all the disk space on the server unless someone truncates the logs regularly.

b) If you originally created the database on SQL Express edition turn off AutoClose. Databases created on SQL Server Express are set to AutoClose ON by default.

c) Make sure that FastMaint is using a SQL Server user account that has DDL administration as well as read+write access to the FastMaint database. Set the default schema for this user account to “dbo” (database owner). Otherwise you may get errors and reports of missing tables or objects in FastMaint. In SQL Server Management Studio you should see all tables in the FastMaint database have the prefix “dbo.” e.g. “dbo.sm_task”, “dbo.sm_vendor”, etc.

d) Check the memory utilization and CPU usage on the database server. Ideally memory usage should be 60% or less and CPU usage should average less than 10% (with occasional spikes). Make sure you do not have other open applications running on the server. For example having a running instance of Google Chrome left open on the server is known to cause large memory and CPU usage.

How can we help?