There are times when you are unable to log into the vault on a client machine with an error of ‘Could not access item in database’.
You may also see repeated connection error entries in the PDM client log file as shown in the screenshots below.
This generally indicates that the PDM Client machine is unable to communicate with the SQL Server properly. Creating an ODBC connection on your PDM Client machine will resolve this issue. Creating this connection is a manual handshake between the client machine and the database server. This has also been useful for clients who work remotely and, in some cases, helps with performance.
Information needed to set up the ODBC connection
Before you can create an ODBC connection on your client machine, you will need the following information:
- You will need the sa password to your database. You can test if it is the correct password by logging into Microsoft SQL Server Management Studio (SSMS).
- Server\Instance Name. This can be found in the Microsoft SQL Server Management Studio (SSMS).
- If you are using SQL Express (PDM Standard), you will need to know what ports SQL Express has dynamically used. You can find these in the SQL Server Configuration Manager. If you do not change this port to 1433, or 1434 you will need to open this port in your server’s firewall.
After gathering the above information, you are ready to proceed with setting up the ODBC connection on the client machine.
Setting up the ODBC Connection
Go to the client machine experiencing the issue and open ODBC Data Sources (64-bit).
(It is sometimes needed to perform the same steps for the (32-bit) version, especially if using 32-bit Office applications)
After opening ODBC 64-bit, navigate to the System DSN tab and add a source.
Select SQL Server and hit Finish.
You will then name and describe the data source you are creating. More importantly, you need to select or enter the name of your server hosting your database. This is the information found from step 2 when logging into SSMS.
You will then select the SQL server authentication radio button. You will also need to provide the SQL Server login credentials by typing in sa for the Login ID and the corresponding password. !!!BEFORE SELECTING NEXT, BE SURE TO CLICK ON CLIENT CONFIGURATION!!!
After selecting Client Configuration and before selecting Next, you will add your dynamic port you got from the SQL Server Configuration Manager. This is found in step 3 from the above required information. In order to add your own dynamic port, you will first need to uncheck the 'Dynamically determine port'. If you do not need to change this information, do not uncheck that option.
Select 'Change the default database to' and select your vault name. Then, click Next.
On the next page, we do not have to change anything, so select Finish.
Select Test Data Source in the new window.
If everything was entered correctly, you should see the following screen. You can simply click on OK to finish the process.
These are the steps to create an ODBC connection to your database.