• Article

Configure a linked server in SQL Server (Project Server 2010)

 

Applies to: Project Server 2010

Topic Last Modified: 2013-12-18

Configuring a linked server connection in Microsoft SQL Server is done in conjunction with using the Project2003LinkedSQLServer= setting in the migration configuration file. You only need to use this setting and configure the linked server connection when you are migrating a Microsoft Office Project Server 2003 database that cannot be copied and restored on the Microsoft Office Project Server 2007 database server and it must be connected to remotely.

Configure a Linked Server connection using SQL Server 2000

If you are migrating a Project Server 2003 database in SQL Server 2000 to Office Project Server 2007 on another computer, and the database tier for Office Project Server 2007 is SQL Server 2000 as well, use the following procedure to configure a linked server connection.

Configure the connection

  1. Connect to the SQL Server hosting the Office Project Server 2007 databases by using Enterprise Manager.

  2. In the tree structure, click to expand the Security folder.

  3. Right-click Linked Servers, and then click New Linked Server.

  4. Refer to SQL Server documentation for more information on how to create a linked server (search for the term "linked servers"). Make sure you click the Security tab and select the correct security option. (For example, select be made using the login's current security context option button. If this option does not work in your environment, select be made using this security context and give a valid user name and password that works on the computer running SQL Server that is hosting the Project Server 2003 database.)

  5. Verify that the linked server you created functions properly before proceeding with the migration. You should see the linked server you just created under the Linked Servers node. Once you expand the linked server, you should see the Tables and Views nodes. If you click Tables or Views, you should see some entries

  6. Use the name of the linked server as the value for the Project2003LinkedSQLServer= parameter in the migration configuration file.

Note

In some IT environments, specific ports related to Microsoft Distributed Transaction Coordinator (MSDTC) might need to be opened for the linked server configuration to work. For more information, see the SQL Server documentation about linked servers or contact your system administrator.

Configure a linked server connection from SQL Server 2000 to SQL Server 2005

If you are migrating a Project Server 2003 database in SQL Server 2000 to Office Project Server 2007 on another computer, but the database server being used by Office Project Server 2007 is SQL Server 2005, use the following procedures to configure a linked server connection.

First, in both SQL Server 2000 and SQL Server 2005, set the authentication mode to mixed, so that SQL Server logins are accepted along with Windows logins.

Configure SQL Server 2005 authentication to mixed mode

  1. Open SQL Server 2005 Management Studio.

  2. In the Object Explorer pane, right-clicking the SQL 2005 server name, then click Properties.

  3. In the Select a Page list, click Security.

  4. In the Server Authentication section, select SQL Server and Windows Authentication Mode.

  5. Click OK.

Configure SQL Server 2000 authentication to mixed mode

  1. Open SQL Server Enterprise Manager.

  2. Expand the server group that contains the database server that you will use with Project Server 2003.

  3. Right-click the server, and then click Properties.

  4. Click the Security tab.

  5. Under Authentication, click SQL Server and Windows.

  6. Click OK.

Configure the connection

  1. On the computers running SQL Server 2000 and SQL Server 2005, create an identical SQL authenticated user account to run the linked server connection. Make sure that the password is the same for the accounts on both servers. Ensure that the accounts have read/write access to the databases you intend to access using the linked server system.

  2. In SQL Server 2000, open Query Analyzer and on your master database run a file called instcat.sql. It should be located under your SQL folders in Program Files.

  3. In SQL Server 2005 Management Studio, select your computer running SQL Server 2005, expand Server Objects, right-click Linked Servers, and click New Linked Server.

    1. Enter the server name of your computer running SQL Server 2000.

    2. Select the SQL Server option.

    3. Click the Security tab in the side pane.

    4. Select the Be made using this security context option.

    5. Enter the SQL account and password needed to access the Project Server 2003 databases and Office Project Server 2007 databases (created in step 1).

    6. Click OK.

  4. In SQL Server 2005 Management Studio, launch a query against the linked server to verify that it worked:

    1. SELECT * from <P200Server>.<P11DBName>.dbo.msp_web_admin

    2. Replace the server and database name with appropriate values.

    3. If this query returns successfully, your connection is successful.

Note

When you run the migration, make sure the user account with which you are running the migration tool has read access to both your Project Server 2003 and Office Project Server 2007 databases.

Configure a linked server connection from SQL Server 2000 to SQL Server 2008

If you are migrating a Project Server 2003 database in SQL Server 2000 to Office Project Server 2007 on another computer, but the database server being used by Office Project Server 2007 is SQL Server 2008, use the following procedures to configure a linked server connection.

First, in both SQL Server 2000 and SQL Server 2008, set the authentication mode to mixed, so that SQL Server logins are accepted along with Windows logins.

Configure SQL Server 2008 authentication to mixed mode

  1. Open SQL Server 2008 Management Studio.

  2. In the Object Explorer pane, right-clicking the SQL 2008 server name, then click Properties.

  3. In the Select a Page list, click Security.

  4. In the Server Authentication section, select SQL Server and Windows Authentication Mode.

  5. Click OK.

Configure SQL Server 2000 authentication to mixed mode

  1. Open SQL Server Enterprise Manager.

  2. Expand the server group that contains the database server that you will use with Project Server 2003.

  3. Right-click the server, and then click Properties.

  4. Click the Security tab.

  5. Under Authentication, click SQL Server and Windows.

  6. Click OK.

Configure the connection

  1. On the computers running SQL Server 2000 and SQL Server 2008, create an identical SQL authenticated user account to run the linked server connection. Make sure that the password is the same for the accounts on both servers. Ensure that the accounts have read/write access to the databases you intend to access using the linked server system.

  2. In SQL Server 2000, open Query Analyzer and on your master database run a file called instcat.sql. It should be located under your SQL folders in Program Files.

  3. In SQL Server 2008 Management Studio, select your computer running SQL Server 2008, expand Server Objects, right-click Linked Servers, and click New Linked Server.

    1. Enter the server name of your computer running SQL Server 2000.

    2. Select the SQL Server option.

    3. Click the Security tab in the side pane.

    4. Select the Be made using this security context option.

    5. Enter the SQL account and password needed to access the Project Server 2003 databases and Office Project Server 2007 databases (created in step 1).

    6. Click OK.

  4. In SQL Server 2008 Management Studio, launch a query against the linked server to verify that it worked:

    1. SELECT * from <P2003Server>.<P11DBName>.dbo.msp_web_admin

    2. Replace the server and database name with appropriate values.

    3. If this query returns successfully, your connection is successful.

Note

When you run the migration, make sure the user account with which you are running the migration tool has read access to both your Project Server 2003 and Office Project Server 2007 databases.