Database Connection Errors while Installing Farm

Before going through this section, see Prerequisites for Debugging Exceptions.

Troubleshooting issues encountered for Database Connections while installing the Farm Server or the Farm Client are listed below.

1. User receives 'Could not establish connection to the SQL Server data source, Login failed for user <UserName>' on the Create Farm Database page or on the Connect to existing database page.

Probable Cause

SQL Server Instance is not able to authenticate the user.

Solution

The user in the error message, needs to be added in the SQL Database Instance(entered in Server Name field). This user can be a Windows user or can be a SQL user.

• Add Windows user

• Add SQL user

Database User should have logon machine rights in the Database Server Instance and the Farm Server (Database User should be able to login in to these machines successfully).

Add Windows user

1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.

2. Double click the Security folder, right-click Logins.

3. Click New Login.

4. On the General page, enter the name of a Windows user in the Login name box.

5. Select Windows Authentication.

6. Click OK. The user will get listed in the Logins folder.

Add SQL user

7. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.

8. Right-click the Security folder, point to New, and then click Logins.

9. Click New Login.

10. On the General page, enter a name for the new login in the Login name box.

11. Select SQL Server Authentication. Windows Authentication is the more secure option.

12. Enter a password for the login.

13. Select the password policy options that should be applied to the new login. In general, enforcing password policy is the more secure option.

14. Click OK.

Check http://msdn.microsoft.com/en-us/library/aa337562.aspx for more details.

2. Error: Test Connection Succeeded. However the login - <UserName> does not have dbcreator role in <ServerInstanceName>.

Probable Cause

The Farm Configuration Wizard is trying to create the Farm Database, entered in Database Title, using the <UserName> from the error message.

Solution

15. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.

16. Double click the Security folder, double click Logins.

17. Right-click on the <UserName>. Click Properties.

18. Click Server Roles. Check dbcreator.

19. Click OK.

3. The Create the Farm Database or Connect to existing database page does not respond while configuring the Farm.

Probable Cause

The local machine is not able to connect to the database server instance.

Solution

20. Connect to the database using an SQL Server Instance Client.

21. If Windows authentication was used while connecting, confirm that the database user has not changed the Active Directory password.

22. Test Connection Tool by AVEVA Work Tasks:

    This tool is used with the Load Balanced Server. Using the Test Connection tool, check if all the machines (Server and Client) are able to communicate with each other before installation. This test ensures that all the Server machines involved can actively support the Load Balancing and Failover support features of the Load Balanced Server. To check the communication between machines, the Test Connection tool must run simultaneously on all the machines.

    Messages received from all the machines are displayed by the Test Connection tool indicating that the machines are able to communicate with each other. Machines that are not able to communicate will not be able to receive or display messages from other machines.

23. DTCTester Tool by Microsoft:

    The MSDTC connection settings can also be checked using the DTCTester tool. Check the availability in Microsoft Download site.

    The DTCTester tool does the following:

    • Establishes a connection to SQL Server using a data source name (DSN), username and password. You can establish this connection via a command line using the default network library.

    • Creates a temporary table

    • Enlists the connection in a transaction

    • Does an insert on the temporary table

    • Commits the distributed transaction

    • Selects inserted value to make sure it was committed

    • Closes the connection

To use the DTCTester Tool:

1. Create an ODBC data source for your SQL Server through the ODBC utility in Control Panel.

2. Execute the following from the command line: dtctester <dsn name><user name><password>.

3. Replace the values in brackets as appropriate for your environment.

The table below lists the ports to be opened for DTC:

Check Enable and Verify MSDTC for more details.

4. Error logged in the Logger Console: RunScript failed, ExecuteNonQuery failed for Database <Database Name>

Probable Cause

If the local machine is 64-bit, two dlls of 'Microsoft.SqlServer.BatchParser' with Processor Architecture as x86 and AMD64 should exist in the assembly. If you have only one version of this dll present, refer to the following solution.

Solution

24. Go to the following website:

    http://www.microsoft.com/en-in/download/details.aspx?id=29065

25. Download and install the following packages:

    • X64 Package (SQLSysClrTypes.msi) from the Microsoft System CLR Types for Microsoft SQL Server 2016 section

    • X64 Package (SharedManagementObjects.msi) from the Microsoft SQL Server 2016 Shared Management Objects section.