Primavera P6 Professional : “Unable to connect to the database” error

When connect SQL database in Primavera P6 Professional, we usually have problem like:

  • Error Message:  Bad public user Name or password. SQL Server Error: [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.
  • Error message: Bad public user name or password. SQL Server Error: [DBNETLIB][ConnectionOpen (Invalid Instance()).] Invalid connection.;”
  • Error Message:  Bad public user name or password. Database Server Error: Login failed for user ‘pubuser’.

Below are some Reason and Solution:

CAUSE 1:
Incorrect hostname, instance or database name
Bad public user Name or password. SQL Server Error: [DBNETLIB [ConnectionOpen(Connect()).]SQL Server does not exist or access denied.;

The default configuration entries for a standalone installation are localhost\primavera for the server host name and PMDB$primavera for the database name.

FIX 1:
Click back to the Database Configuration and correct the database name or hostname or instance.

Primavera P6 Professional Unable to connect to the database error-1


CAUSE 2:
Connecting to an existing SQL Server 2005 (or SQL Server 2005 Studio Express) instance and there were no network protocols enabled for the  instance.

FIX 2:
Enable the TCP/IP in the SQL Server Configuration Manager.

Click Start, All Programs, Microsoft SQL Server 2005, Configuration Tools, SQL Server Configuration Manager.
Select your instance of SQL Server 2005 (or SQL Server 2005 Studio Express) instance in the ‘SQL Server 2005 Network Configuration’ left-hand section.

Primavera P6 Professional Unable to connect to the database error-2.jpg

Double-click on TCP/IP in the right-hand section and set Enabled to Yes then click Apply and OK.

 

Primavera P6 Professional Unable to connect to the database error-3

Restart the Service by going to Start, Run and typing in Services.msc and clicking OK.  In the Services window locate the SQL Server. Entry for your instance then right-click it and select Restart.


CAUSE 3:
Firewall is enabled on either the client or server preventing communication between the application and database.

FIX 3:
Disable the firewall on the machine or create an exception within the firewall to allow the ports /or programs to properly communicate. (ensure the machine has other anti-virus/firewall software) . You will then find the connection will be successful.

Default port for SQL is 1433. Default Port for Oracle is 1521. An exception can be added to the firwewall for pm.exe and dbconfig.exe

CAUSE 4:
On a machine that does not have a network cable plugged in, ‘localhost’ is not recognized as a valid replacement for the machine name.  (This is common in a ‘stand alone’ environment.)

FIX 4:
Replace ‘localhost’ with the actual machine name in the db alias connection information


CAUSE 5:
User made a typo in the server name.  Accidentally typed ” / ” (forward slash) instead of the correct ” \ ” (back slash) or didn’t enter the right name of the Instance.

FIX 5:
Enter the server name correctly.  (Example: “<hostname>\<Instance Name>” where <hostname> is the machine name where SQL Server is installed and <Instance Name> is the name of the Instance where the P6 database is located. Example: localhost\Primavera )


CAUSE 6:
Databases were not created during standalone installation.  To verify:

Click Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio/ SQL Server Management Studio Express.
Log in either with Authentication set to Windows Authentication or using Login: sa and Password: Prima123Vera with SQL Server Authentication.

Expand the databases folder and see is databases ‘pmdb$primavera’ and ‘mmdb$primavera’ have been created.

FIX 6:

For P6.0 and P6.1 run the ConfigAsst.exe from CD1 to create databases:  (See admin guide pg. 36)

For P6.2 or P6.2.1 run the dbsetup.bat utility.


CAUSE 7:

MSSQL database windows service is not started for a MS SQL database.

Check the Status of the SQL Server Management Studio/SQL Server Management Studio Express service by going to Start, Run and typing in Services.msc and clicking OK.

Locate the SQL Server .. service entry for your instance (for example MSSQLSERVER) and examine the Status column:

 

Primavera P6 Professional Unable to connect to the database error-4

FIX 7:

Right-click the service for your instance and select Restart.

 

Primavera P6 Professional Unable to connect to the database error-5

Double-click on the Service to open its Properties and set the service to Automatic so it will restart after a shutdown of the machine:

 

Primavera P6 Professional Unable to connect to the database error-6

 

Primavera P6 Professional Unable to connect to the database error-7


CAUSE 8:

If running full Microsoft SQL Server 2005, Authentication setting is set to “Windows Only”.

FIX 8:

Click Start, All Programs, Microsoft SQL Server 2005.

Within Server Management, right click on the Server icon and select Properties.  On the Security tab, set Authentication to ‘SQL Server and Windows Authentication Mode’:

 

Primavera P6 Professional Unable to connect to the database error-8


CAUSE 9:

The SQL port was changed on the server and either:

1.) the new port is not dynamically resolving, or

2.) there is already a static port specified on the client machine.

