Overcoming Database Connection Issues When Installing Wise Package Studio

What is the best way to build up a new Wise Package Studio Server (WPS)? This article will outline one approach to that task, and describe some the challenges found, and concentrate specifically around the database configuration side. Hopefully, this will be able to assist others who want to add additional WPS servers and functionality into their environments.

In trying to build a new server and a new instance of Wise Package Studio for another area of the company, we employed the following procedure:

1. We built up new server hardware.
2. Next, the Wise database installation procedure was run on test SQL 2000 server, creating the schema and databases. In order for this to work correctly, we needed to use an account with SA equivalency on the server in order to get those objects properly created.
3. Using basic SQL tools, we then moved the three WPS databases, Software Manager, Wise Services, and the Workbench database to a production SQL 2000 server. We then initiated the Wise Database Repository Configuration tool. The next two screen shots show those pages:
   
   

   Click to view.

   

   Click to view.

4. In this case we began with the Workbench database. Our first step in configuration of this database was to provide the correct credential set, appropriately reflecting how the database would be used in production. The account that we were logged in with was setup as a DBO for the WPS databases. Based on the fact that they were already created, and since DBO was the privileges required to run WPS, we were confident that we had the correct credentials. But we received the following error, shown in the screenshot below, as we attempted to continue configuration through the wizard to connect the databases.

Click to view.

Why did that not work? Since the database structure was already created, the privileges and permissions held by the SA authority should not have been required any more, right?

Well, interestingly enough, there are a couple of important things to remember about the database configuration side of WPS.

1. Wise Package Server requires SA account equivalency for the creation, and initial configuration of the databases.
2. In addition, the database server should be configured to use mixed-mode authentication for SQL 2000. To set this configuration option in SQL 2005, the screen shot below shows the SQL Server and Windows Authentication mode radio button checked. This is the same as mixed-mode authentication in SQL 2000.
   
   

   Click to view.

This allowed the remainder of the database configuration to complete for each of the three WPS databases, Software Manager, Wise Services, and the Workbench database. Once the basic configuration was completed, it became important to determine the minimum SQL security requirements for running WPS.

We then created a basic test configuration of WPS, and re-setup the software, determining the actual operational-level SQL permissions required to safely and successfully run Wise Package Studio. We did that in the following manner:

1. We installed the software using default permissions, noting how they were configured.
2. We then removed all SQL accounts and configured the SQL security permissions according to what we discovered.
3. This allowed us to produce the table shown below, outlining the minimum permissions required in SQL to run WPS.

Our hope is that this information will make it easier to troubleshoot any database permission issues in the future, and resolve database-related problems faster.

Please use this table as a reference of the items in SQL and how they need to bet set.