HOWTO: Handling SQL Server Authentication and Accerler8DB or DataGate client

The information in this article applies to:

Acceler8DB, Release 5.0
DataGate for SQL Server (DSS), Release 5.0 and Release 7.x
Installation
MS SQL Server 2000 and MS SQL Server 2005
Windows
Security

SYMPTOMS:

Receiving security error messages when attempting to connect to SQL from Acceler8DB or DataGate client via DataGate for SQL Server (DSS).

RESOLUTION:

If you are having problems while trying to connect to your SQL Server database from Acceler8DB or DataGate client via DSS or wanting to know more information on how to configure SQL and ADB go to the MORE INFORMATION Section below.

MORE INFORMATION:

NOTE for DSS Release 5.0 users: If you are receiving error 1208 "License Manager Service not Accessible" from a client machine, please click here.

A SQL Server Instance can be configured (at installation or at a later time) to use Windows-only (domain) security authentication, or a combination of Windows and SQL Server authentication (See Figure 1).

Figure 1

Windows Authentication:

When using Windows authentication, SQL Server will use the User identity from the Windows client logon for security authentication. When administering Security logins for the database instance, you can specify individuals or groups from each trusted Windows domain that can connect to the instance (See Figure 2).

Using Figure 2, any PC where ASNAUS\Client1 has logged onto can connect to the database instance.

Figure 2

SQL Server Authentication:

When using SQL Server authentication, SQL Server will use the User identity and Password from the connecting application for security authentication. When administering Security logins for the database instance, you can specify individuals from anywhere that can connect to the instance (See Figure 3).

TIP: This implementation is very similar to the security authentication for the iSeries/400 in that all security is administered by the database engine rather than a larger, security domain,

NOTE: The application mentioned above might be: SQL Server Enterprise Manager, DataGate Database Manager, an AVR application, etc.

Using Figure 3, any application that provides the User ID of User and valid password can connect to the database instance.

Figure 3

Configuring Database Names:

When setting up the DataGate for SQL Server (DSS) database name to connect to a SQL Server database, you can direct DSS to connect using either Windows or SQL Server authentication.

Using Windows Authentication:

Use *DOMAIN to instruct the DSS client to establish security context with SQL Server security logins established for Windows Authentication (See Figure 4).

NOTE: Username is *DOMAIN. Leave password blank.

Figure 4

Using SQL Authentication:

Use a valid User ID and password to instruct the DataGate client to establish security context with SQL Server's Security Logins established for SQL Server Authentication (See Figure 5).

NOTE: Username is a valid login Name. Correspondingly valid password must be provided.

Figure 5