FIX 9:  (to be done from the machine that can’t connect to the database)

  1. Click: Start, Run.
  2. Type: cliconfg, Click: OK.
  3. In the SQL Server Client Network Utility, click: ‘Alias’ (tab)
  4. If an alias is already defined, and its name matches the server name being used while configuring a Primavera Project Management connection, adjust the port number and close.  Otherwise, proceed to the next step:
  5. Click: ‘Add’ (button), TCP/IP.
  6. Type a name in ‘Server alias’.
  7. ‘Server name’ must match the database location.
    NOTE: Specify the instance name, if there is one.
  8. ‘Dynamically determine port’ should NOT be checked.
  9. ‘Port number’ must match the new port of the SQL Service.
  10. Click: OK, Apply, OK.

CAUSE 10:

The application was installed using the “Client Applications” whereas the intention was to install the Stand-alone version of the software, which installs a database locally for the client to connect to.

 

Primavera P6 Professional Unable to connect to the database error-9

FIX 10:

Uninstall the software and re-install, choosing Stand-alone rather than Client applications


CAUSE 11:

The client machine and database server are on different domains.

 

FIX 11:

  1. Restart the database configuration wizard for the P6 Client
  2. At the screen to specify the hostname and database:
    • For the hostname field, try specifying the host as: IPADDRESS or IPADDRESS\INSTANCENAME
    • For the database name, enter the database being used to connect
  3. Complete the remaining steps of the database configuration wizard.

If it still continues to generate the issue, utilize a TCP/IP method to connect to the database instead of shared memory:

  1. Restart the database configuration wizard for the P6 Client
  2. At the screen to specify the hostname and database:
    • For the hostname field, try specifying the host as: <IPADDRESS>,<port> (Example: 10.10.10.10,1433)  — (You will not need to include the instance name with this connection)
    • For the database name, enter the database being used to connect
  3. Complete the remaining steps of the database configuration wizard.

CAUSE 12:

The SQL Database Server TCP port was changed and the Server was never restarted.

FIX 12:

Restart the SQL Server.


CAUSE 13:

The ‘SQL Server Browser’ service is not running on the Server machine hosting the database. All SQL Server named instances register its port with the ‘SQL Server Browser’ service when it is running, so when a P6 client connection uses ‘MachineName\InstanceName’ to connect to the sql server, the client will talk with SQL Server Browser first to get the named instance port, then connect to that named instance by this port.  If SQL Server Browser is disabled, client connections could fail with the reported error since it doesnt understand the TCP/IP connection that should be made.

FIX 13:

On the database Server go to Start, Run and typing in Services.msc and click OK.  Locate the SQL Browser Service, right-click it and select Start.  Then double-click on the Service to open its Properties and set the service to Automatic so it will restart after a shutdown of the machine.

Once the service is running, reattempt connection to the server with the database configuration wizard.

If the connection still fails with the service running:

  1. Restart the database configuration wizard for the P6 Client
  2. At the screen to specify the hostname and database:
    • For the hostname field, try specifying the host as: <servername>,<port> (Example: mySQLServerName,1433)  — (You will not need to include the instance name with this connection)
    • For the database name, enter the database being used to connect
  3. Complete the remaining steps of the database configuration wizard.

