Microsoft SQL Server Access Requirements
The gateway uses an ODBC interface to Microsoft SQL Server. To access the Microsoft SQL Server, the following conditions must be met:
• User logins must be defined to 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) must be defined for the database. The gateway uses the ODBC System DSN to access the Microsoft SQL Server database on the local or remote system through the Microsoft SQL Server driver (see
Gateway Configurations When Using OS Authentication). We recommend that the DSN be configured with an explicit database name and not left with the default.
System DSN can be configured in one of the following ways:
– With Windows authentication, using the network login ID.
– With Microsoft SQL Server authentication, using the 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.
Gateway Configurations When Using OS Authentication
Microsoft SQL Server has a Microsoft Operating System restriction on delegated impersonations (known as the Double Hop problem). For Microsoft SQL Server to authenticate users, one of the following conditions must be met:
• Client application must be local to the gateway.
• Gateway server must be local to the Microsoft SQL Server DBMS.
SQL Server will not allow a remote gateway with remote gateway client connections.
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" (SeImpersonatePrivilege) permission is required for the gateway installation owner if OS Authentication for the Microsoft SQL Server is used.
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.
Invalid Configuration When Using OS Authentication
A configuration where the client, gateway, and database server are each remote will not work due to the Microsoft SQL Server limitations documented previously. For example:
Machine 1: Client application, such as OpenROAD
Machine 2: MSSQL Gateway
Machine 3: Microsoft SQL Server
Allowable Configurations When Using OS Authentication
The following client/server configurations are allowed:
• Client is local to the gateway but the database server is remote to the gateway, such as the following:
Machine 1: Client application, such as OpenROAD, and MSSQL Gateway
Machine 2: Microsoft SQL Server
• Client is remote to the gateway but the database server is local to the gateway, such as the following:
Machine 1: Client application, such as OpenROAD
Machine 2: Microsoft SQL Server and MSSQL Gateway
Connecting to MSSQL Server as the Remotely Authenticated User
A user can connect to MSSQL Server as the same user that remotely authenticates through Enterprise Access and at the local operating system level. This user need not be a privileged administrator.
To configure remotely authenticated user connection
1. Log in to the MSSQL Server Management Studio as the selected user.
This should be an operating system user. You should be logged in to the machine as this user.
2. Run this query against the connected database:
SELECT user
The query should return the name of the user you are logged in as.
If you get the user name of the database owner instead, you can change this in the MSSQL Server Management Studio by selecting Security, Logins, User properties, User mapping. The value of “User” should be the simple name of the user.
When you get a query result of the logged in user, continue.
3. On the server, start an Enterprise Access Netadmin Command Window from the Windows Start menu.
4. Execute the following command:
sql dbname/mssql
where dbname is the database gateway alias defined in iigwalias.
5. At the SQL prompt, execute the following command:
SELECT user\G
This should return the user you are logged in as.
6. Log in to the client machine as this user at the OS level.
7. Create a vnode using this user name and its password (from the server’s OS).
8. Execute the following command:
sql vnode_name::dbname/mssql
If the SQL prompt is displayed, run:
SELECT user\g
This should return the name of the user you are logged in as.
After this configuration is complete, you can set up private vnodes for individual if multiple users are expected to connect from this client installation.
If you do not want to create multiple vnodes manually, you can use vnodeless connections (dynamic vnodes). For more information, see
Dynamic Vnode Specification—Connect to Remote Database in the Ingres documentation.
DSN Setup for Windows
Enterprise Access is a 32-bit application and requires a 32-bit DSN. Users with 32-bit Windows operating systems can configure this DSN using the ODBC Data Source Administrator from the Administrative Tools folder in Control Panel. Alternately, users can run the ODBC Data Source Administrator from the following location:
%WINDIR%\System32\odbcad32.exe
However, users with 64-bit Windows operating systems must not use Control Panel to access ODBC Data Source Administrator because that version only displays 64-bit drivers. Instead, users with 64-bit operating systems should run the following version to configure a 32-bit DSN:
%WINDIR%\SysWOW64\odbcad32.exe
To configure the DSN settings for Windows
1. From the ODBC Data Source Administrator dialog, select the System DSN tab and then click Add.
2. Select the SQL Server driver from the list, and click Finish.
3. Name the DSN, for example, localmss.
4. Set the Server name to the name of the machine where the SQL Server database server is installed. Click Next.
6. Fill in the Login ID and Password fields with the ingres DBMS user login information. Click Next.
7. Check Change a default database to and then select a database from the drop-down list. Do not select "default". Click Next.
8. Check Perform translation for character data. (For additional information on character data, see Checking Character Set Compatibility in
Basic Troubleshooting and Tracing.)
9. (Optional) To use regional settings for II_DECIMAL support with the Microsoft SQL Server gateway, check Use regional settings when outputting currency numbers, dates and times. Click Finish.
10. (Optional) Click Test Data Source to try the connection. Click OK to end setup.
Microsoft SQL Server Gateway and II_DECIMAL
The II_DECIMAL parameter determines the character used to separate fractional and non-fractional parts of a number. The default value is the period (.); however, you can change this value to a comma. To use II_DECIMAL set to a comma with the Microsoft SQL Server gateway, two settings are required:
• Windows locale must be set to use a comma as the decimal separator.
• The Data Source Name (DSN) must be configured to use the regional settings.
To set up the locale settings for Windows
1. From the Control Panel, select Regional and Language Options.
2. Under Standards and formats, either select a language that defaults to using a comma (such as German (Germany)) or select Customize and set the Decimal symbol to a comma (,). Click OK.
To configure the DSN to use the regional settings, select the Use regional settings when outputting currency, numbers, dates and times check box. See DSN Setup for Windows for additional information.
For additional information on II_DECIMAL, see Developing Portable Applications and the Ingres OpenSQL Reference Guide.