Summary

This article describes how to configure the adTempus SQL Server Express installation to allow connections from remote computers.

More Information

When you use the default SQL Server Express installation to host the adTempus database, the database server is by default configured so that only local connections are allowed (no connections are allowed from other computers). In some cases you may wish to allow remote access (for example, to query the database from another computer).

The configuration process is slightly different for SQL Server 2005 (used by adTempus 3) and SQL Server 2012 (used by adTempus 4).

SQL Server 2005 Express (adTempus 3)

To make this change:

1. Log in as an Administrator to the computer where the adTempus service is installed.
2. Go to the Start menu, then Microsoft SQL Server 2005, then Configuration Tools, then SQL Server Surface Area Configuration.
3. In the Surface Area Configuration tool, click the Surface Area Configuration for Services and Connections link.
4. Expand ADTEMPUS, then Database Engine, and select Remote Connections.
5. Check the option for Local and Remote Connections and Using TCP/IP Only.
6. Click OK.
7. Using the Windows Services tool, restart the "SQL Server (ADTEMPUS)" service. This will require restarting adTempus as well.

Designating System Administrators

Depending on the version of Windows running on the computer, members of the Administrators group may automatically have access to SQL Server. On newer versions of Windows, only the user who installed adTempus and SQL Server will have access by default, and other users will need to be added.

To add an additional user as a System Administrator for SQL Server:

1. Log in as an Administrator to the computer where the adTempus service is installed.
2. Go to the Start menu, then Microsoft SQL Server 2005, then Configuration Tools, then SQL Server Surface Area Configuration.
3. In the Surface Area Configuration tool, click the Add New Administrator link.
4. Enter the appropriate user ID in the User to provision box.
5. Select the "Member of SQL Server SysAdmin role on ADTEMPUS" privilege and click the right arrow button to add the privilege for the user.
6. Click OK to complete the configuration.

This will grant the selected user full permissions for the adTempus instance of SQL Server. To grant a lower level of access (such as read-only) use SQL Server Management Studio (see article K00000385) and refer to SQL Server documentation on user and security management.

SQL Server 2012 Express (adTempus 4)

To make this change:

1. Log in as an Administrator to the computer where the adTempus service is installed.
2. Go to the Start menu, then All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager.
3. In the SQL Server Configuration Manager, expand the node for SQL Server Network Configuration, then select Protocols for ADTEMPUS.
4. Right-click TCP/IP and select Enabled.
5. Close the SQL Server Configuration Manager.
6. Using the Windows Services tool, restart the "SQL Server (ADTEMPUS)" service. This will require restarting adTempus as well.
7. Using the Windows Services tool, locate the "SQL Server Browser" service. Change its startup type to "Automatic" and start the service if it is not already running.

Granting Access to Users

By default only the user who originally installed SQL Server will have permission to connect to SQL Server. To allow additional users to connect you must create SQL Server logins for them.

To do this:

1. Run the adTempus Database Utility from the adTempus group on the Start menu. This tool will automatically connect to the database.

2. Paste the following script into the query window. This will grant read-only access to a user.

use [master]
GO

if not exists (select * from master.dbo.syslogins where loginname = N'*USERNAME*')
CREATE LOGIN [*USERNAME*] FROM WINDOWS with DEFAULT_DATABASE=[*DATABASENAME*]
go

use [*DATABASENAME*]
GO

if not exists (select * from dbo.sysusers where name = N'*USERNAME*' and uid < 16382)
	CREATE USER [*USERNAME*] FOR LOGIN [*USERNAME*] WITH DEFAULT_SCHEMA=[dbo]
GO

exec sp_addrolemember N'db_datareader', N'*USERNAME*'
GO

3. Make the following changes to the script:

• Replace "*USERNAME*" with the user ID of the user to grant access to, including the domain if appropriate. For example, "corpdomain\bobw".
• Replace "*DATABASENAME*" with the name of the adTempus database. This will generally be "adtempus_computername", where computername is the name of the computer where adTempus is installed. If you are unsure of the database name, you can read it from the adTempus database settings in the Registry, as described in article K00000480.

4. Press F5 to execute the script. Make sure no error messages are reported.

5. Repeat as necessary to grant permission to additional users.

Firewall Configuration

Your firewall must be configured to allow incoming connections on port 1433 for connections to SQL Server.

Connection Information

Using SQL Server Management Studio or another database tool, connect to the database. You are connecting to the SQL Server instance named "adTempus." Therefore you generally will specify the server to connect to as "computername/adtempus", where computername is the name of the computer.

The database name will generally be "adtempus_computername", where computername is the name of the computer where adTempus is installed. If you are unsure of the database name, you can read it from the adTempus database settings in the Registry, as described in article K00000480.