CAUSE 14:

The SQL Server Primavera service is not starting as the Local System Account.  This may be causing an OS 5:  Access is denied message when attempting to start the service.

FIX 14:

Use Windows Services (start/run/services.msc)

Right click on SQL Server Primavera service and choose Properties

For the Log On tab: Log on as Local System Account – Apply – OK

Start the SQL Server Primavera Service


CAUSE 15:

P6 clients installed on Windows XP can connect but the P6 clients installed on Windows Vista cannot

or

P6 clients installed on Windows 2008

Microsoft SQL 2005 SP1 is not supported on Vista

FIX 15:

For Windows XP Upgrade Microsoft SQL Server 2005 to SP2

For Windows 2008 use Fix 9 above and supply a period for the hostname field, i.e. just:  .


 

CAUSE 16:

Data Exection Prevention (DEP) is enabled.

FIX 16:

DEP can either be disabled (set to essential windows programs and services only), or the sql programs would have to be added to the list.


CAUSE 17:

IP Address is being used in the alias to connect to the Database Server host, and because DHCP is enabled, workstation hosting the database server caused the IP Address to change, resulting in an incorrect host entry.

FIX 17:

Replace the host name variable with the new IP assigned to the database server (OR) use the fully qualified hostname in replace of an IP Address (so long as the host is registered with the DNS Server, and accessble via hostname.  See your network administrator for further information).


NOTE: For issues relating to the native MS SQL server client; registry entries specifically pointing to a sql instance and port while accessing either a different named instance or a different port.

Refer to Microsoft article: http://support.microsoft.com/kb/328383

Under key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0, add string value=<servername or ip>\instance name  data=DBMSSOCN,<servername or ip>\instancename,port

For example:  string vaule=localhost\primavera  data=DBMSSOCN,localhost\primavera,1433


CAUSE 18:

SQL Server Database Log is full

FIX 18:

Shrink the database by following the directions :

1. Open up Microsoft SQL Server Management Studio Express

2. Right click on the database for shrinking.

 

Primavera P6 Professional Unable to connect to the database error-10

3.  Click on Tasks.

4.  Click on Shrink.

5.  Click on Database.

6.  This screen will give the available free space. Click ok, if you want to continue.

 

Primavera P6 Professional Unable to connect to the database error-11


CAUSE 19:

Database was restored and has not had the public and private database logins re-synced.

FIX 19:

Re-sync the public and private database logins using the following syntax:

EXEC sp_change_users_login ‘Update_One’, ‘db_user_name’, ‘db_user_name’

Example:

USE PMDB

EXEC sp_change_users_login ‘Update_One’, ‘pubuser’, ‘pubuser’
EXEC sp_change_users_login ‘Update_One’, ‘privuser’, ‘privuser’


CAUSE 20:

The SQL Server database may be set to auto-close.  When trying to log in, the database may not be ready to accept the connection.

FIX 20:

Change auto-close setting to false:

1) Log into SQL Server Management Studio
2) Expand Databases
3) Right-click on the P6 Database
4) Options page
5) Under Automatic Section, Set Auto Close to False
6) Click OK

Advertisements

Author: Khuong Do

Khuong Do is a Project Planner in Civil, Transportation and Oil & Gas Industry. He has worked as a Primavera Consultant Specialist since 2006 . He holds a Civil Engineering degree and a Bachelor of Information Management System. Now he is working in Petronas RAPID project, one of the biggest Refinery and Petrochemical complex, in Pengerang – Malaysia.

3 thoughts on “Primavera P6 Professional : “Unable to connect to the database” error”

  1. I notice you failed to address all the issues related to connecting to an Oracle database. Creating a proper tnsnames file on the client machine and correct sqlnet file on the server and client machine and proper listener file on host machine can be a bear to deal with in the many different network configuration/securiry configurations that can exist. Not to mention the additional Oracle connection challenges presented by virtual lans and virtual machines.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s