Accessing a Microsoft SQL Server database in another domain

Resolving The Problem

Here are some known workarounds:

---

Configure User Access to the same Domain as SQL Server

Configure a Domain Trust

Configuring the SQL Server connection for TCP/IP

For SQL Server 7.0

For SQL Server 2000

For SQL Server 2005

Configure each ClearQuest to use TCP/IP for its SQL Server connections

---

There are several ways to workaround this problem:


Configure User Access to the same Domain as SQL Server

One workaround is to create a user ID in the server's Microsoft Windows domain with the same user name and password as the requesting user ID. Then, when SQL Server using named pipes to authenticate, the it succeeds.

Although this approach is simple to implement, it is not a good solution for large networks with large numbers of users, as it requires that all userid/password maintenance be done twice, once in each Microsoft Windows domain.


Configure a Domain Trust

Another solution is to set up a "domain trust" relationship so the domain the SQL Server "trusts" the domain that the requesting user is defined in. The trust relationship effectively makes all the users defined in the "trusted" domain visible and valid in the "trusting" domain (the SQL Server's domain).

This approach is also simply in application (and has no administrative implications like the duplicate-userid's workaround above,) it may not be consistent with your company security requirements.


Configuring the SQL Server connection for TCP/IP

If the previous solutions are not feasible, or are not allowed under your company security policy, then named pipes cannot be the sole network protocol for SQL Server. Configure SQL Server to use TCP/IP.

On the SQL Server, configure the server to support TCP/IP instead of, or in addition to, named pipes.


For SQL Server 7.0:

1. Bring up the "Server Network Utility", under Start > Programs > Microsoft SQL Server 7.0.
2. On the General tab, if TCP/IP does not already appear, click Add...
3. Set the Network Library to TCP/IP.
4. Ensure that the port number is 1433. Alternatively, you can select a different port and remember it for use with the client-side procedure documented below.

For SQL Server 2000:

1. Bring up the "Server Network Utility", under Start > Programs > Microsoft SQL Server.
2. On the General tab, if TCP/IP does not already appear in the Enabled Protocols box, select it and click Enable >>.
3. Select TCP/IP and click Properties...
4. Ensure that the port number is 1433. Alternatively, you can select a different port and remember it for use with the client-side procedure documented below.

For SQL Server 2005:

1. Bring up the "SQL Server Configuration Manager", under Start > Programs > Microsoft SQL Server 2005 > Configuration Tools.
2. Expand the SQL Server 2005 Network Configuration option, and select Protocols for MSSQLSERVER.
3. Enable TCP/IP if it is not already enabled.
4. Double-click on TCP/IP.
5. On the IP Addresses tab, enure that the port number for the applicable IP address is 1433. Alternatively, you can select a different port and remember it for use with the client-side procedure documented below.

Note: At this point, depending on network configuration, you might be able to connect to the databases. Test out the connection on a ClearQuest client, and proceed with these instructions if connection errors persist.

---

Configure each ClearQuest client to use TCP/IP for its SQL Server connections

Next, you will need to configure each ClearQuest client workstation to use TCP/IP for its SQL Server connections. To do that, go to each client machine and define an ODBC System DSN. A "system DSN" is a specification that supplies connection parameters that this client machine will use when connecting to a specific SQL Server host. To set up the system DSN, do the following:

1. Bring up the ODBC administrator dialog: Start > Settings > Control Panel > Administrative Tools > ODBC. Note: Its name may be "ODBC", "ODBC Data Source Administrator", or other similar names.
2. Select the System DSN tab and click Add...
3. Select the SQL Server driver and click Finish.
4. Enter any name you like in the "Name" field and enter the SQL server's hostname in the "Server" field. Enter a description for the connection if desired. Click Next.
5. Choose SQL Server authentication, which will enable the login ID and password boxes below.
6. Press the Client Configuration button.
7. In the "Network libraries" selection, choose TCP/IP (details vary depending on your ODBC version). Set the "Computer Name" field to the server's hostname unless specific network issues at your site require you to do otherwise (see note below). Set the port number to match the TCP/IP port number you chose on the server side above (1433 by default).
8. Click OK to exit the Client Configuration dialog.
9. Enter the DBO (database owner) login name and password for the SQL Server database that you are connecting to and click Next.
10. Choose the defaults in the next three steps of the wizard, until you get to the final confirmation dialog and see the "Test Data Source..." button. Click the Test Data Source... button to ensure that a connection can be made. It should indicate that the tests worked successfully.

Note: In step 7, the "Server Alias" and "Computer Name" fields normally should be set to the same value: the hostname of the SQL Server machine. However, if your site has special requirements you may need to do otherwise. The details are as follows:

• Server Alias
  
  The "Server Alias" field is the name that all applications (like ClearQuest) will use to connect to the SQL Server. You should use this name as the "Server" name when creating your ClearQuest databases in the ClearQuest Maintenance Tool or ClearQuest Designer. The name used here must be the same on all ClearQuest client workstations, as this value will be stored in the ClearQuest database and used by all ClearQuest-related programs when connecting to the database server. This should usually be set to the SQL Server computer's hostname although technically it does not need to be the same.

• Computer Name
  
  The "Computer Name" field on the ODBC dialog specifies the IP-level connectivity information for reaching the computer on which SQL Server is running. Normally this should be the same as the server hostname but if your site has special requirements you can put a domain-qualified hostname or even the SQL Server host's IP address in the "Server" field instead of the hostname. You would use this, for example, if there are two hosts with the same name in your network, or if WINS/DSN are not set up at all so you cannot resolve host IP addresses by any other mechanism than direct IP address.

The best approach in such cases is to resolve the underlying issues (duplicate hostnames or WINS/DNS setup) so as to allow the use of the "simple" hostname in both the "Server Alias" and "Computer Name" fields; however if you are unable to do that due to network management constraints you can work around the problem by using the domain-qualified hostname or IP address in the "Computer Name" field.

The "Computer Name" field does not have to be specified identically on every ClearQuest client workstation, but on every workstation it must be specified in a form that will allow the client to "see" the server at the IP level (use 'ping' to determine this).

Having set up the SQL Server to support TCP/IP protocol and having configured the client workstations to use it, you should now be able to use ClearQuest together with your SQL Server even though they are in different domains.