Microsoft SQL Server Access Requirements
The gateway uses an ODBC interface to the Microsoft SQL Server. To access Microsoft SQL Server, the following conditions must be met:
• User logins must be defined to the Microsoft SQL Server. Use mixed mode authentication to allow for both DBMS and OS authentication.
• Permission must be granted to access each Microsoft SQL Server database.
• Each user ID (or the group that contains the user ID) in the database must be granted the following permissions:
– Create Table
– Create View
– Create Stored Procedure
• Microsoft SQL Server 2005 and later also require the above permissions and have additional permission and setup requirements. Each user (including the ingres user) must be granted the following permissions on the database used by Enterprise Access:
– Create Function
– Create Schema
– Update
– Select
– Delete
– Insert
– Execute
Notes:
• You can add permissions using Microsoft SQL Server Management Studio. Simply right-click the desired database name, choose Properties and then Permissions. Alternately, you can create roles (or use default roles) that incorporate the required permissions.
• The Create Schema permission is required if you want iigwcatmss to create the ingres schema for you. If you choose not to grant the Create Schema permission, you must create the ingres schema manually.
• The ingres user can optionally be made the DBO of the database.
Note: If the ingres user as DBO connects to the database, all table name references are assumed to be dbo.<table_name> unless they are fully qualified with the explicit schema name (<schema_name>.<table_name>). Help commands will not find any dbo-owned table even if it is created by the ingres user as an unqualified table.
• An ODBC SYSTEM Data Source Name (DSN) can be defined for the database. The gateway can use the ODBC System DSN to access the Microsoft SQL Server database on the local or remote system. You should configure the DSN with an explicit database name rather than use the default.
• System DSN can be configured in one of the following two ways:
– With Windows authentication using the network login ID
– With SQL Server authentication using a login ID and password specified by the user.
If Windows authentication is used, the gateway detects this and ignores the following:
• Any user name and password passed into the gateway.
• Ingres Net connections are not allowed except for Direct Connect connections.
If Windows authentication is used, one of the following conditions must be met to enable Microsoft SQL Server to authenticate users:
• Client application must be local to gateway.
• Gateway server must be local to Microsoft SQL Server DBMS.
We recommend that aliases that use Windows authentication use NULL for the alias user name and password. Using NULL is good practice in case a DBMS authentication DSN is referenced by mistake and also helps to ensure a login failure.
Beginning with Windows 2000 Service Pack 4 and Windows 2003 Server, the "Impersonate a Client After Authentication" (SetImpersonatePrivilege) permission is required for the gateway installation owner if OS Authentication is used for the Microsoft SQL Server.
Support for Operating System authentication does not remove the need to have a DBMS user called "ingres" who is authenticated by the DBMS. The DBMS authenticated user "ingres" is used to create the gateway system catalog when running iigwcatmss or iigwcat.
Configure a Data Source (Windows)
A data source configuration is a collection of information that identifies the database you want to access using the ODBC driver. You can configure as many data sources as you require. Once defined, a data source is available for use by any application that uses ODBC.
ODBC data sources are a convenient way of connecting to a database. You can, however, connect to a database without them by using only a connection string. For details, see Connection String Keywords.
Note: Enterprise Access 11.x requires 64-bit ODBC data sources.
To configure a new data source on Windows
1. Run the ODBC Data Source Administrator provided on Windows.
To do this on Windows, click Start, Control Panel, Administrative Tools, Data Sources (ODBC).
The ODBC Data Source Administrator is displayed:
You can define one or more data sources for each installed driver. The data source name must provide a unique description of the data; for example, Payroll or Accounts Payable.
A data source can be defined as system or user, depending on whether it must be visible to all users (and services) or only the current user.
2. Select the User DSN or the System DSN tab, depending on your requirements, and click Add.
Note: A system DSN pointing to a public server definition is required for Microsoft Internet Information Server (IIS) and Microsoft Transaction Server (MTS).
The Create New Data Source dialog opens, which lists all the ODBC drivers installed on your system.
3. Select the driver of choice and click Finish.
The Ingres ODBC Administrator dialog opens.
4. Fill in the necessary fields in the Ingres ODBC Administrator. For field descriptions, click the Help button.
