Setting Up a Shared Trimble Positions Desktop Database on SQL Server

The goal is to setup a shared SQL Server database called TPDatabase1 on SQLServer1 that will allow User1 on PC1 and User2 on PC2 to share their Trimble Positions projects, post-processing profiles, and field configurations.  This will be done by using Windows Authentication.  Let’s assume that User1 and User2 are Windows domain users and at least User1 is a member of the administrators group for the SQL Server installation on SQLServer1.  Also, User1 has SQL Server Management Studio (SSMS) installed on either PC1 or SQLServer1 so that he/she can connect to SQLServer1.  Finally, PC1 and PC2 have installed the SQL Server Native Client (at or above the SQL Server version), Esri ArcGIS for Desktop, and the Trimble Positions Desktop (TPD) add-in.

  1. Launch SSMS on PC1 or SQLServer1, connect to SQLServer1 as User1, find the ‘Databases’ node in the Object Explorer panel and right-click to create a ‘New Database’.
  2. In the SSMS dialog that opens, specify the name (TPDatabase1) and click ‘OK’.
  3. In the SSMS Object Explorer panel, find the ‘Logins’ node within the ‘Security’ node and right-click to create a ‘New login’.
  4. In the SSMS dialog that opens, specify the login name (User2).
  5. In the same SSMS dialog, click the ‘User Mapping’ link at left and note the list of databases displayed.  Click the checkbox next to TPDatabase1 and when the ‘Database role membership’ area is enabled below, click the checkbox next to ‘db_owner’. [Note: the combination of ‘db_datareader’ and ‘db_datawriter’ would likely be sufficient but this has not been explicitly tested.]  Click ‘OK’ to complete this.  SSMS can now be closed.
  6. Launch the TPD Configuration tool on PC1 and select the option to ‘Add/Edit a new configuration’.
  7. In the TPD Configuration screen, select ‘Single user’ or ‘All users on this PC’ as appropriate.
  8. In the same screen, select the radio button for ‘I will setup a database connection myself (Advanced)’.
  9. In the same screen, select the option to ‘Manage ODBC data sources’.
  10. In the ODBC Data Source Administrator window that opens, select the ‘System DSN’ tab.
  11. From the ‘System DSN’ tab, click ‘Add…’ and select an appropriate driver from the list (e.g., SQL Server Native Client 10.0), and click ‘Finish’.
  12. In the ‘Create a New Data Source’ (DSN) wizard, provide a name for the data source (we’ll call it TPonSQL), specify the server instance name (SQLServer1), and click ‘Next’.
  13. In the authentication page of the DSN wizard, leave the radio button for ‘With Integrated Windows authentication’ checked and click ‘Next’.
  14. In the next page of the DSN wizard, click the checkbox next to ‘Change the default database to’, select TPDatabase1 from the list, and click ‘Next’.
  15. In the final page of the DSN wizard, click ‘Finish’.  In the confirmation dialog that opens, click ‘Test Data Source…’ to confirm a proper connection.  Click ‘OK’ twice to get back to the ODBC Data Source Administrator screen.
  16. From the ODBC Data Source Administrator screen, click ‘OK’ to get back to the TPD Configuration screen.
  17. In the TPD Configuration screen, open the ‘ODBC data source’ select list and choose the TPonSQL entry that you just created.
  18. In the same screen, click the ‘Test data source’ link and at the prompt to create schema, click ‘Yes’.  Wait for the confirmation dialog and click ‘OK’.
  19. In the same screen, click the ‘Apply’ button.
  20. In the original TPD Configuration screen, click ‘Exit’.  This completes the configuration of PC1.
  21. As User2 logged in on PC2, repeat steps 6 through 20 and note that in step 18, you will not be prompted to create schema as the schema already exists and you have access to it.

Notes:

  • The SQL Server instance name has been simplified for this example.  It may take on the form SERVER\INSTANCE,PORT.
  • The usernames have also been simplified for this example.  In a domain (Active Directory) environment, they would take on the form DOMAIN\USER.
  • This example makes use of ‘System DSN’ entries which are specific to the machine.  For Citrix-type environments where users may login to different physical computers, the use of ‘User DSN’ entries should be considered in conjunction with the ‘Single user’ configuration type.
  • This procedure should work equally well with SQL Server Express provided the user also downloads and installs SQL Server Management Studio Express.
  • Although this example was given for SQL Server, it could also be done with Oracle, PostgreSQL, etc. assuming the user has the requisite drivers and management tools installed.
  • If SQL Server has been installed with ‘Mixed Mode Authentication’, it may be easier to create a SQL login with full access to the TPD database and then use this SQL login in the DSN entries on both computers.  SQL Server security best practices (and perhaps your IT department) should be consulted.
This entry was posted in Configuration, How To, Tips and Tricks. Bookmark the permalink.