TechnoteID: 8148

Subject: How do I access a SQL Server database in another domain?
PRODUCT: ClearQuest
OS: Windows NT
DEFECT #:
PATCH #:
REFERENCES:
CREATED: 22-Jun-1999
REVISED: 03-Dec-1999
QUESTION:
How do I access a SQL Server database in another domain?
ANSWER:
If your ClearQuest schema repository or user databases are on a
SQL Server on a machine in a different Windows/NT domain from the
machine on which you have installed the ClearQuest software, you
will get errors when trying to create or connect to the databases.
The problem is that if you have chosen default options when setting
up SQL Server, it uses "named pipes" as its network communication
mechanism. Using named pipes requires that the SQL Server be able to
access files on behalf of the invoking userid, which in turn requires
that the requesting userid be "known" in the SQL Server's domain. If
your userid is defined in one domain and the server is in another
and no domain-trust relationship exists, then the requesting userid
is not "known" in the server's domain so the authentication fails.
There are a variety of workarounds for this issue, each of which
require some effort to implement and each of which may be appropriate
in different circumstances.
The (technically) easiest workaround to implement is to create a
userid in the server's NT domain with the same username and password
as the requesting userid. Then, using named pipes when the SQL Server
tries to validate the NT userid and password, the authentication
succeeds.
Although this approach is easiest 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 NT
domain.
A better solution is to set up a "domain trust" relationship so the
domain the SQL Server is in "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).
Although this approach is also easy to set up (and has no administration
implications like the duplicate-userid's workaround above) it may not be
consistent with your company's security requirements. If not, then you
will not be able to use named pipes. Fortunately, there is another way:
you can switch to TCP/IP protocol for the connection.
To force the SQL Server connection to use TCP/IP, do the following:
On the SQL Server, configure the server to support TCP/IP (instead of,
or in addition to, named pipes). The details of how to do this are
different between SQL Server 6.5 and SQL Server 7.0:
For SQL Server 6.5:
1. Start the "SQL Setup" program (Start > Programs > Microsoft SQL Server
6.5 > SQL Setup).
2. Follow the instructions until the Options dialog.
3. Select "Change Network Support" and click Continue.
4. Check TCP/IP (you can also leave named pipes selected if you wish) and
click OK.
5. Depending on the number of network libraries you enabled, you will see
a number of configuration dialogs, one for each library. When you see
the one for TCP/IP be sure to use the default port (1433), or if you
wish to use a different port be sure to remember it for use in the
client-side part of the procedure documented below.
6. Stop/start the SQL Server using Enterprise Manager.
For SQL Server 7.0:
1. Bring up the "Server Network Utility" (Start > Programs > Microsoft
SQL Server 7.0 > Server Network Utility).
2. On the "General" tab, if TCP/IP does not already appear, click
"Add...", set the Network Library to TCP/IP, and ensure that the port
number is 1433 (or select a different port and remember it for use with
the client-side procedure documented below).
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 > ODBC (note its name may be "ODBC", "ODBC Data Source
Administrator", or other similar names).
2. Select the System DSN tab and click the "Add..." button.
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). Click OK to exit the Client Configuration dialog.
8. Enter the DBO (database owner) login name and password for the SQL
Server database that you are connecting to and click Next.
9. 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:
- The "Server Alias" field is the name that all applications (e.g. 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.
- 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"
hostanme 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.
For more information, contact Rational Software Technical Support.